# 2009 October 7 # # 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 tests to verify the "testable statements" in the # foreignkeys.in document. # # The tests in this file are arranged to mirror the structure of # foreignkey.in, with one exception: The statements in section 2, which # deals with enabling/disabling foreign key support, is tested first, # before section 1. This is because some statements in section 2 deal # with builds that do not include complete foreign key support (because # either SQLITE_OMIT_TRIGGER or SQLITE_OMIT_FOREIGN_KEY was defined # at build time). # set testdir [file dirname $argv0] source $testdir/tester.tcl ########################################################################### ### SECTION 2: Enabling Foreign Key Support ########################################################################### #------------------------------------------------------------------------- # /* EV: R-33710-56344 */ # # Test builds neither OMIT_FOREIGN_KEY or OMIT_TRIGGER defined have # foreign key functionality. # ifcapable trigger&&foreignkey { do_test e_fkey-49 { execsql { PRAGMA foreign_keys = ON; CREATE TABLE p(i PRIMARY KEY); CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE); INSERT INTO p VALUES('hello'); INSERT INTO c VALUES('hello'); UPDATE p SET i = 'world'; SELECT * FROM c; } } {world} } #------------------------------------------------------------------------- # /* EV: R-44697-61543 */ # # Test the effects of defining OMIT_TRIGGER but not OMIT_FOREIGN_KEY. # # /* EV: R-22567-44039 */ # /* EV: R-60444-29168 */ # # Specifically, test that "PRAGMA foreign_keys" is a no-op in this case. # When using the pragma to query the current setting, 0 rows are returned. # reset_db ifcapable !trigger&&foreignkey { do_test e_fkey-51.1 { execsql { PRAGMA foreign_keys = ON; CREATE TABLE p(i PRIMARY KEY); CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE); INSERT INTO p VALUES('hello'); INSERT INTO c VALUES('hello'); UPDATE p SET i = 'world'; SELECT * FROM c; } } {hello} do_test e_fkey-51.2 { execsql { PRAGMA foreign_key_list(c) } } {0 0 p j {} CASCADE {NO ACTION} NONE} do_test e_fkey-51.3 { execsql { PRAGMA foreign_keys } } {} } #------------------------------------------------------------------------- # /* EV: R-58428-36660 */ # # Test the effects of defining OMIT_FOREIGN_KEY. # # /* EV: R-58428-36660 */ # # Specifically, test that foreign key constraints cannot even be parsed # in such a build. # reset_db ifcapable !foreignkey { do_test e_fkey-52.1 { execsql { CREATE TABLE p(i PRIMARY KEY) } catchsql { CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE) } } {1 {near "ON": syntax error}} do_test e_fkey-52.2 { # This is allowed, as in this build, "REFERENCES" is not a keyword. # The declared datatype of column j is "REFERENCES p". execsql { CREATE TABLE c(j REFERENCES p) } } {} do_test e_fkey-52.3 { execsql { PRAGMA table_info(c) } } {0 j {REFERENCES p} 0 {} 0} do_test e_fkey-52.4 { execsql { PRAGMA foreign_key_list(c) } } {} do_test e_fkey-52.5 { execsql { PRAGMA foreign_keys } } {} } ifcapable !foreignkey||!trigger { finish_test ; return } reset_db #------------------------------------------------------------------------- # /* EV: R-07280-60510 */ # # Test that even if foreign keys are supported by the build, they must # be enabled using "PRAGMA foreign_keys = ON" (or similar). # # /* EV: R-15831-45974 */ # # This also tests that foreign key constraints are disabled by default. # drop_all_tables do_test e_fkey-53.1 { execsql { CREATE TABLE p(i PRIMARY KEY); CREATE TABLE c(j REFERENCES p ON UPDATE CASCADE); INSERT INTO p VALUES('hello'); INSERT INTO c VALUES('hello'); UPDATE p SET i = 'world'; SELECT * FROM c; } } {hello} do_test e_fkey-53.2 { execsql { DELETE FROM c; DELETE FROM p; PRAGMA foreign_keys = ON; INSERT INTO p VALUES('hello'); INSERT INTO c VALUES('hello'); UPDATE p SET i = 'world'; SELECT * FROM c; } } {world} #------------------------------------------------------------------------- # /* EV: R-15278-54456 */ # /* EV: R-11255-19907 */ # # Test that the application can use "PRAGMA foreign_keys" to query for # whether or not foreign keys are currently enabled. This also tests # the example code in section 2 of foreignkeys.in. # reset_db do_test e_fkey-54.1 { execsql { PRAGMA foreign_keys } } {0} do_test e_fkey-54.2 { execsql { PRAGMA foreign_keys = ON; PRAGMA foreign_keys; } } {1} do_test e_fkey-54.3 { execsql { PRAGMA foreign_keys = OFF; PRAGMA foreign_keys; } } {0} #------------------------------------------------------------------------- # /* EV: R-07050-54503 */ # # Test that it is not possible to enable or disable foreign key support # while not in auto-commit mode. # reset_db do_test e_fkey-55.1 { execsql { PRAGMA foreign_keys = ON; CREATE TABLE t1(a UNIQUE, b); CREATE TABLE t2(c, d REFERENCES t1(a)); INSERT INTO t1 VALUES(1, 2); INSERT INTO t2 VALUES(2, 1); BEGIN; PRAGMA foreign_keys = OFF; } catchsql { DELETE FROM t1 } } {1 {foreign key constraint failed}} do_test e_fkey-55.2 { execsql { PRAGMA foreign_keys } } {1} do_test e_fkey-55.3 { execsql { COMMIT; PRAGMA foreign_keys = OFF; BEGIN; PRAGMA foreign_keys = ON; DELETE FROM t1; PRAGMA foreign_keys; } } {0} do_test e_fkey-55.4 { execsql COMMIT } {} ########################################################################### ### SECTION 1: Introduction to Foreign Key Constraints ########################################################################### execsql "PRAGMA foreign_keys = ON" #------------------------------------------------------------------------- # /* EV: R-04042-24825 */ # # Verify that the syntax in the first example in section 1 is valid. # do_test e_fkey-38.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid) ); } } {} #------------------------------------------------------------------------- # /* EV: R-61362-32087 */ # # Attempting to insert a row into the 'track' table that corresponds # to no row in the 'artist' table fails. # do_test e_fkey-39.1 { catchsql { INSERT INTO track VALUES(1, 'track 1', 1) } } {1 {foreign key constraint failed}} do_test e_fkey-39.2 { execsql { INSERT INTO artist VALUES(2, 'artist 1') } catchsql { INSERT INTO track VALUES(1, 'track 1', 1) } } {1 {foreign key constraint failed}} do_test e_fkey-39.2 { execsql { INSERT INTO track VALUES(1, 'track 1', 2) } } {} #------------------------------------------------------------------------- # /* EV: R-24401-52400 */ # # Attempting to delete a row from the 'artist' table while there are # dependent rows in the track table also fails. # do_test e_fkey-40.1 { catchsql { DELETE FROM artist WHERE artistid = 2 } } {1 {foreign key constraint failed}} do_test e_fkey-40.2 { execsql { DELETE FROM track WHERE trackartist = 2; DELETE FROM artist WHERE artistid = 2; } } {} #------------------------------------------------------------------------- # /* EV: R-23980-48859 */ # # If the foreign key column (trackartist) in table 'track' is set to NULL, # there is no requirement for a matching row in the 'artist' table. # do_test e_fkey-41.1 { execsql { INSERT INTO track VALUES(1, 'track 1', NULL); INSERT INTO track VALUES(2, 'track 2', NULL); } } {} do_test e_fkey-41.2 { execsql { SELECT * FROM artist } } {} do_test e_fkey-41.3 { # Setting the trackid to a non-NULL value fails, of course. catchsql { UPDATE track SET trackartist = 5 WHERE trackid = 1 } } {1 {foreign key constraint failed}} do_test e_fkey-41.4 { execsql { INSERT INTO artist VALUES(5, 'artist 5'); UPDATE track SET trackartist = 5 WHERE trackid = 1; } catchsql { DELETE FROM artist WHERE artistid = 5} } {1 {foreign key constraint failed}} do_test e_fkey-41.5 { execsql { UPDATE track SET trackartist = NULL WHERE trackid = 1; DELETE FROM artist WHERE artistid = 5; } } {} #------------------------------------------------------------------------- # /* EV: R-52486-21352 */ # # Test that the following is true fo all rows in the track table: # # trackartist IS NULL OR # EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist) # # This procedure executes a test case to check that statement # R-52486-21352 is true after executing the SQL statement passed. # as the second argument. proc test_r52486_21352 {tn sql} { set res [catchsql $sql] set results { {0 {}} {1 {PRIMARY KEY must be unique}} {1 {foreign key constraint failed}} } if {[lsearch $results $res]<0} { error $res } do_test e_fkey-42.$tn { execsql { SELECT count(*) FROM track WHERE NOT ( trackartist IS NULL OR EXISTS(SELECT 1 FROM artist WHERE artistid=trackartist) ) } } {0} } # Execute a series of random INSERT, UPDATE and DELETE operations # (some of which may fail due to FK or PK constraint violations) on # the two tables in the example schema. Test that R-52486-21352 # is true after executing each operation. # set Template { {INSERT INTO track VALUES($t, 'track $t', $a)} {DELETE FROM track WHERE trackid = $t} {UPDATE track SET trackartist = $a WHERE trackid = $t} {INSERT INTO artist VALUES($a, 'artist $a')} {DELETE FROM artist WHERE artistid = $a} {UPDATE artist SET artistid = $a2 WHERE artistid = $a} } for {set i 0} {$i < 500} {incr i} { set a [expr int(rand()*10)] set a2 [expr int(rand()*10)] set t [expr int(rand()*50)] set sql [subst [lindex $Template [expr int(rand()*6)]]] test_r52486_21352 $i $sql } #------------------------------------------------------------------------- # /* EV: R-42412-59321 */ # # Check that a NOT NULL constraint can be added to the example schema # to prohibit NULL child keys from being inserted. # drop_all_tables do_test e_fkey-48.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER NOT NULL, FOREIGN KEY(trackartist) REFERENCES artist(artistid) ); } } {} do_test e_fkey-48.2 { catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) } } {1 {track.trackartist may not be NULL}} #------------------------------------------------------------------------- # /* EV: R-01991-24099 */ # # Test an example from foreignkeys.html. # drop_all_tables do_test e_fkey-43.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER, FOREIGN KEY(trackartist) REFERENCES artist(artistid) ); INSERT INTO artist VALUES(1, 'Dean Martin'); INSERT INTO artist VALUES(2, 'Frank Sinatra'); INSERT INTO track VALUES(11, 'That''s Amore', 1); INSERT INTO track VALUES(12, 'Christmas Blues', 1); INSERT INTO track VALUES(13, 'My Way', 2); } } {} do_test e_fkey-43.2 { catchsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', 3) } } {1 {foreign key constraint failed}} do_test e_fkey-43.3 { execsql { INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL) } } {} do_test e_fkey-43.4 { catchsql { UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; } } {1 {foreign key constraint failed}} do_test e_fkey-43.5 { execsql { INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'; INSERT INTO track VALUES(15, 'Boogie Woogie', 3); } } {} #------------------------------------------------------------------------- # /* EV: R-22377-02546 */ # # Test the second example from the first section of foreignkeys.html. # do_test e_fkey-44.1 { catchsql { DELETE FROM artist WHERE artistname = 'Frank Sinatra'; } } {1 {foreign key constraint failed}} do_test e_fkey-44.2 { execsql { DELETE FROM track WHERE trackname = 'My Way'; DELETE FROM artist WHERE artistname = 'Frank Sinatra'; } } {} do_test e_fkey-44.3 { catchsql { UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; } } {1 {foreign key constraint failed}} do_test e_fkey-44.4 { execsql { DELETE FROM track WHERE trackname IN('That''s Amore', 'Christmas Blues'); UPDATE artist SET artistid=4 WHERE artistname = 'Dean Martin'; } } {} #------------------------------------------------------------------------- # /* EV: R-56032-24923 */ # # Test that a foreign key constraint is satisifed if "for each row in the child # table either one or more of the child key columns are NULL, or there exists a # row in the parent table for which each parent key column contains a value # equal to the value in its associated child key column". # # /* EV: R-57765-12380 */ # # Test also that the comparison rules are used when testing if there # is a matching row in the parent table of a foreign key constraint. # drop_all_tables do_test e_fkey-45.1 { execsql { CREATE TABLE par(p PRIMARY KEY); CREATE TABLE chi(c REFERENCES par); INSERT INTO par VALUES(1); INSERT INTO par VALUES('1'); INSERT INTO par VALUES(X'31'); SELECT typeof(p) FROM par; } } {integer text blob} proc test_efkey_45 {tn isError sql} { do_test e_fkey-45.$tn.1 " catchsql {$sql} " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] do_test e_fkey-45.$tn.2 { execsql { SELECT * FROM chi WHERE c IS NOT NULL AND c NOT IN (SELECT p FROM par) } } {} } test_efkey_45 1 0 "INSERT INTO chi VALUES(1)" test_efkey_45 2 1 "INSERT INTO chi VALUES('1.0')" test_efkey_45 3 0 "INSERT INTO chi VALUES('1')" test_efkey_45 4 1 "DELETE FROM par WHERE p = '1'" test_efkey_45 5 0 "DELETE FROM chi WHERE c = '1'" test_efkey_45 6 0 "DELETE FROM par WHERE p = '1'" test_efkey_45 7 1 "INSERT INTO chi VALUES('1')" test_efkey_45 8 0 "INSERT INTO chi VALUES(X'31')" test_efkey_45 9 1 "INSERT INTO chi VALUES(X'32')" #------------------------------------------------------------------------- # /* EV: R-15796-47513 */ # # Specifically, test that when comparing child and parent key values the # default collation sequence of the parent key column is used. # drop_all_tables do_test e_fkey-46.1 { execsql { CREATE TABLE t1(a COLLATE nocase PRIMARY KEY); CREATE TABLE t2(b REFERENCES t1); } } {} do_test e_fkey-46.2 { execsql { INSERT INTO t1 VALUES('oNe'); INSERT INTO t2 VALUES('one'); INSERT INTO t2 VALUES('ONE'); UPDATE t2 SET b = 'OnE'; UPDATE t1 SET a = 'ONE'; } } {} do_test e_fkey-46.3 { catchsql { UPDATE t2 SET b = 'two' WHERE rowid = 1 } } {1 {foreign key constraint failed}} do_test e_fkey-46.4 { catchsql { DELETE FROM t1 WHERE rowid = 1 } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # /* EV: R-04240-13860 */ # # Specifically, test that when comparing child and parent key values the # affinity of the parent key column is applied to the child key value # before the comparison takes place. # drop_all_tables do_test e_fkey-47.1 { execsql { CREATE TABLE t1(a NUMERIC PRIMARY KEY); CREATE TABLE t2(b TEXT REFERENCES t1); } } {} do_test e_fkey-47.2 { execsql { INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(2); INSERT INTO t1 VALUES('three'); INSERT INTO t2 VALUES('2.0'); SELECT b, typeof(b) FROM t2; } } {2.0 text} do_test e_fkey-47.3 { execsql { SELECT typeof(a) FROM t1 } } {integer integer text} do_test e_fkey-47.4 { catchsql { DELETE FROM t1 WHERE rowid = 2 } } {1 {foreign key constraint failed}} ########################################################################### ### SECTION 3: Required and Suggested Database Indexes ########################################################################### ########################################################################### ### SECTION 4.1: Composite Foreign Key Constraints ########################################################################### #------------------------------------------------------------------------- # /* EV: R-56396-15644 */ # # Test the example schema in the "Composite Foreign Key Constraints" # section. # do_test e_fkey-36.1 { execsql { CREATE TABLE album( albumartist TEXT, albumname TEXT, albumcover BINARY, PRIMARY KEY(albumartist, albumname) ); CREATE TABLE song( songid INTEGER, songartist TEXT, songalbum TEXT, songname TEXT, FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist,albumname) ); } } {} do_test e_fkey-36.2 { execsql { INSERT INTO album VALUES('Elvis Presley', 'Elvis'' Christmas Album', NULL); INSERT INTO song VALUES( 1, 'Elvis Presley', 'Elvis'' Christmas Album', 'Here Comes Santa Clause' ); } } {} do_test e_fkey-36.3 { catchsql { INSERT INTO song VALUES(2, 'Elvis Presley', 'Elvis Is Back!', 'Fever'); } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # /* EV: R-33626-48418 */ # # Check that if any of the child key columns in the above schema are NULL, # there is no requirement for a corresponding parent key. # do_test e_fkey-37.1 { execsql { INSERT INTO song VALUES(2, 'Elvis Presley', NULL, 'Fever'); INSERT INTO song VALUES(3, NULL, 'Elvis Is Back', 'Soldier Boy'); } } {} ########################################################################### ### SECTION 4.2: Deferred Foreign Key Constraints ########################################################################### #------------------------------------------------------------------------- # Note: R-35290-16460 is tested below. # # TODO: R-30323-21917 #------------------------------------------------------------------------- # /* EV: R-09323-30470 */ # # Test that if a statement violates an immediate FK constraint, and the # database does not satisfy the FK constraint once all effects of the # statement have been applied, an error is reported and the effects of # the statement rolled back. # drop_all_tables do_test e_fkey-33.1 { execsql { CREATE TABLE king(a, b, PRIMARY KEY(a)); CREATE TABLE prince(c REFERENCES king, d); } } {} do_test e_fkey-33.2 { # Execute a statement that violates the immediate FK constraint. catchsql { INSERT INTO prince VALUES(1, 2) } } {1 {foreign key constraint failed}} do_test e_fkey-33.3 { # This time, use a trigger to fix the constraint violation before the # statement has finished executing. Then execute the same statement as # in the previous test case. This time, no error. execsql { CREATE TRIGGER kt AFTER INSERT ON prince WHEN NOT EXISTS (SELECT a FROM king WHERE a = new.c) BEGIN INSERT INTO king VALUES(new.c, NULL); END } execsql { INSERT INTO prince VALUES(1, 2) } } {} # Test that operating inside a transaction makes no difference to # immediate constraint violation handling. do_test e_fkey-33.4 { execsql { BEGIN; INSERT INTO prince VALUES(2, 3); DROP TRIGGER kt; } catchsql { INSERT INTO prince VALUES(3, 4) } } {1 {foreign key constraint failed}} do_test e_fkey-33.5 { execsql { COMMIT; SELECT * FROM king; } } {1 {} 2 {}} #------------------------------------------------------------------------- # /* EV: R-49178-21358 */ # /* EV: R-39692-12488 */ # /* EV: R-55147-47664 */ # /* EV: R-29604-30395 */ # # Test that if a deferred constraint is violated within a transaction, # nothing happens immediately and the database is allowed to persist # in a state that does not satisfy the FK constraint. However attempts # to COMMIT the transaction fail until the FK constraint is satisfied. # proc test_efkey_34 {tn isError sql} { do_test e_fkey-34.$tn " catchsql {$sql} " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] } drop_all_tables test_efkey_34 1 0 { CREATE TABLE ll(k PRIMARY KEY); CREATE TABLE kk(c REFERENCES ll DEFERRABLE INITIALLY DEFERRED); } test_efkey_34 2 0 "BEGIN" test_efkey_34 3 0 "INSERT INTO kk VALUES(5)" test_efkey_34 4 0 "INSERT INTO kk VALUES(10)" test_efkey_34 5 1 "COMMIT" test_efkey_34 6 0 "INSERT INTO ll VALUES(10)" test_efkey_34 7 1 "COMMIT" test_efkey_34 8 0 "INSERT INTO ll VALUES(5)" test_efkey_34 9 0 "COMMIT" #------------------------------------------------------------------------- # /* EV: R-56844-61705 */ # # When not running inside a transaction, a deferred constraint is similar # to an immediate constraint (violations are reported immediately). # drop_all_tables proc test_efkey_35 {tn isError sql} { do_test e_fkey-35.$tn " catchsql {$sql} " [lindex {{0 {}} {1 {foreign key constraint failed}}} $isError] } do_test e_fkey-35.1 { execsql { CREATE TABLE parent(x, y); CREATE UNIQUE INDEX pi ON parent(x, y); CREATE TABLE child(a, b, FOREIGN KEY(a, b) REFERENCES parent(x, y) DEFERRABLE INITIALLY DEFERRED ); } } {} test_efkey_35 2 1 "INSERT INTO child VALUES('x', 'y')" test_efkey_35 3 0 "INSERT INTO parent VALUES('x', 'y')" test_efkey_35 4 0 "INSERT INTO child VALUES('x', 'y')" #------------------------------------------------------------------------- # /* EV: R-12782-61841 */ # # Test that an FK constraint is made deferred by adding the following # to the definition: # # DEFERRABLE INITIALLY DEFERRED # # /* EV: R-54882-46975 */ # # Also test that adding any of the following to a foreign key definition # makes the constraint IMMEDIATE: # # NOT DEFERRABLE INITIALLY DEFERRED # DEFERRABLE INITIALLY IMMEDIATE # DEFERRABLE # # /* EV: R-35290-16460 */ # # Foreign keys are IMMEDIATE by default (if there is no DEFERRABLE or NOT # DEFERRABLE clause). # drop_all_tables do_test e_fkey-29.1 { execsql { CREATE TABLE parent(x, y, z, PRIMARY KEY(x,y,z)); CREATE TABLE c1(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent NOT DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE c2(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY IMMEDIATE ); CREATE TABLE c3(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE ); CREATE TABLE c4(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent); -- This FK constraint is the only deferrable one. CREATE TABLE c5(a, b, c, FOREIGN KEY(a, b, c) REFERENCES parent DEFERRABLE INITIALLY DEFERRED ); INSERT INTO parent VALUES('a', 'b', 'c'); INSERT INTO parent VALUES('d', 'e', 'f'); INSERT INTO parent VALUES('g', 'h', 'i'); INSERT INTO parent VALUES('j', 'k', 'l'); INSERT INTO parent VALUES('m', 'n', 'o'); INSERT INTO c1 VALUES('a', 'b', 'c'); INSERT INTO c2 VALUES('d', 'e', 'f'); INSERT INTO c3 VALUES('g', 'h', 'i'); INSERT INTO c4 VALUES('j', 'k', 'l'); INSERT INTO c5 VALUES('m', 'n', 'o'); } } {} proc test_efkey_29 {tn sql isError} { do_test e_fkey-29.$tn "catchsql {$sql}" [ lindex {{0 {}} {1 {foreign key constraint failed}}} $isError ] } test_efkey_29 2 "BEGIN" 0 test_efkey_29 3 "DELETE FROM parent WHERE x = 'a'" 1 test_efkey_29 4 "DELETE FROM parent WHERE x = 'd'" 1 test_efkey_29 5 "DELETE FROM parent WHERE x = 'g'" 1 test_efkey_29 6 "DELETE FROM parent WHERE x = 'j'" 1 test_efkey_29 7 "DELETE FROM parent WHERE x = 'm'" 0 test_efkey_29 8 "COMMIT" 1 test_efkey_29 9 "ROLLBACK" 0 test_efkey_29 9 "BEGIN" 0 test_efkey_29 10 "UPDATE parent SET z = 'z' WHERE z = 'c'" 1 test_efkey_29 11 "UPDATE parent SET z = 'z' WHERE z = 'f'" 1 test_efkey_29 12 "UPDATE parent SET z = 'z' WHERE z = 'i'" 1 test_efkey_29 13 "UPDATE parent SET z = 'z' WHERE z = 'l'" 1 test_efkey_29 14 "UPDATE parent SET z = 'z' WHERE z = 'o'" 0 test_efkey_29 15 "COMMIT" 1 test_efkey_29 16 "ROLLBACK" 0 test_efkey_29 17 "BEGIN" 0 test_efkey_29 18 "INSERT INTO c1 VALUES(1, 2, 3)" 1 test_efkey_29 19 "INSERT INTO c2 VALUES(1, 2, 3)" 1 test_efkey_29 20 "INSERT INTO c3 VALUES(1, 2, 3)" 1 test_efkey_29 21 "INSERT INTO c4 VALUES(1, 2, 3)" 1 test_efkey_29 22 "INSERT INTO c5 VALUES(1, 2, 3)" 0 test_efkey_29 23 "COMMIT" 1 test_efkey_29 24 "INSERT INTO parent VALUES(1, 2, 3)" 0 test_efkey_29 25 "COMMIT" 0 test_efkey_29 26 "BEGIN" 0 test_efkey_29 27 "UPDATE c1 SET a = 10" 1 test_efkey_29 28 "UPDATE c2 SET a = 10" 1 test_efkey_29 29 "UPDATE c3 SET a = 10" 1 test_efkey_29 30 "UPDATE c4 SET a = 10" 1 test_efkey_29 31 "UPDATE c5 SET a = 10" 0 test_efkey_29 32 "COMMIT" 1 test_efkey_29 33 "ROLLBACK" 0 #------------------------------------------------------------------------- # /* EV: R-27340-26081 */ # # Test an example from foreignkeys.html dealing with a deferred foreign # key constraint. # do_test e_fkey-28.1 { drop_all_tables execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) DEFERRABLE INITIALLY DEFERRED ); } } {} do_test e_fkey-28.2 { execsql { BEGIN; INSERT INTO track VALUES(1, 'White Christmas', 5); } catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-28.3 { execsql { INSERT INTO artist VALUES(5, 'Bing Crosby'); COMMIT; } } {} #------------------------------------------------------------------------- # /* EV: R-07223-48323 */ # # Verify that a nested savepoint may be released without satisfying # deferred foreign key constraints. # drop_all_tables do_test e_fkey-30.1 { execsql { CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1 DEFERRABLE INITIALLY DEFERRED ); INSERT INTO t1 VALUES(1, 1); INSERT INTO t1 VALUES(2, 2); INSERT INTO t1 VALUES(3, 3); } } {} do_test e_fkey-30.2 { execsql { BEGIN; SAVEPOINT one; INSERT INTO t1 VALUES(4, 5); RELEASE one; } } {} do_test e_fkey-30.3 { catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-30.4 { execsql { UPDATE t1 SET a = 5 WHERE a = 4; COMMIT; } } {} #------------------------------------------------------------------------- # /* EV: R-44295-13823 */ # # Check that a transaction savepoint (an outermost savepoint opened when # the database was in auto-commit mode) cannot be released without # satisfying deferred foreign key constraints. It may be rolled back. # do_test e_fkey-31.1 { execsql { SAVEPOINT one; SAVEPOINT two; INSERT INTO t1 VALUES(6, 7); RELEASE two; } } {} do_test e_fkey-31.2 { catchsql {RELEASE one} } {1 {foreign key constraint failed}} do_test e_fkey-31.3 { execsql { UPDATE t1 SET a = 7 WHERE a = 6; RELEASE one; } } {} do_test e_fkey-31.4 { execsql { SAVEPOINT one; SAVEPOINT two; INSERT INTO t1 VALUES(9, 10); RELEASE two; } } {} do_test e_fkey-31.5 { catchsql {RELEASE one} } {1 {foreign key constraint failed}} do_test e_fkey-31.6 { execsql {ROLLBACK TO one ; RELEASE one} } {} #------------------------------------------------------------------------- # /* EV: R-37736-42616 */ # # Test that if a COMMIT operation fails due to deferred foreign key # constraints, any nested savepoints remain open. # do_test e_fkey-32.1 { execsql { DELETE FROM t1 WHERE a>3; SELECT * FROM t1; } } {1 1 2 2 3 3} do_test e_fkey-32.2 { execsql { BEGIN; INSERT INTO t1 VALUES(4, 4); SAVEPOINT one; INSERT INTO t1 VALUES(5, 6); SELECT * FROM t1; } } {1 1 2 2 3 3 4 4 5 6} do_test e_fkey-32.3 { catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-32.4 { execsql { ROLLBACK TO one; COMMIT; SELECT * FROM t1; } } {1 1 2 2 3 3 4 4} do_test e_fkey-32.5 { execsql { SAVEPOINT a; INSERT INTO t1 VALUES(5, 5); SAVEPOINT b; INSERT INTO t1 VALUES(6, 7); SAVEPOINT c; INSERT INTO t1 VALUES(7, 8); } } {} do_test e_fkey-32.6 { catchsql {RELEASE a} } {1 {foreign key constraint failed}} do_test e_fkey-32.7 { execsql {ROLLBACK TO c} catchsql {RELEASE a} } {1 {foreign key constraint failed}} do_test e_fkey-32.8 { execsql { ROLLBACK TO b; RELEASE a; SELECT * FROM t1; } } {1 1 2 2 3 3 4 4 5 5} ########################################################################### ### SECTION 4.3: ON DELETE and ON UPDATE Actions ########################################################################### #------------------------------------------------------------------------- # /* EV: R-48270-44282 */ # # Test that configured ON DELETE and ON UPDATE actions take place when # deleting or modifying rows of the parent table, respectively. # # /* EV: R-48124-63225 */ # # Test that a single FK constraint may have different actions configured # for ON DELETE and ON UPDATE. # do_test e_fkey-16.1 { execsql { CREATE TABLE p(a, b PRIMARY KEY, c); CREATE TABLE c1(d, e, f DEFAULT 'k0' REFERENCES p ON UPDATE SET DEFAULT ON DELETE SET NULL ); INSERT INTO p VALUES(0, 'k0', ''); INSERT INTO p VALUES(1, 'k1', 'I'); INSERT INTO p VALUES(2, 'k2', 'II'); INSERT INTO p VALUES(3, 'k3', 'III'); INSERT INTO c1 VALUES(1, 'xx', 'k1'); INSERT INTO c1 VALUES(2, 'xx', 'k2'); INSERT INTO c1 VALUES(3, 'xx', 'k3'); } } {} do_test e_fkey-16.2 { execsql { UPDATE p SET b = 'k4' WHERE a = 1; SELECT * FROM c1; } } {1 xx k0 2 xx k2 3 xx k3} do_test e_fkey-16.3 { execsql { DELETE FROM p WHERE a = 2; SELECT * FROM c1; } } {1 xx k0 2 xx {} 3 xx k3} do_test e_fkey-16.4 { execsql { CREATE UNIQUE INDEX pi ON p(c); REPLACE INTO p VALUES(5, 'k5', 'III'); SELECT * FROM c1; } } {1 xx k0 2 xx {} 3 xx {}} #------------------------------------------------------------------------- # /* EV: R-33326-45252 */ # # Each foreign key in the system has an ON UPDATE and ON DELETE action, # either "NO ACTION", "RESTRICT", "SET NULL", "SET DEFAULT" or "CASCADE". # # /* EV: R-19803-45884 */ # # If none is specified explicitly, "NO ACTION" is the default. # drop_all_tables do_test e_fkey-17.1 { execsql { CREATE TABLE parent(x PRIMARY KEY, y); CREATE TABLE child1(a, b REFERENCES parent ON UPDATE NO ACTION ON DELETE RESTRICT ); CREATE TABLE child2(a, b REFERENCES parent ON UPDATE RESTRICT ON DELETE SET NULL ); CREATE TABLE child3(a, b REFERENCES parent ON UPDATE SET NULL ON DELETE SET DEFAULT ); CREATE TABLE child4(a, b REFERENCES parent ON UPDATE SET DEFAULT ON DELETE CASCADE ); -- Create some foreign keys that use the default action - "NO ACTION" CREATE TABLE child5(a, b REFERENCES parent ON UPDATE CASCADE); CREATE TABLE child6(a, b REFERENCES parent ON DELETE RESTRICT); CREATE TABLE child7(a, b REFERENCES parent ON DELETE NO ACTION); CREATE TABLE child8(a, b REFERENCES parent ON UPDATE NO ACTION); } } {} foreach {tn zTab lRes} { 2 child1 {0 0 parent b {} {NO ACTION} RESTRICT NONE} 3 child2 {0 0 parent b {} RESTRICT {SET NULL} NONE} 4 child3 {0 0 parent b {} {SET NULL} {SET DEFAULT} NONE} 5 child4 {0 0 parent b {} {SET DEFAULT} CASCADE NONE} 6 child5 {0 0 parent b {} CASCADE {NO ACTION} NONE} 7 child6 {0 0 parent b {} {NO ACTION} RESTRICT NONE} 8 child7 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE} 9 child8 {0 0 parent b {} {NO ACTION} {NO ACTION} NONE} } { do_test e_fkey-17.$tn { execsql "PRAGMA foreign_key_list($zTab)" } $lRes } #------------------------------------------------------------------------- # /* EV: R-19971-54976 */ # # Test that "NO ACTION" means that nothing happens to a child row when # it's parent row is updated or deleted. # drop_all_tables do_test e_fkey-18.1 { execsql { CREATE TABLE parent(p1, p2, PRIMARY KEY(p1, p2)); CREATE TABLE child(c1, c2, FOREIGN KEY(c1, c2) REFERENCES parent ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ); INSERT INTO parent VALUES('j', 'k'); INSERT INTO parent VALUES('l', 'm'); INSERT INTO child VALUES('j', 'k'); INSERT INTO child VALUES('l', 'm'); } } {} do_test e_fkey-18.2 { execsql { BEGIN; UPDATE parent SET p1='k' WHERE p1='j'; DELETE FROM parent WHERE p1='l'; SELECT * FROM child; } } {j k l m} do_test e_fkey-18.3 { catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-18.4 { execsql ROLLBACK } {} #------------------------------------------------------------------------- # /* EV: R-04272-38653 */ # # Test that "RESTRICT" means the application is prohibited from deleting # or updating a parent table row when there exists one or more child keys # mapped to it. # drop_all_tables do_test e_fkey-18.1 { execsql { CREATE TABLE parent(p1, p2); CREATE UNIQUE INDEX parent_i ON parent(p1, p2); CREATE TABLE child1(c1, c2, FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON DELETE RESTRICT ); CREATE TABLE child2(c1, c2, FOREIGN KEY(c2, c1) REFERENCES parent(p1, p2) ON UPDATE RESTRICT ); } } {} do_test e_fkey-18.2 { execsql { INSERT INTO parent VALUES('a', 'b'); INSERT INTO parent VALUES('c', 'd'); INSERT INTO child1 VALUES('b', 'a'); INSERT INTO child2 VALUES('d', 'c'); } } {} do_test e_fkey-18.3 { catchsql { DELETE FROM parent WHERE p1 = 'a' } } {1 {foreign key constraint failed}} do_test e_fkey-18.4 { catchsql { UPDATE parent SET p2 = 'e' WHERE p1 = 'c' } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # /* EV: R-37997-42187 */ # # Test that RESTRICT is slightly different from NO ACTION for IMMEDIATE # constraints, in that it is enforced immediately, not at the end of the # statement. # drop_all_tables do_test e_fkey-19.1 { execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT); CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION); INSERT INTO parent VALUES('key1'); INSERT INTO parent VALUES('key2'); INSERT INTO child1 VALUES('key1'); INSERT INTO child2 VALUES('key2'); CREATE TRIGGER parent_t AFTER UPDATE ON parent BEGIN UPDATE child1 set c = new.x WHERE c = old.x; UPDATE child2 set c = new.x WHERE c = old.x; END; } } {} do_test e_fkey-19.2 { catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' } } {1 {foreign key constraint failed}} do_test e_fkey-19.3 { execsql { UPDATE parent SET x = 'key two' WHERE x = 'key2'; SELECT * FROM child2; } } {{key two}} drop_all_tables do_test e_fkey-19.4 { execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT); CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION); INSERT INTO parent VALUES('key1'); INSERT INTO parent VALUES('key2'); INSERT INTO child1 VALUES('key1'); INSERT INTO child2 VALUES('key2'); CREATE TRIGGER parent_t AFTER DELETE ON parent BEGIN UPDATE child1 SET c = NULL WHERE c = old.x; UPDATE child2 SET c = NULL WHERE c = old.x; END; } } {} do_test e_fkey-19.5 { catchsql { DELETE FROM parent WHERE x = 'key1' } } {1 {foreign key constraint failed}} do_test e_fkey-19.6 { execsql { DELETE FROM parent WHERE x = 'key2'; SELECT * FROM child2; } } {{}} drop_all_tables do_test e_fkey-19.7 { execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT); CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION); INSERT INTO parent VALUES('key1'); INSERT INTO parent VALUES('key2'); INSERT INTO child1 VALUES('key1'); INSERT INTO child2 VALUES('key2'); } } {} do_test e_fkey-19.8 { catchsql { REPLACE INTO parent VALUES('key1') } } {1 {foreign key constraint failed}} do_test e_fkey-19.9 { execsql { REPLACE INTO parent VALUES('key2'); SELECT * FROM child2; } } {key2} #------------------------------------------------------------------------- # /* EV: R-24179-60523 */ # # Test that RESTRICT is enforced immediately, even for a DEFERRED constraint. # drop_all_tables do_test e_fkey-20.1 { execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child1(c REFERENCES parent ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE child2(c REFERENCES parent ON UPDATE NO ACTION DEFERRABLE INITIALLY DEFERRED ); INSERT INTO parent VALUES('key1'); INSERT INTO parent VALUES('key2'); INSERT INTO child1 VALUES('key1'); INSERT INTO child2 VALUES('key2'); BEGIN; } } {} do_test e_fkey-20.2 { catchsql { UPDATE parent SET x = 'key one' WHERE x = 'key1' } } {1 {foreign key constraint failed}} do_test e_fkey-20.3 { execsql { UPDATE parent SET x = 'key two' WHERE x = 'key2' } } {} do_test e_fkey-20.4 { catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-20.5 { execsql { UPDATE child2 SET c = 'key two'; COMMIT; } } {} drop_all_tables do_test e_fkey-20.6 { execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child1(c REFERENCES parent ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE child2(c REFERENCES parent ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ); INSERT INTO parent VALUES('key1'); INSERT INTO parent VALUES('key2'); INSERT INTO child1 VALUES('key1'); INSERT INTO child2 VALUES('key2'); BEGIN; } } {} do_test e_fkey-20.7 { catchsql { DELETE FROM parent WHERE x = 'key1' } } {1 {foreign key constraint failed}} do_test e_fkey-20.8 { execsql { DELETE FROM parent WHERE x = 'key2' } } {} do_test e_fkey-20.9 { catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-20.10 { execsql { UPDATE child2 SET c = NULL; COMMIT; } } {} #------------------------------------------------------------------------- # /* EV: R-03353-05327 */ # # Test SET NULL actions. # drop_all_tables do_test e_fkey-21.1 { execsql { CREATE TABLE pA(x PRIMARY KEY); CREATE TABLE cA(c REFERENCES pA ON DELETE SET NULL); CREATE TABLE cB(c REFERENCES pA ON UPDATE SET NULL); INSERT INTO pA VALUES(X'ABCD'); INSERT INTO pA VALUES(X'1234'); INSERT INTO cA VALUES(X'ABCD'); INSERT INTO cB VALUES(X'1234'); } } {} do_test e_fkey-21.2 { execsql { DELETE FROM pA WHERE rowid = 1; SELECT quote(x) FROM pA; } } {X'1234'} do_test e_fkey-21.3 { execsql { SELECT quote(c) FROM cA; } } {NULL} do_test e_fkey-21.4 { execsql { UPDATE pA SET x = X'8765' WHERE rowid = 2; SELECT quote(x) FROM pA; } } {X'8765'} do_test e_fkey-21.5 { execsql { SELECT quote(c) FROM cB } } {NULL} #------------------------------------------------------------------------- # /* EV: R-43054-54832 */ # # Test SET DEFAULT actions. # drop_all_tables do_test e_fkey-22.1 { execsql { CREATE TABLE pA(x PRIMARY KEY); CREATE TABLE cA(c DEFAULT X'0000' REFERENCES pA ON DELETE SET DEFAULT); CREATE TABLE cB(c DEFAULT X'9999' REFERENCES pA ON UPDATE SET DEFAULT); INSERT INTO pA(rowid, x) VALUES(1, X'0000'); INSERT INTO pA(rowid, x) VALUES(2, X'9999'); INSERT INTO pA(rowid, x) VALUES(3, X'ABCD'); INSERT INTO pA(rowid, x) VALUES(4, X'1234'); INSERT INTO cA VALUES(X'ABCD'); INSERT INTO cB VALUES(X'1234'); } } {} do_test e_fkey-22.2 { execsql { DELETE FROM pA WHERE rowid = 3; SELECT quote(x) FROM pA; } } {X'0000' X'9999' X'1234'} do_test e_fkey-22.3 { execsql { SELECT quote(c) FROM cA } } {X'0000'} do_test e_fkey-22.4 { execsql { UPDATE pA SET x = X'8765' WHERE rowid = 4; SELECT quote(x) FROM pA; } } {X'0000' X'9999' X'8765'} do_test e_fkey-22.5 { execsql { SELECT quote(c) FROM cB } } {X'9999'} #------------------------------------------------------------------------- # /* EV: R-61376-57267 */ # /* EV: R-61809-62207 */ # # Test ON DELETE CASCADE actions. # drop_all_tables do_test e_fkey-23.1 { execsql { CREATE TABLE p1(a, b UNIQUE); CREATE TABLE c1(c REFERENCES p1(b) ON DELETE CASCADE, d); INSERT INTO p1 VALUES(NULL, NULL); INSERT INTO p1 VALUES(4, 4); INSERT INTO p1 VALUES(5, 5); INSERT INTO c1 VALUES(NULL, NULL); INSERT INTO c1 VALUES(4, 4); INSERT INTO c1 VALUES(5, 5); SELECT count(*) FROM c1; } } {3} do_test e_fkey-23.2 { execsql { DELETE FROM p1 WHERE a = 4; SELECT d, c FROM c1; } } {{} {} 5 5} do_test e_fkey-23.3 { execsql { DELETE FROM p1; SELECT d, c FROM c1; } } {{} {}} do_test e_fkey-23.4 { execsql { SELECT * FROM p1 } } {} #------------------------------------------------------------------------- # /* EV: R-61376-57267 */ # /* EV: R-13877-64542 */ # # Test ON UPDATE CASCADE actions. # drop_all_tables do_test e_fkey-24.1 { execsql { CREATE TABLE p1(a, b UNIQUE); CREATE TABLE c1(c REFERENCES p1(b) ON UPDATE CASCADE, d); INSERT INTO p1 VALUES(NULL, NULL); INSERT INTO p1 VALUES(4, 4); INSERT INTO p1 VALUES(5, 5); INSERT INTO c1 VALUES(NULL, NULL); INSERT INTO c1 VALUES(4, 4); INSERT INTO c1 VALUES(5, 5); SELECT count(*) FROM c1; } } {3} do_test e_fkey-24.2 { execsql { UPDATE p1 SET b = 10 WHERE b = 5; SELECT d, c FROM c1; } } {{} {} 4 4 5 10} do_test e_fkey-24.3 { execsql { UPDATE p1 SET b = 11 WHERE b = 4; SELECT d, c FROM c1; } } {{} {} 4 11 5 10} do_test e_fkey-24.4 { execsql { UPDATE p1 SET b = 6 WHERE b IS NULL; SELECT d, c FROM c1; } } {{} {} 4 11 5 10} do_test e_fkey-23.5 { execsql { SELECT * FROM p1 } } {{} 6 4 11 5 10} #------------------------------------------------------------------------- # /* EV: R-51329-33438 */ # # Test an example from the "ON DELETE and ON UPDATE Actions" section # of foreignkeys.html. # drop_all_tables do_test e_fkey-15.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE ); INSERT INTO artist VALUES(1, 'Dean Martin'); INSERT INTO artist VALUES(2, 'Frank Sinatra'); INSERT INTO track VALUES(11, 'That''s Amore', 1); INSERT INTO track VALUES(12, 'Christmas Blues', 1); INSERT INTO track VALUES(13, 'My Way', 2); } } {} do_test e_fkey-15.2 { execsql { UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin'; } } {} do_test e_fkey-15.3 { execsql { SELECT * FROM artist } } {2 {Frank Sinatra} 100 {Dean Martin}} do_test e_fkey-15.4 { execsql { SELECT * FROM track } } {11 {That's Amore} 100 12 {Christmas Blues} 100 13 {My Way} 2} #------------------------------------------------------------------------- # /* EV: R-53968-51642 */ # # Verify that adding an FK action does not absolve the user of the # requirement not to violate the foreign key constraint. # drop_all_tables do_test e_fkey-25.1 { execsql { CREATE TABLE parent(a COLLATE nocase, b, c, PRIMARY KEY(c, a)); CREATE TABLE child(d DEFAULT 'a', e, f DEFAULT 'c', FOREIGN KEY(f, d) REFERENCES parent ON UPDATE SET DEFAULT ); INSERT INTO parent VALUES('A', 'b', 'c'); INSERT INTO parent VALUES('ONE', 'two', 'three'); INSERT INTO child VALUES('one', 'two', 'three'); } } {} do_test e_fkey-25.2 { execsql { BEGIN; UPDATE parent SET a = '' WHERE a = 'oNe'; SELECT * FROM child; } } {a two c} do_test e_fkey-25.3 { execsql { ROLLBACK; DELETE FROM parent WHERE a = 'A'; SELECT * FROM parent; } } {ONE two three} do_test e_fkey-25.4 { catchsql { UPDATE parent SET a = '' WHERE a = 'oNe' } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # /* EV: R-07065-59588 */ # /* EV: R-28220-46694 */ # # Test an example from the "ON DELETE and ON UPDATE Actions" section # of foreignkeys.html. This example shows that adding an "ON DELETE DEFAULT" # clause does not abrogate the need to satisfy the foreign key constraint # (R-28220-46694). # drop_all_tables do_test e_fkey-14.1 { execsql { CREATE TABLE artist( artistid INTEGER PRIMARY KEY, artistname TEXT ); CREATE TABLE track( trackid INTEGER, trackname TEXT, trackartist INTEGER DEFAULT 0 REFERENCES artist(artistid) ON DELETE SET DEFAULT ); INSERT INTO artist VALUES(3, 'Sammy Davis Jr.'); INSERT INTO track VALUES(14, 'Mr. Bojangles', 3); } } {} do_test e_fkey-14.2 { catchsql { DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.' } } {1 {foreign key constraint failed}} do_test e_fkey-14.3 { execsql { INSERT INTO artist VALUES(0, 'Unknown Artist'); DELETE FROM artist WHERE artistname = 'Sammy Davis Jr.'; } } {} do_test e_fkey-14.4 { execsql { SELECT * FROM artist } } {0 {Unknown Artist}} do_test e_fkey-14.5 { execsql { SELECT * FROM track } } {14 {Mr. Bojangles} 0} #------------------------------------------------------------------------- # /* EV: R-35762-30306 */ # # Check that the order of steps in an UPDATE or DELETE on a parent # table is as follows: # # 1. Execute applicable BEFORE trigger programs, # 2. Check local (non foreign key) constraints, # 3. Update or delete the row in the parent table, # 4. Perform any required foreign key actions, # 5. Execute applicable AFTER trigger programs. # drop_all_tables do_test e_fkey-27.1 { proc maxparent {args} { db one {SELECT max(x) FROM parent} } db func maxparent maxparent execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TRIGGER bu BEFORE UPDATE ON parent BEGIN INSERT INTO parent VALUES(new.x-old.x); END; CREATE TABLE child( a DEFAULT (maxparent()) REFERENCES parent ON UPDATE SET DEFAULT ); CREATE TRIGGER au AFTER UPDATE ON parent BEGIN INSERT INTO parent VALUES(new.x+old.x); END; INSERT INTO parent VALUES(1); INSERT INTO child VALUES(1); } } {} do_test e_fkey-27.2 { execsql { UPDATE parent SET x = 22; SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child; } } {22 21 23 xxx 22} do_test e_fkey-27.3 { execsql { DELETE FROM child; DELETE FROM parent; INSERT INTO parent VALUES(-1); INSERT INTO child VALUES(-1); UPDATE parent SET x = 22; SELECT * FROM parent UNION ALL SELECT 'xxx' UNION ALL SELECT a FROM child; } } {22 23 21 xxx 23} #------------------------------------------------------------------------- # /* EV: R-27383-10246 */ # # Verify that ON UPDATE actions only actually take place if the parent key # is set to a new value that is distinct from the old value. The default # collation sequence and affinity are used to determine if the new value # is 'distinct' from the old or not. # drop_all_tables do_test e_fkey-26.1 { execsql { CREATE TABLE zeus(a INTEGER COLLATE NOCASE, b, PRIMARY KEY(a, b)); CREATE TABLE apollo(c, d, FOREIGN KEY(c, d) REFERENCES zeus ON UPDATE CASCADE ); INSERT INTO zeus VALUES('abc', 'xyz'); INSERT INTO apollo VALUES('ABC', 'xyz'); } execsql { UPDATE zeus SET a = 'aBc'; SELECT * FROM apollo; } } {ABC xyz} do_test e_fkey-26.2 { execsql { UPDATE zeus SET a = 1, b = 1; SELECT * FROM apollo; } } {1 1} do_test e_fkey-26.3 { execsql { UPDATE zeus SET a = 1, b = 1; SELECT typeof(c), c, typeof(d), d FROM apollo; } } {integer 1 integer 1} do_test e_fkey-26.4 { execsql { UPDATE zeus SET a = '1'; SELECT typeof(c), c, typeof(d), d FROM apollo; } } {integer 1 integer 1} do_test e_fkey-26.5 { execsql { UPDATE zeus SET b = '1'; SELECT typeof(c), c, typeof(d), d FROM apollo; } } {integer 1 text 1} do_test e_fkey-26.6 { execsql { UPDATE zeus SET b = NULL; SELECT typeof(c), c, typeof(d), d FROM apollo; } } {integer 1 null {}} #------------------------------------------------------------------------- # /* EV: R-51437-39891 */ # # Test an example from the "ON DELETE and ON UPDATE Actions" section # of foreignkeys.html. This example demonstrates that ON UPDATE actions # only take place if at least one parent key column is set to a value # that is distinct from its previous value. # drop_all_tables do_test e_fkey-13.1 { execsql { CREATE TABLE parent(x PRIMARY KEY); CREATE TABLE child(y REFERENCES parent ON UPDATE SET NULL); INSERT INTO parent VALUES('key'); INSERT INTO child VALUES('key'); } } {} do_test e_fkey-13.2 { execsql { UPDATE parent SET x = 'key'; SELECT IFNULL(y, 'null') FROM child; } } {key} do_test e_fkey-13.3 { execsql { UPDATE parent SET x = 'key2'; SELECT IFNULL(y, 'null') FROM child; } } {null} ########################################################################### ### SECTION 5: CREATE, ALTER and DROP TABLE commands ########################################################################### #------------------------------------------------------------------------- # /* EV: R-36018-21755 */ # /* EV: R-25384-39337 */ # # Test that parent keys are not checked when tables are created. # # Child keys are checked to ensure all component columns exist. If parent # key columns are explicitly specified, SQLite checks to make sure there # are the same number of columns in the child and parent keys. (TODO: This # is tested but does not correspond to any testable statement.) # # /* EV: R-08908-23439 */ # # Also test that the above statements are true regardless of whether or not # foreign keys are enabled: "A CREATE TABLE command operates the same whether # or not foreign key constraints are enabled." # foreach {tn zCreateTbl lRes} { 1 "CREATE TABLE t1(a, b REFERENCES t1)" {0 {}} 2 "CREATE TABLE t1(a, b REFERENCES t2)" {0 {}} 3 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1)" {0 {}} 4 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)" {0 {}} 5 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2)" {0 {}} 6 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t2(n,d))" {0 {}} 7 "CREATE TABLE t1(a, b, FOREIGN KEY(a,b) REFERENCES t1(a,b))" {0 {}} A "CREATE TABLE t1(a, b, FOREIGN KEY(c,b) REFERENCES t2)" {1 {unknown column "c" in foreign key definition}} B "CREATE TABLE t1(a, b, FOREIGN KEY(c,b) REFERENCES t2(d))" {1 {number of columns in foreign key does not match the number of columns in the referenced table}} } { do_test e_fkey-5.$tn.off { drop_all_tables execsql {PRAGMA foreign_keys = OFF} catchsql $zCreateTbl } $lRes do_test e_fkey-5.$tn.on { drop_all_tables execsql {PRAGMA foreign_keys = ON} catchsql $zCreateTbl } $lRes } #------------------------------------------------------------------------- # /* EV: R-47952-62498 */ # proc test_efkey_6 {tn zAlter isError} { drop_all_tables do_test e_fkey-6.$tn.1 " execsql { CREATE TABLE tbl(a, b) } [list catchsql $zAlter] " [lindex {{0 {}} {1 {Cannot add a REFERENCES column with non-NULL default value}}} $isError] } test_efkey_6 1 "ALTER TABLE tbl ADD COLUMN c REFERENCES xx" 0 test_efkey_6 2 "ALTER TABLE tbl ADD COLUMN c DEFAULT NULL REFERENCES xx" 0 test_efkey_6 3 "ALTER TABLE tbl ADD COLUMN c DEFAULT 0 REFERENCES xx" 1 #------------------------------------------------------------------------- # /* EV: R-47080-02069 */ # # Test that ALTER TABLE adjusts REFERENCES clauses when the parent table # is RENAMED. # # /* EV: R-63827-54774 */ # # Test that these adjustments are visible in the sqlite_master table. # do_test e_fkey-7.1 { drop_all_tables execsql { CREATE TABLE 'p 1 "parent one"'(a REFERENCES 'p 1 "parent one"', b, PRIMARY KEY(b)); CREATE TABLE c1(c, d REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE); CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE); CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES 'p 1 "parent one"' ON UPDATE CASCADE); INSERT INTO 'p 1 "parent one"' VALUES(1, 1); INSERT INTO c1 VALUES(1, 1); INSERT INTO c2 VALUES(1, 1); INSERT INTO c3 VALUES(1, 1); -- CREATE TABLE q(a, b, PRIMARY KEY(b)); } } {} do_test e_fkey-7.2 { execsql { ALTER TABLE 'p 1 "parent one"' RENAME TO p } } {} do_test e_fkey-7.3 { execsql { UPDATE p SET a = 'xxx', b = 'xxx'; SELECT * FROM p; SELECT * FROM c1; SELECT * FROM c2; SELECT * FROM c3; } } {xxx xxx 1 xxx 1 xxx 1 xxx} do_test e_fkey-7.4 { execsql { SELECT sql FROM sqlite_master WHERE type = 'table'} } [list \ {CREATE TABLE "p"(a REFERENCES "p", b, PRIMARY KEY(b))} \ {CREATE TABLE c1(c, d REFERENCES "p" ON UPDATE CASCADE)} \ {CREATE TABLE c2(e, f, FOREIGN KEY(f) REFERENCES "p" ON UPDATE CASCADE)} \ {CREATE TABLE c3(e, 'f col 2', FOREIGN KEY('f col 2') REFERENCES "p" ON UPDATE CASCADE)} \ ] #------------------------------------------------------------------------- # /* EV: R-14208-23986 */ # /* EV: R-11078-03945 */ # # Check that a DROP TABLE does an implicit DELETE FROM. Which does not # cause any triggers to fire, but does fire foreign key actions. # do_test e_fkey-8.1 { drop_all_tables execsql { CREATE TABLE p(a, b, PRIMARY KEY(a, b)); CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET NULL); CREATE TABLE c2(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE SET DEFAULT); CREATE TABLE c3(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE CASCADE); CREATE TABLE c4(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE RESTRICT); CREATE TABLE c5(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE NO ACTION); CREATE TABLE c6(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE c7(c, d, FOREIGN KEY(c, d) REFERENCES p ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ); CREATE TABLE log(msg); CREATE TRIGGER tt AFTER DELETE ON p BEGIN INSERT INTO log VALUES('delete ' || old.rowid); END; } } {} do_test e_fkey-8.2 { execsql { INSERT INTO p VALUES('a', 'b'); INSERT INTO c1 VALUES('a', 'b'); INSERT INTO c2 VALUES('a', 'b'); INSERT INTO c3 VALUES('a', 'b'); BEGIN; DROP TABLE p; SELECT * FROM c1; } } {{} {}} do_test e_fkey-8.3 { execsql { SELECT * FROM c2 } } {{} {}} do_test e_fkey-8.4 { execsql { SELECT * FROM c3 } } {} do_test e_fkey-8.5 { execsql { SELECT * FROM log } } {} do_test e_fkey-8.6 { execsql ROLLBACK } {} do_test e_fkey-8.7 { execsql { BEGIN; DELETE FROM p; SELECT * FROM log; ROLLBACK; } } {{delete 1}} #------------------------------------------------------------------------- # /* EV: R-32768-47925 */ # # If an IMMEDIATE foreign key fails as a result of a DROP TABLE, the # DROP TABLE command fails. # do_test e_fkey-9.1 { execsql { DELETE FROM c1; DELETE FROM c2; DELETE FROM c3; } execsql { INSERT INTO c5 VALUES('a', 'b') } catchsql { DROP TABLE p } } {1 {foreign key constraint failed}} do_test e_fkey-9.2 { execsql { SELECT * FROM p } } {a b} do_test e_fkey-9.3 { catchsql { BEGIN; DROP TABLE p; } } {1 {foreign key constraint failed}} do_test e_fkey-9.4 { execsql { SELECT * FROM p; SELECT * FROM c5; ROLLBACK; } } {a b a b} #------------------------------------------------------------------------- # /* EV: R-05903-08460 */ # # If a DEFERRED foreign key fails as a result of a DROP TABLE, attempting # to commit the transaction fails unless the violation is fixed. # do_test e_fkey-10.1 { execsql { DELETE FROM c1 ; DELETE FROM c2 ; DELETE FROM c3 ; DELETE FROM c4 ; DELETE FROM c5 ; DELETE FROM c6 ; DELETE FROM c7 } } {} do_test e_fkey-10.2 { execsql { INSERT INTO c7 VALUES('a', 'b') } execsql { BEGIN; DROP TABLE p; } } {} do_test e_fkey-10.3 { catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-10.4 { execsql { CREATE TABLE p(a, b, PRIMARY KEY(a, b)) } catchsql COMMIT } {1 {foreign key constraint failed}} do_test e_fkey-10.5 { execsql { INSERT INTO p VALUES('a', 'b') } execsql COMMIT } {} #------------------------------------------------------------------------- # /* EV: R-57242-37005 */ # # Any "foreign key mismatch" errors encountered while running an implicit # "DELETE FROM tbl" are ignored. # drop_all_tables do_test e_fkey-11.1 { execsql { PRAGMA foreign_keys = OFF; CREATE TABLE p(a PRIMARY KEY, b REFERENCES nosuchtable); CREATE TABLE c1(c, d, FOREIGN KEY(c, d) REFERENCES a); CREATE TABLE c2(c REFERENCES p(b), d); CREATE TABLE c3(c REFERENCES p ON DELETE SET NULL, d); INSERT INTO p VALUES(1, 2); INSERT INTO c1 VALUES(1, 2); INSERT INTO c2 VALUES(1, 2); INSERT INTO c3 VALUES(1, 2); } } {} do_test e_fkey-11.2 { execsql { PRAGMA foreign_keys = ON } catchsql { DELETE FROM p } } {1 {no such table: main.nosuchtable}} do_test e_fkey-11.3 { execsql { BEGIN; DROP TABLE p; SELECT * FROM c3; ROLLBACK; } } {{} 2} do_test e_fkey-11.4 { execsql { CREATE TABLE nosuchtable(x PRIMARY KEY) } catchsql { DELETE FROM p } } {1 {foreign key mismatch}} do_test e_fkey-11.5 { execsql { DROP TABLE c1 } catchsql { DELETE FROM p } } {1 {foreign key mismatch}} do_test e_fkey-11.6 { execsql { DROP TABLE c2 } execsql { DELETE FROM p } } {} #------------------------------------------------------------------------- # /* EV: R-54142-41346 */ # # Test that the special behaviours of ALTER and DROP TABLE are only # activated when foreign keys are enabled. Special behaviours are: # # 1. ADD COLUMN not allowing a REFERENCES clause with a non-NULL # default value. # 2. Modifying foreign key definitions when a parent table is RENAMEd. # 3. Running an implicit DELETE FROM command as part of DROP TABLE. # do_test e_fkey-12.1.1 { drop_all_tables execsql { CREATE TABLE t1(a, b) } catchsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 } } {1 {Cannot add a REFERENCES column with non-NULL default value}} do_test e_fkey-12.1.2 { execsql { PRAGMA foreign_keys = OFF } execsql { ALTER TABLE t1 ADD COLUMN c DEFAULT 'xxx' REFERENCES t2 } execsql { SELECT sql FROM sqlite_master WHERE name = 't1' } } {{CREATE TABLE t1(a, b, c DEFAULT 'xxx' REFERENCES t2)}} do_test e_fkey-12.1.3 { execsql { PRAGMA foreign_keys = ON } } {} do_test e_fkey-12.2.1 { drop_all_tables execsql { CREATE TABLE p(a UNIQUE); CREATE TABLE c(b REFERENCES p(a)); BEGIN; ALTER TABLE p RENAME TO parent; SELECT sql FROM sqlite_master WHERE name = 'c'; ROLLBACK; } } {{CREATE TABLE c(b REFERENCES "parent"(a))}} do_test e_fkey-12.2.2 { execsql { PRAGMA foreign_keys = OFF; ALTER TABLE p RENAME TO parent; SELECT sql FROM sqlite_master WHERE name = 'c'; } } {{CREATE TABLE c(b REFERENCES p(a))}} do_test e_fkey-12.2.3 { execsql { PRAGMA foreign_keys = ON } } {} do_test e_fkey-12.3.1 { drop_all_tables execsql { CREATE TABLE p(a UNIQUE); CREATE TABLE c(b REFERENCES p(a) ON DELETE SET NULL); INSERT INTO p VALUES('x'); INSERT INTO c VALUES('x'); BEGIN; DROP TABLE p; SELECT * FROM c; ROLLBACK; } } {{}} do_test e_fkey-12.3.2 { execsql { PRAGMA foreign_keys = OFF; DROP TABLE p; SELECT * FROM c; } } {x} do_test e_fkey-12.3.3 { execsql { PRAGMA foreign_keys = ON } } {} ########################################################################### ### SECTION 6: Limits and Unsupported Features ########################################################################### #------------------------------------------------------------------------- # /* EV: R-24728-13230 */ # /* EV: R-24450-46174 */ # # Test that MATCH clauses are parsed, but SQLite treats every foreign key # constraint as if it were "MATCH SIMPLE". # foreach zMatch [list SIMPLE PARTIAL FULL Simple parTIAL FuLL ] { drop_all_tables do_test e_fkey-1.$zMatch.1 { execsql " CREATE TABLE p(a, b, c, PRIMARY KEY(b, c)); CREATE TABLE c(d, e, f, FOREIGN KEY(e, f) REFERENCES p MATCH $zMatch); " } {} do_test e_fkey-1.$zMatch.2 { execsql { INSERT INTO p VALUES(1, 2, 3) } # MATCH SIMPLE behaviour: Allow any child key that contains one or more # NULL value to be inserted. Non-NULL values do not have to map to any # parent key values, so long as at least one field of the child key is # NULL. execsql { INSERT INTO c VALUES('w', 2, 3) } execsql { INSERT INTO c VALUES('x', 'x', NULL) } execsql { INSERT INTO c VALUES('y', NULL, 'x') } execsql { INSERT INTO c VALUES('z', NULL, NULL) } # Check that the FK is enforced properly if there are no NULL values # in the child key columns. catchsql { INSERT INTO c VALUES('a', 2, 4) } } {1 {foreign key constraint failed}} } #------------------------------------------------------------------------- # /* EV: R-21599-16038 */ # # Test that SQLite does not support the SET CONSTRAINT statement. And # that it is possible to create both immediate and deferred constraints. # drop_all_tables do_test e_fkey-2.1 { catchsql { SET CONSTRAINTS ALL IMMEDIATE } } {1 {near "SET": syntax error}} do_test e_fkey-2.2 { catchsql { SET CONSTRAINTS ALL DEFERRED } } {1 {near "SET": syntax error}} do_test e_fkey-2.3 { execsql { CREATE TABLE p(a, b, PRIMARY KEY(a, b)); CREATE TABLE cd(c, d, FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY DEFERRED); CREATE TABLE ci(c, d, FOREIGN KEY(c, d) REFERENCES p DEFERRABLE INITIALLY IMMEDIATE); BEGIN; } } {} do_test e_fkey-2.4 { catchsql { INSERT INTO ci VALUES('x', 'y') } } {1 {foreign key constraint failed}} do_test e_fkey-2.5 { catchsql { INSERT INTO cd VALUES('x', 'y') } } {0 {}} do_test e_fkey-2.6 { catchsql { COMMIT } } {1 {foreign key constraint failed}} do_test e_fkey-2.7 { execsql { DELETE FROM cd; COMMIT; } } {} #------------------------------------------------------------------------- # /* EV: R-42264-30503 */ # # Test that the maximum recursion depth of foreign key action programs is # governed by the SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH # settings. # proc test_on_delete_recursion {limit} { drop_all_tables execsql { BEGIN; CREATE TABLE t0(a PRIMARY KEY, b); INSERT INTO t0 VALUES('x0', NULL); } for {set i 1} {$i <= $limit} {incr i} { execsql " CREATE TABLE t$i ( a PRIMARY KEY, b REFERENCES t[expr $i-1] ON DELETE CASCADE ); INSERT INTO t$i VALUES('x$i', 'x[expr $i-1]'); " } execsql COMMIT catchsql " DELETE FROM t0; SELECT count(*) FROM t$limit; " } proc test_on_update_recursion {limit} { drop_all_tables execsql { BEGIN; CREATE TABLE t0(a PRIMARY KEY); INSERT INTO t0 VALUES('xxx'); } for {set i 1} {$i <= $limit} {incr i} { set j [expr $i-1] execsql " CREATE TABLE t$i (a PRIMARY KEY REFERENCES t$j ON UPDATE CASCADE); INSERT INTO t$i VALUES('xxx'); " } execsql COMMIT catchsql " UPDATE t0 SET a = 'yyy'; SELECT NOT (a='yyy') FROM t$limit; " } do_test e_fkey-3.1.1 { test_on_delete_recursion $SQLITE_MAX_TRIGGER_DEPTH } {0 0} do_test e_fkey-3.1.2 { test_on_delete_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1] } {1 {too many levels of trigger recursion}} do_test e_fkey-3.1.3 { sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5 test_on_delete_recursion 5 } {0 0} do_test e_fkey-3.1.4 { test_on_delete_recursion 6 } {1 {too many levels of trigger recursion}} do_test e_fkey-3.1.5 { sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000 } {5} do_test e_fkey-3.2.1 { test_on_update_recursion $SQLITE_MAX_TRIGGER_DEPTH } {0 0} do_test e_fkey-3.2.2 { test_on_update_recursion [expr $SQLITE_MAX_TRIGGER_DEPTH+1] } {1 {too many levels of trigger recursion}} do_test e_fkey-3.2.3 { sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 5 test_on_update_recursion 5 } {0 0} do_test e_fkey-3.2.4 { test_on_update_recursion 6 } {1 {too many levels of trigger recursion}} do_test e_fkey-3.2.5 { sqlite3_limit db SQLITE_LIMIT_TRIGGER_DEPTH 1000000 } {5} #------------------------------------------------------------------------- # /* EV: R-51769-32730 */ # # The setting of the recursive_triggers pragma does not affect foreign # key actions. # foreach recursive_triggers_setting [list 0 1 ON OFF] { drop_all_tables execsql "PRAGMA recursive_triggers = $recursive_triggers_setting" do_test e_fkey-4.$recursive_triggers_setting.1 { execsql { CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1 ON DELETE CASCADE); INSERT INTO t1 VALUES(1, NULL); INSERT INTO t1 VALUES(2, 1); INSERT INTO t1 VALUES(3, 2); INSERT INTO t1 VALUES(4, 3); INSERT INTO t1 VALUES(5, 4); SELECT count(*) FROM t1; } } {5} do_test e_fkey-4.$recursive_triggers_setting.2 { execsql { SELECT count(*) FROM t1 WHERE a = 1 } } {1} do_test e_fkey-4.$recursive_triggers_setting.3 { execsql { DELETE FROM t1 WHERE a = 1; SELECT count(*) FROM t1; } } {0} } finish_test