# 2009 September 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for foreign keys. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable {!foreignkey||!trigger} { finish_test return } #------------------------------------------------------------------------- # Test structure: # # fkey2-1.*: Simple tests to check that immediate and deferred foreign key # constraints work when not inside a transaction. # # fkey2-2.*: Tests to verify that deferred foreign keys work inside # explicit transactions (i.e that processing really is deferred). # # fkey2-3.*: Tests that a statement transaction is rolled back if an # immediate foreign key constraint is violated. # # fkey2-4.*: Test that FK actions may recurse even when recursive triggers # are disabled. # # fkey2-5.*: Check that if foreign-keys are enabled, it is not possible # to write to an FK column using the incremental blob API. # # fkey2-6.*: Test that FK processing is automatically disabled when # running VACUUM. # # fkey2-7.*: Test using an IPK as the key in the child (referencing) table. # # fkey2-8.*: Test that enabling/disabling foreign key support while a # transaction is active is not possible. # # fkey2-9.*: Test SET DEFAULT actions. # # fkey2-10.*: Test errors. # # fkey2-11.*: Test CASCADE actions. # # fkey2-12.*: Test RESTRICT actions. # # fkey2-13.*: Test that FK processing is performed when a row is REPLACED by # an UPDATE or INSERT statement. # # fkey2-14.*: Test the ALTER TABLE and DROP TABLE commands. # # fkey2-15.*: Test that if there are no (known) outstanding foreign key # constraint violations in the database, inserting into a parent # table or deleting from a child table does not cause SQLite # to check if this has repaired an outstanding violation. # # fkey2-16.*: Test that rows that refer to themselves may be inserted, # updated and deleted. # # fkey2-genfkey.*: Tests that were used with the shell tool .genfkey # command. Recycled to test the built-in implementation. # proc drop_all_tables {{db db}} { set tbls [execsql {SELECT name FROM sqlite_master WHERE type = 'table'}] execsql { PRAGMA foreign_keys = OFF } foreach t [execsql { SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT like 'sqlite_%' }] { execsql "DROP TABLE $t" } execsql { PRAGMA foreign_keys = ON } } execsql { PRAGMA foreign_keys = on } set FkeySimpleSchema { PRAGMA foreign_keys = on; CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2(c REFERENCES t1(a) /D/ , d); CREATE TABLE t3(a PRIMARY KEY, b); CREATE TABLE t4(c REFERENCES t3 /D/, d); CREATE TABLE t7(a, b INTEGER PRIMARY KEY); CREATE TABLE t8(c REFERENCES t7 /D/, d); CREATE TABLE t9(a REFERENCES nosuchtable, b); CREATE TABLE t10(a REFERENCES t9(c) /D/, b); } set FkeySimpleTests { 1.1 "INSERT INTO t2 VALUES(1, 3)" {1 {foreign key constraint failed}} 1.2 "INSERT INTO t1 VALUES(1, 2)" {0 {}} 1.3 "INSERT INTO t2 VALUES(1, 3)" {0 {}} 1.4 "INSERT INTO t2 VALUES(2, 4)" {1 {foreign key constraint failed}} 1.5 "INSERT INTO t2 VALUES(NULL, 4)" {0 {}} 1.6 "UPDATE t2 SET c=2 WHERE d=4" {1 {foreign key constraint failed}} 1.7 "UPDATE t2 SET c=1 WHERE d=4" {0 {}} 1.9 "UPDATE t2 SET c=1 WHERE d=4" {0 {}} 1.10 "UPDATE t2 SET c=NULL WHERE d=4" {0 {}} 1.11 "DELETE FROM t1 WHERE a=1" {1 {foreign key constraint failed}} 1.12 "UPDATE t1 SET a = 2" {1 {foreign key constraint failed}} 1.13 "UPDATE t1 SET a = 1" {0 {}} 2.1 "INSERT INTO t4 VALUES(1, 3)" {1 {foreign key constraint failed}} 2.2 "INSERT INTO t3 VALUES(1, 2)" {0 {}} 2.3 "INSERT INTO t4 VALUES(1, 3)" {0 {}} 4.1 "INSERT INTO t8 VALUES(1, 3)" {1 {foreign key constraint failed}} 4.2 "INSERT INTO t7 VALUES(2, 1)" {0 {}} 4.3 "INSERT INTO t8 VALUES(1, 3)" {0 {}} 4.4 "INSERT INTO t8 VALUES(2, 4)" {1 {foreign key constraint failed}} 4.5 "INSERT INTO t8 VALUES(NULL, 4)" {0 {}} 4.6 "UPDATE t8 SET c=2 WHERE d=4" {1 {foreign key constraint failed}} 4.7 "UPDATE t8 SET c=1 WHERE d=4" {0 {}} 4.9 "UPDATE t8 SET c=1 WHERE d=4" {0 {}} 4.10 "UPDATE t8 SET c=NULL WHERE d=4" {0 {}} 4.11 "DELETE FROM t7 WHERE b=1" {1 {foreign key constraint failed}} 4.12 "UPDATE t7 SET b = 2" {1 {foreign key constraint failed}} 4.13 "UPDATE t7 SET b = 1" {0 {}} 4.14 "INSERT INTO t8 VALUES('a', 'b')" {1 {foreign key constraint failed}} 5.1 "INSERT INTO t9 VALUES(1, 3)" {1 {no such table: main.nosuchtable}} 5.2 "INSERT INTO t10 VALUES(1, 3)" {1 {foreign key mismatch}} } do_test fkey2-1.1.0 { execsql [string map {/D/ {}} $FkeySimpleSchema] } {} foreach {tn zSql res} $FkeySimpleTests { do_test fkey2-1.1.$tn { catchsql $zSql } $res } drop_all_tables do_test fkey2-1.2.0 { execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema] } {} foreach {tn zSql res} $FkeySimpleTests { do_test fkey2-1.2.$tn { catchsql $zSql } $res } drop_all_tables do_test fkey2-1.3.0 { execsql [string map {/D/ {}} $FkeySimpleSchema] execsql { PRAGMA count_changes = 1 } } {} foreach {tn zSql res} $FkeySimpleTests { if {$res == "0 {}"} { set res {0 1} } do_test fkey2-1.3.$tn { catchsql $zSql } $res } execsql { PRAGMA count_changes = 0 } drop_all_tables # Special test: When the parent key is an IPK, make sure the affinity of # the IPK is not applied to the child key value before it is inserted # into the child table. do_test fkey2-1.4.1 { execsql { CREATE TABLE i(i INTEGER PRIMARY KEY); CREATE TABLE j(j REFERENCES i); INSERT INTO i VALUES(35); INSERT INTO j VALUES('35.0'); SELECT j, typeof(j) FROM j; } } {35.0 text} do_test fkey2-1.4.2 { catchsql { DELETE FROM i } } {1 {foreign key constraint failed}} # Same test using a regular primary key with integer affinity. drop_all_tables do_test fkey2-1.5.1 { execsql { CREATE TABLE i(i INT UNIQUE); CREATE TABLE j(j REFERENCES i(i)); INSERT INTO i VALUES('35.0'); INSERT INTO j VALUES('35.0'); SELECT j, typeof(j) FROM j; SELECT i, typeof(i) FROM i; } } {35.0 text 35 integer} do_test fkey2-1.5.2 { catchsql { DELETE FROM i } } {1 {foreign key constraint failed}} # Use a collation sequence on the parent key. drop_all_tables do_test fkey2-1.6.1 { execsql { CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY); CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i)); INSERT INTO i VALUES('SQLite'); INSERT INTO j VALUES('sqlite'); } catchsql { DELETE FROM i } } {1 {foreign key constraint failed}} # Use the parent key collation even if it is default and the child key # has an explicit value. drop_all_tables do_test fkey2-1.6.2 { execsql { CREATE TABLE i(i TEXT PRIMARY KEY); -- Colseq is "BINARY" CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i)); INSERT INTO i VALUES('SQLite'); } catchsql { INSERT INTO j VALUES('sqlite') } } {1 {foreign key constraint failed}} do_test fkey2-1.6.3 { execsql { INSERT INTO i VALUES('sqlite'); INSERT INTO j VALUES('sqlite'); DELETE FROM i WHERE i = 'SQLite'; } catchsql { DELETE FROM i WHERE i = 'sqlite' } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # This section (test cases fkey2-2.*) contains tests to check that the # deferred foreign key constraint logic works. # proc fkey2-2-test {tn nocommit sql {res {}}} { if {$res eq "FKV"} { set expected {1 {foreign key constraint failed}} } else { set expected [list 0 $res] } do_test fkey2-2.$tn [list catchsql $sql] $expected if {$nocommit} { do_test fkey2-2.${tn}c { catchsql COMMIT } {1 {foreign key constraint failed}} } } fkey2-2-test 1 0 { CREATE TABLE node( nodeid PRIMARY KEY, parent REFERENCES node DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE leaf( cellid PRIMARY KEY, parent REFERENCES node DEFERRABLE INITIALLY DEFERRED ); } fkey2-2-test 1 0 "INSERT INTO node VALUES(1, 0)" FKV fkey2-2-test 2 0 "BEGIN" fkey2-2-test 3 1 "INSERT INTO node VALUES(1, 0)" fkey2-2-test 4 0 "UPDATE node SET parent = NULL" fkey2-2-test 5 0 "COMMIT" fkey2-2-test 6 0 "SELECT * FROM node" {1 {}} fkey2-2-test 7 0 "BEGIN" fkey2-2-test 8 1 "INSERT INTO leaf VALUES('a', 2)" fkey2-2-test 9 1 "INSERT INTO node VALUES(2, 0)" fkey2-2-test 10 0 "UPDATE node SET parent = 1 WHERE nodeid = 2" fkey2-2-test 11 0 "COMMIT" fkey2-2-test 12 0 "SELECT * FROM node" {1 {} 2 1} fkey2-2-test 13 0 "SELECT * FROM leaf" {a 2} fkey2-2-test 14 0 "BEGIN" fkey2-2-test 15 1 "DELETE FROM node WHERE nodeid = 2" fkey2-2-test 16 0 "INSERT INTO node VALUES(2, NULL)" fkey2-2-test 17 0 "COMMIT" fkey2-2-test 18 0 "SELECT * FROM node" {1 {} 2 {}} fkey2-2-test 19 0 "SELECT * FROM leaf" {a 2} fkey2-2-test 20 0 "BEGIN" fkey2-2-test 21 0 "INSERT INTO leaf VALUES('b', 1)" fkey2-2-test 22 0 "SAVEPOINT save" fkey2-2-test 23 0 "DELETE FROM node WHERE nodeid = 1" fkey2-2-test 24 0 "ROLLBACK TO save" fkey2-2-test 25 0 "COMMIT" fkey2-2-test 26 0 "SELECT * FROM node" {1 {} 2 {}} fkey2-2-test 27 0 "SELECT * FROM leaf" {a 2 b 1} fkey2-2-test 28 0 "BEGIN" fkey2-2-test 29 0 "INSERT INTO leaf VALUES('c', 1)" fkey2-2-test 30 0 "SAVEPOINT save" fkey2-2-test 31 0 "DELETE FROM node WHERE nodeid = 1" fkey2-2-test 32 1 "RELEASE save" fkey2-2-test 33 1 "DELETE FROM leaf WHERE cellid = 'b'" fkey2-2-test 34 0 "DELETE FROM leaf WHERE cellid = 'c'" fkey2-2-test 35 0 "COMMIT" fkey2-2-test 36 0 "SELECT * FROM node" {2 {}} fkey2-2-test 37 0 "SELECT * FROM leaf" {a 2} fkey2-2-test 38 0 "SAVEPOINT outer" fkey2-2-test 39 1 "INSERT INTO leaf VALUES('d', 3)" fkey2-2-test 40 1 "RELEASE outer" FKV fkey2-2-test 41 1 "INSERT INTO leaf VALUES('e', 3)" fkey2-2-test 42 0 "INSERT INTO node VALUES(3, 2)" fkey2-2-test 43 0 "RELEASE outer" fkey2-2-test 44 0 "SAVEPOINT outer" fkey2-2-test 45 1 "DELETE FROM node WHERE nodeid=3" fkey2-2-test 47 0 "INSERT INTO node VALUES(3, 2)" fkey2-2-test 48 0 "ROLLBACK TO outer" fkey2-2-test 49 0 "RELEASE outer" fkey2-2-test 50 0 "SAVEPOINT outer" fkey2-2-test 51 1 "INSERT INTO leaf VALUES('f', 4)" fkey2-2-test 52 1 "SAVEPOINT inner" fkey2-2-test 53 1 "INSERT INTO leaf VALUES('g', 4)" fkey2-2-test 54 1 "RELEASE outer" FKV fkey2-2-test 55 1 "ROLLBACK TO inner" fkey2-2-test 56 0 "COMMIT" FKV fkey2-2-test 57 0 "INSERT INTO node VALUES(4, NULL)" fkey2-2-test 58 0 "RELEASE outer" fkey2-2-test 59 0 "SELECT * FROM node" {2 {} 3 2 4 {}} fkey2-2-test 60 0 "SELECT * FROM leaf" {a 2 d 3 e 3 f 4} # The following set of tests check that if a statement that affects # multiple rows violates some foreign key constraints, then strikes a # constraint that causes the statement-transaction to be rolled back, # the deferred constraint counter is correctly reset to the value it # had before the statement-transaction was opened. # fkey2-2-test 61 0 "BEGIN" fkey2-2-test 62 0 "DELETE FROM leaf" fkey2-2-test 63 0 "DELETE FROM node" fkey2-2-test 64 1 "INSERT INTO leaf VALUES('a', 1)" fkey2-2-test 65 1 "INSERT INTO leaf VALUES('b', 2)" fkey2-2-test 66 1 "INSERT INTO leaf VALUES('c', 1)" do_test fkey2-2-test-67 { catchsql "INSERT INTO node SELECT parent, 3 FROM leaf" } {1 {column nodeid is not unique}} fkey2-2-test 68 0 "COMMIT" FKV fkey2-2-test 69 1 "INSERT INTO node VALUES(1, NULL)" fkey2-2-test 70 0 "INSERT INTO node VALUES(2, NULL)" fkey2-2-test 71 0 "COMMIT" fkey2-2-test 72 0 "BEGIN" fkey2-2-test 73 1 "DELETE FROM node" fkey2-2-test 74 0 "INSERT INTO node(nodeid) SELECT DISTINCT parent FROM leaf" fkey2-2-test 75 0 "COMMIT" #------------------------------------------------------------------------- # Test cases fkey2-3.* test that a program that executes foreign key # actions (CASCADE, SET DEFAULT, SET NULL etc.) or tests FK constraints # opens a statement transaction if required. # # fkey2-3.1.*: Test UPDATE statements. # fkey2-3.2.*: Test DELETE statements. # drop_all_tables do_test fkey2-3.1.1 { execsql { CREATE TABLE ab(a PRIMARY KEY, b); CREATE TABLE cd( c PRIMARY KEY REFERENCES ab ON UPDATE CASCADE ON DELETE CASCADE, d ); CREATE TABLE ef( e REFERENCES cd ON UPDATE CASCADE, f, CHECK (e!=5) ); } } {} do_test fkey2-3.1.2 { execsql { INSERT INTO ab VALUES(1, 'b'); INSERT INTO cd VALUES(1, 'd'); INSERT INTO ef VALUES(1, 'e'); } } {} do_test fkey2-3.1.3 { catchsql { UPDATE ab SET a = 5 } } {1 {constraint failed}} do_test fkey2-3.1.4 { execsql { SELECT * FROM ab } } {1 b} do_test fkey2-3.1.4 { execsql BEGIN; catchsql { UPDATE ab SET a = 5 } } {1 {constraint failed}} do_test fkey2-3.1.5 { execsql COMMIT; execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef } } {1 b 1 d 1 e} do_test fkey2-3.2.1 { execsql BEGIN; catchsql { DELETE FROM ab } } {1 {foreign key constraint failed}} do_test fkey2-3.2.2 { execsql COMMIT execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef } } {1 b 1 d 1 e} #------------------------------------------------------------------------- # Test cases fkey2-4.* test that recursive foreign key actions # (i.e. CASCADE) are allowed even if recursive triggers are disabled. # drop_all_tables do_test fkey2-4.1 { execsql { CREATE TABLE t1( node PRIMARY KEY, parent REFERENCES t1 ON DELETE CASCADE ); CREATE TABLE t2(node PRIMARY KEY, parent); CREATE TRIGGER t2t AFTER DELETE ON t2 BEGIN DELETE FROM t2 WHERE parent = old.node; END; INSERT INTO t1 VALUES(1, NULL); INSERT INTO t1 VALUES(2, 1); INSERT INTO t1 VALUES(3, 1); INSERT INTO t1 VALUES(4, 2); INSERT INTO t1 VALUES(5, 2); INSERT INTO t1 VALUES(6, 3); INSERT INTO t1 VALUES(7, 3); INSERT INTO t2 SELECT * FROM t1; } } {} do_test fkey2-4.2 { execsql { PRAGMA recursive_triggers = off } execsql { BEGIN; DELETE FROM t1 WHERE node = 1; SELECT node FROM t1; } } {} do_test fkey2-4.3 { execsql { DELETE FROM t2 WHERE node = 1; SELECT node FROM t2; ROLLBACK; } } {4 5 6 7} do_test fkey2-4.4 { execsql { PRAGMA recursive_triggers = on } execsql { BEGIN; DELETE FROM t1 WHERE node = 1; SELECT node FROM t1; } } {} do_test fkey2-4.3 { execsql { DELETE FROM t2 WHERE node = 1; SELECT node FROM t2; ROLLBACK; } } {} #------------------------------------------------------------------------- # Test cases fkey2-5.* verify that the incremental blob API may not # write to a foreign key column while foreign-keys are enabled. # drop_all_tables ifcapable incrblob { do_test fkey2-5.1 { execsql { CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1(a)); INSERT INTO t1 VALUES('hello', 'world'); INSERT INTO t2 VALUES('key', 'hello'); } } {} do_test fkey2-5.2 { set rc [catch { set fd [db incrblob t2 b 1] } msg] list $rc $msg } {1 {cannot open foreign key column for writing}} do_test fkey2-5.3 { set rc [catch { set fd [db incrblob -readonly t2 b 1] } msg] close $fd set rc } {0} do_test fkey2-5.4 { execsql { PRAGMA foreign_keys = off } set rc [catch { set fd [db incrblob t2 b 1] } msg] close $fd set rc } {0} do_test fkey2-5.5 { execsql { PRAGMA foreign_keys = on } } {} } drop_all_tables ifcapable vacuum { do_test fkey2-6.1 { execsql { CREATE TABLE t1(a REFERENCES t2(c), b); CREATE TABLE t2(c UNIQUE, b); INSERT INTO t2 VALUES(1, 2); INSERT INTO t1 VALUES(1, 2); VACUUM; } } {} } #------------------------------------------------------------------------- # Test that it is possible to use an INTEGER PRIMARY KEY as the child key # of a foreign constraint. # drop_all_tables do_test fkey2-7.1 { execsql { CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b); } } {} do_test fkey2-7.2 { catchsql { INSERT INTO t2 VALUES(1, 'A'); } } {1 {foreign key constraint failed}} do_test fkey2-7.3 { execsql { INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(2, 3); INSERT INTO t2 VALUES(1, 'A'); } } {} do_test fkey2-7.4 { execsql { UPDATE t2 SET c = 2 } } {} do_test fkey2-7.5 { catchsql { UPDATE t2 SET c = 3 } } {1 {foreign key constraint failed}} do_test fkey2-7.6 { catchsql { DELETE FROM t1 WHERE a = 2 } } {1 {foreign key constraint failed}} do_test fkey2-7.7 { execsql { DELETE FROM t1 WHERE a = 1 } } {} do_test fkey2-7.8 { catchsql { UPDATE t1 SET a = 3 } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # Test that it is not possible to enable/disable FK support while a # transaction is open. # drop_all_tables proc fkey2-8-test {tn zSql value} { do_test fkey-2.8.$tn.1 [list execsql $zSql] {} do_test fkey-2.8.$tn.2 { execsql "PRAGMA foreign_keys" } $value } fkey2-8-test 1 { PRAGMA foreign_keys = 0 } 0 fkey2-8-test 2 { PRAGMA foreign_keys = 1 } 1 fkey2-8-test 3 { BEGIN } 1 fkey2-8-test 4 { PRAGMA foreign_keys = 0 } 1 fkey2-8-test 5 { COMMIT } 1 fkey2-8-test 6 { PRAGMA foreign_keys = 0 } 0 fkey2-8-test 7 { BEGIN } 0 fkey2-8-test 8 { PRAGMA foreign_keys = 1 } 0 fkey2-8-test 9 { COMMIT } 0 fkey2-8-test 10 { PRAGMA foreign_keys = 1 } 1 fkey2-8-test 11 { PRAGMA foreign_keys = off } 0 fkey2-8-test 12 { PRAGMA foreign_keys = on } 1 fkey2-8-test 13 { PRAGMA foreign_keys = no } 0 fkey2-8-test 14 { PRAGMA foreign_keys = yes } 1 fkey2-8-test 15 { PRAGMA foreign_keys = false } 0 fkey2-8-test 16 { PRAGMA foreign_keys = true } 1 #------------------------------------------------------------------------- # The following tests, fkey2-9.*, test SET DEFAULT actions. # drop_all_tables do_test fkey2-9.1.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b); CREATE TABLE t2( c INTEGER PRIMARY KEY, d INTEGER DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT ); DELETE FROM t1; } } {} do_test fkey2-9.1.2 { execsql { INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two'); INSERT INTO t2 VALUES(1, 2); SELECT * FROM t2; DELETE FROM t1 WHERE a = 2; SELECT * FROM t2; } } {1 2 1 1} do_test fkey2-9.1.3 { execsql { INSERT INTO t1 VALUES(2, 'two'); UPDATE t2 SET d = 2; DELETE FROM t1 WHERE a = 1; SELECT * FROM t2; } } {1 2} do_test fkey2-9.1.4 { execsql { SELECT * FROM t1 } } {2 two} do_test fkey2-9.1.5 { catchsql { DELETE FROM t1 } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # The following tests, fkey2-10.*, test "foreign key mismatch" and # other errors. # set tn 1 foreach zSql [list { CREATE TABLE p(a PRIMARY KEY, b); CREATE TABLE c(x REFERENCES p(c)); }] { drop_all_tables do_test fkey2-10.1.$tn { execsql $zSql catchsql { INSERT INTO c DEFAULT VALUES } } {1 {foreign key mismatch}} } # "rowid" cannot be used as part of a child or parent key definition # unless it happens to be the name of an explicitly declared column. # do_test fkey2-10.2.1 { drop_all_tables catchsql { CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2(c, d, FOREIGN KEY(rowid) REFERENCES t1(a)); } } {1 {unknown column "rowid" in foreign key definition}} do_test fkey2-10.2.2 { drop_all_tables catchsql { CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE t2(rowid, d, FOREIGN KEY(rowid) REFERENCES t1(a)); } } {0 {}} do_test fkey2-10.2.1 { drop_all_tables catchsql { CREATE TABLE t1(a, b); CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid)); INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1); INSERT INTO t2 VALUES(1, 1); } } {1 {foreign key mismatch}} do_test fkey2-10.2.2 { drop_all_tables catchsql { CREATE TABLE t1(rowid PRIMARY KEY, b); CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid)); INSERT INTO t1(rowid, b) VALUES(1, 1); INSERT INTO t2 VALUES(1, 1); } } {0 {}} #------------------------------------------------------------------------- # The following tests, fkey2-11.*, test CASCADE actions. # drop_all_tables do_test fkey2-11.1.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b); CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE); INSERT INTO t1 VALUES(10, 100); INSERT INTO t2 VALUES(10, 100); UPDATE t1 SET a = 15; SELECT * FROM t2; } } {15 100} #------------------------------------------------------------------------- # The following tests, fkey2-12.*, test RESTRICT actions. # drop_all_tables do_test fkey2-12.1.1 { execsql { CREATE TABLE t1(a, b PRIMARY KEY); CREATE TABLE t2( x REFERENCES t1 ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED ); INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two'); INSERT INTO t1 VALUES(3, 'three'); } } {} do_test fkey2-12.1.2 { execsql "BEGIN" execsql "INSERT INTO t2 VALUES('two')" } {} do_test fkey2-12.1.3 { execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'" } {} do_test fkey2-12.1.4 { catchsql "UPDATE t1 SET b = 'five' WHERE b = 'two'" } {1 {foreign key constraint failed}} do_test fkey2-12.1.5 { execsql "DELETE FROM t1 WHERE b = 'two'" } {} do_test fkey2-12.1.6 { catchsql "COMMIT" } {1 {foreign key constraint failed}} do_test fkey2-12.1.7 { execsql { INSERT INTO t1 VALUES(2, 'two'); COMMIT; } } {} #------------------------------------------------------------------------- # The following tests, fkey2-13.*, test that FK processing is performed # when rows are REPLACEd. # drop_all_tables do_test fkey2-13.1.1 { execsql { CREATE TABLE pp(a UNIQUE, b, c, PRIMARY KEY(b, c)); CREATE TABLE cc(d, e, f UNIQUE, FOREIGN KEY(d, e) REFERENCES pp); INSERT INTO pp VALUES(1, 2, 3); INSERT INTO cc VALUES(2, 3, 1); } } {} foreach {tn stmt} { 1 "REPLACE INTO pp VALUES(1, 4, 5)" 2 "REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 3, 4)" } { do_test fkey2-13.1.$tn.1 { catchsql $stmt } {1 {foreign key constraint failed}} do_test fkey2-13.1.$tn.2 { execsql { SELECT * FROM pp; SELECT * FROM cc; } } {1 2 3 2 3 1} do_test fkey2-13.1.$tn.3 { execsql BEGIN; catchsql $stmt } {1 {foreign key constraint failed}} do_test fkey2-13.1.$tn.4 { execsql { COMMIT; SELECT * FROM pp; SELECT * FROM cc; } } {1 2 3 2 3 1} } do_test fkey2-13.1.3 { execsql { REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 2, 3); SELECT rowid, * FROM pp; SELECT * FROM cc; } } {1 2 2 3 2 3 1} do_test fkey2-13.1.4 { execsql { REPLACE INTO pp(rowid, a, b, c) VALUES(2, 2, 2, 3); SELECT rowid, * FROM pp; SELECT * FROM cc; } } {2 2 2 3 2 3 1} #------------------------------------------------------------------------- # The following tests, fkey2-14.*, test that the "DROP TABLE" and "ALTER # TABLE" commands work as expected wrt foreign key constraints. # # fkey2-14.1*: ALTER TABLE ADD COLUMN # fkey2-14.2*: ALTER TABLE RENAME TABLE # fkey2-14.3*: DROP TABLE # drop_all_tables ifcapable altertable { do_test fkey2-14.1.1 { # Adding a column with a REFERENCES clause is not supported. execsql { CREATE TABLE t1(a PRIMARY KEY); CREATE TABLE t2(a, b); } catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 } } {0 {}} do_test fkey2-14.1.2 { catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 } } {0 {}} do_test fkey2-14.1.3 { catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL} } {0 {}} do_test fkey2-14.1.4 { catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'} } {1 {Cannot add a REFERENCES column with non-NULL default value}} do_test fkey2-14.1.5 { catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 } } {1 {Cannot add a REFERENCES column with non-NULL default value}} do_test fkey2-14.1.6 { execsql { PRAGMA foreign_keys = off; ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1; PRAGMA foreign_keys = on; SELECT sql FROM sqlite_master WHERE name='t2'; } } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}} # Test the sqlite_rename_parent() function directly. # proc test_rename_parent {zCreate zOld zNew} { db eval {SELECT sqlite_rename_parent($zCreate, $zOld, $zNew)} } do_test fkey2-14.2.1.1 { test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3 } {{CREATE TABLE t1(a REFERENCES "t3")}} do_test fkey2-14.2.1.2 { test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3 } {{CREATE TABLE t1(a REFERENCES t2)}} do_test fkey2-14.2.1.3 { test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3 } {{CREATE TABLE t1(a REFERENCES "t3")}} # Test ALTER TABLE RENAME TABLE a bit. # do_test fkey2-14.2.2.1 { drop_all_tables execsql { CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1); CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2); CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1); } execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} } [list \ {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \ {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \ {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \ ] do_test fkey2-14.2.2.2 { execsql { ALTER TABLE t1 RENAME TO t4 } execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} } [list \ {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \ {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \ {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \ ] do_test fkey2-14.2.2.3 { catchsql { INSERT INTO t3 VALUES(1, 2, 3) } } {1 {foreign key constraint failed}} do_test fkey2-14.2.2.4 { execsql { INSERT INTO t4 VALUES(1, NULL) } } {} do_test fkey2-14.2.2.5 { catchsql { UPDATE t4 SET b = 5 } } {1 {foreign key constraint failed}} do_test fkey2-14.2.2.6 { catchsql { UPDATE t4 SET b = 1 } } {0 {}} do_test fkey2-14.2.2.7 { execsql { INSERT INTO t3 VALUES(1, NULL, 1) } } {} } do_test fkey-2.14.3.1 { drop_all_tables execsql { CREATE TABLE t1(a, b REFERENCES nosuchtable); DROP TABLE t1; } } {} do_test fkey-2.14.3.2 { execsql { CREATE TABLE t1(a PRIMARY KEY, b); INSERT INTO t1 VALUES('a', 1); CREATE TABLE t2(x REFERENCES t1); INSERT INTO t2 VALUES('a'); } } {} do_test fkey-2.14.3.3 { catchsql { DROP TABLE t1 } } {1 {foreign key constraint failed}} do_test fkey-2.14.3.4 { execsql { DELETE FROM t2; DROP TABLE t1; } } {} do_test fkey-2.14.3.4 { catchsql { INSERT INTO t2 VALUES('x') } } {1 {no such table: main.t1}} do_test fkey-2.14.3.5 { execsql { CREATE TABLE t1(x PRIMARY KEY); INSERT INTO t1 VALUES('x'); } execsql { INSERT INTO t2 VALUES('x') } } {} do_test fkey-2.14.3.6 { catchsql { DROP TABLE t1 } } {1 {foreign key constraint failed}} do_test fkey-2.14.3.7 { execsql { DROP TABLE t2; DROP TABLE t1; } } {} do_test fkey-2.14.3.8 { execsql { CREATE TABLE pp(x, y, PRIMARY KEY(x, y)); CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z)); } catchsql { INSERT INTO cc VALUES(1, 2) } } {1 {foreign key mismatch}} do_test fkey-2.14.3.9 { execsql { DROP TABLE cc } } {} #------------------------------------------------------------------------- # The following tests, fkey2-15.*, test that unnecessary FK related scans # and lookups are avoided when the constraint counters are zero. # drop_all_tables proc execsqlS {zSql} { set ::sqlite_search_count 0 set ::sqlite_found_count 0 set res [uplevel [list execsql $zSql]] concat [expr $::sqlite_found_count + $::sqlite_search_count] $res } do_test fkey2-15.1.1 { execsql { CREATE TABLE pp(a PRIMARY KEY, b); CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED); INSERT INTO pp VALUES(1, 'one'); INSERT INTO pp VALUES(2, 'two'); INSERT INTO cc VALUES('neung', 1); INSERT INTO cc VALUES('song', 2); } } {} do_test fkey2-15.1.2 { execsqlS { INSERT INTO pp VALUES(3, 'three') } } {0} do_test fkey2-15.1.3 { execsql { BEGIN; INSERT INTO cc VALUES('see', 4); -- Violates deferred constraint } execsqlS { INSERT INTO pp VALUES(5, 'five') } } {2} do_test fkey2-15.1.4 { execsql { DELETE FROM cc WHERE x = 'see' } execsqlS { INSERT INTO pp VALUES(6, 'six') } } {0} do_test fkey2-15.1.5 { execsql COMMIT } {} do_test fkey2-15.1.6 { execsql BEGIN execsqlS { DELETE FROM cc WHERE x = 'neung'; ROLLBACK; } } {1} do_test fkey2-15.1.7 { execsql { BEGIN; DELETE FROM pp WHERE a = 2; } execsqlS { DELETE FROM cc WHERE x = 'neung'; ROLLBACK; } } {2} #------------------------------------------------------------------------- # This next block of tests, fkey2-16.*, test that rows that refer to # themselves may be inserted and deleted. # foreach {tn zSchema} { 1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a)) } 2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) } 3 { CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES self(a)) } } { drop_all_tables do_test fkey2-16.1.$tn.1 { execsql $zSchema execsql { INSERT INTO self VALUES(13, 13) } } {} do_test fkey2-16.1.$tn.2 { execsql { UPDATE self SET a = 14, b = 14 } } {} do_test fkey2-16.1.$tn.3 { catchsql { UPDATE self SET b = 15 } } {1 {foreign key constraint failed}} do_test fkey2-16.1.$tn.4 { catchsql { UPDATE self SET a = 15 } } {1 {foreign key constraint failed}} do_test fkey2-16.1.$tn.5 { catchsql { UPDATE self SET a = 15, b = 16 } } {1 {foreign key constraint failed}} do_test fkey2-16.1.$tn.6 { catchsql { UPDATE self SET a = 17, b = 17 } } {0 {}} do_test fkey2-16.1.$tn.7 { execsql { DELETE FROM self } } {} do_test fkey2-16.1.$tn.8 { catchsql { INSERT INTO self VALUES(20, 21) } } {1 {foreign key constraint failed}} } #------------------------------------------------------------------------- # The following block of tests, those prefixed with "fkey2-genfkey.", are # the same tests that were used to test the ".genfkey" command provided # by the shell tool. So these tests show that the built-in foreign key # implementation is more or less compatible with the triggers generated # by genfkey. # drop_all_tables do_test fkey2-genfkey.1.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); CREATE TABLE t2(e REFERENCES t1, f); CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c)); } } {} do_test fkey2-genfkey.1.2 { catchsql { INSERT INTO t2 VALUES(1, 2) } } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.3 { execsql { INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t2 VALUES(1, 2); } } {} do_test fkey2-genfkey.1.4 { execsql { INSERT INTO t2 VALUES(NULL, 3) } } {} do_test fkey2-genfkey.1.5 { catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL } } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.6 { execsql { UPDATE t2 SET e = 1 WHERE e IS NULL } } {} do_test fkey2-genfkey.1.7 { execsql { UPDATE t2 SET e = NULL WHERE f = 3 } } {} do_test fkey2-genfkey.1.8 { catchsql { UPDATE t1 SET a = 10 } } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.9 { catchsql { UPDATE t1 SET a = NULL } } {1 {datatype mismatch}} do_test fkey2-genfkey.1.10 { catchsql { DELETE FROM t1 } } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.11 { execsql { UPDATE t2 SET e = NULL } } {} do_test fkey2-genfkey.1.12 { execsql { UPDATE t1 SET a = 10; DELETE FROM t1; DELETE FROM t2; } } {} do_test fkey2-genfkey.1.13 { execsql { INSERT INTO t3 VALUES(1, NULL, NULL); INSERT INTO t3 VALUES(1, 2, NULL); INSERT INTO t3 VALUES(1, NULL, 3); } } {} do_test fkey2-genfkey.1.14 { catchsql { INSERT INTO t3 VALUES(3, 1, 4) } } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.15 { execsql { INSERT INTO t1 VALUES(1, 1, 4); INSERT INTO t3 VALUES(3, 1, 4); } } {} do_test fkey2-genfkey.1.16 { catchsql { DELETE FROM t1 } } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.17 { catchsql { UPDATE t1 SET b = 10} } {1 {foreign key constraint failed}} do_test fkey2-genfkey.1.18 { execsql { UPDATE t1 SET a = 10} } {} do_test fkey2-genfkey.1.19 { catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3} } {1 {foreign key constraint failed}} drop_all_tables do_test fkey2-genfkey.2.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c)); CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f); CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE ); } } {} do_test fkey2-genfkey.2.2 { execsql { INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); INSERT INTO t2 VALUES(1, 'one'); INSERT INTO t2 VALUES(4, 'four'); } } {} do_test fkey2-genfkey.2.3 { execsql { UPDATE t1 SET a = 2 WHERE a = 1; SELECT * FROM t2; } } {2 one 4 four} do_test fkey2-genfkey.2.4 { execsql { DELETE FROM t1 WHERE a = 4; SELECT * FROM t2; } } {2 one} do_test fkey2-genfkey.2.5 { execsql { INSERT INTO t3 VALUES('hello', 2, 3); UPDATE t1 SET c = 2; SELECT * FROM t3; } } {hello 2 2} do_test fkey2-genfkey.2.6 { execsql { DELETE FROM t1; SELECT * FROM t3; } } {} drop_all_tables do_test fkey2-genfkey.3.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b)); CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f); CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL ); } } {} do_test fkey2-genfkey.3.2 { execsql { INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); INSERT INTO t2 VALUES(1, 'one'); INSERT INTO t2 VALUES(4, 'four'); } } {} do_test fkey2-genfkey.3.3 { execsql { UPDATE t1 SET a = 2 WHERE a = 1; SELECT * FROM t2; } } {{} one 4 four} do_test fkey2-genfkey.3.4 { execsql { DELETE FROM t1 WHERE a = 4; SELECT * FROM t2; } } {{} one {} four} do_test fkey2-genfkey.3.5 { execsql { INSERT INTO t3 VALUES('hello', 2, 3); UPDATE t1 SET c = 2; SELECT * FROM t3; } } {hello {} {}} do_test fkey2-genfkey.3.6 { execsql { UPDATE t3 SET h = 2, i = 2; DELETE FROM t1; SELECT * FROM t3; } } {hello {} {}} finish_test