1# 2001 September 15 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. 12# 13# This file implements tests for foreign keys. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set testprefix fkey8 19 20ifcapable {!foreignkey} { 21 finish_test 22 return 23} 24do_execsql_test 1.0 { PRAGMA foreign_keys = 1; } 25 26 27foreach {tn use_stmt sql schema} { 28 1 1 "DELETE FROM p1" { 29 CREATE TABLE p1(a PRIMARY KEY); 30 CREATE TABLE c1(b REFERENCES p1); 31 } 32 33 2.1 0 "DELETE FROM p1" { 34 CREATE TABLE p1(a PRIMARY KEY); 35 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE); 36 } 37 2.2 0 "DELETE FROM p1" { 38 CREATE TABLE p1(a PRIMARY KEY); 39 CREATE TABLE c1(b REFERENCES p1 ON DELETE SET NULL); 40 } 41 2.3 1 "DELETE FROM p1" { 42 CREATE TABLE p1(a PRIMARY KEY); 43 CREATE TABLE c1(b REFERENCES p1 ON DELETE SET DEFAULT); 44 } 45 46 3 1 "DELETE FROM p1" { 47 CREATE TABLE p1(a PRIMARY KEY); 48 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE); 49 CREATE TRIGGER ct1 AFTER DELETE ON c1 BEGIN 50 INSERT INTO p1 VALUES('x'); 51 END; 52 } 53 54 4 1 "DELETE FROM p1" { 55 CREATE TABLE p1(a PRIMARY KEY); 56 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); 57 CREATE TABLE cc1(d REFERENCES c1); 58 } 59 60 5.1 0 "DELETE FROM p1" { 61 CREATE TABLE p1(a PRIMARY KEY); 62 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); 63 CREATE TABLE cc1(d REFERENCES c1 ON DELETE CASCADE); 64 } 65 5.2 0 "DELETE FROM p1" { 66 CREATE TABLE p1(a PRIMARY KEY); 67 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); 68 CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET NULL); 69 } 70 5.3 1 "DELETE FROM p1" { 71 CREATE TABLE p1(a PRIMARY KEY); 72 CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); 73 CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET DEFAULT); 74 } 75 76 6.1 1 "UPDATE p1 SET a = ?" { 77 CREATE TABLE p1(a PRIMARY KEY); 78 CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c); 79 } 80 6.2 0 "UPDATE OR IGNORE p1 SET a = ?" { 81 CREATE TABLE p1(a PRIMARY KEY); 82 CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c); 83 } 84 6.3 1 "UPDATE OR IGNORE p1 SET a = ?" { 85 CREATE TABLE p1(a PRIMARY KEY); 86 CREATE TABLE c1(b REFERENCES p1 ON UPDATE CASCADE, c); 87 } 88 6.4 1 "UPDATE OR IGNORE p1 SET a = ?" { 89 CREATE TABLE p1(a PRIMARY KEY); 90 CREATE TABLE c1(b NOT NULL REFERENCES p1 ON UPDATE SET NULL, c); 91 } 92 93} { 94 drop_all_tables 95 do_test 1.$tn { 96 execsql $schema 97 set stmt [sqlite3_prepare_v2 db $sql -1 dummy] 98 set ret [uses_stmt_journal $stmt] 99 sqlite3_finalize $stmt 100 set ret 101 } $use_stmt 102} 103 104#------------------------------------------------------------------------- 105# The following tests check that foreign key constaint counters are 106# correctly updated for any implicit DELETE operations that occur 107# when a REPLACE command is executed against a WITHOUT ROWID table 108# that has no triggers or auxiliary indexes. 109# 110reset_db 111do_execsql_test 2.1.0 { 112 PRAGMA foreign_keys = on; 113 CREATE TABLE p1(a PRIMARY KEY, b) WITHOUT ROWID; 114 CREATE TABLE c1(x REFERENCES p1 DEFERRABLE INITIALLY DEFERRED); 115 116 INSERT INTO p1 VALUES(1, 'one'); 117 INSERT INTO p1 VALUES(2, 'two'); 118 INSERT INTO c1 VALUES(1); 119 INSERT INTO c1 VALUES(2); 120} 121 122do_catchsql_test 2.1.2 { 123 BEGIN; 124 DELETE FROM p1 WHERE a=1; 125 INSERT OR REPLACE INTO p1 VALUES(2, 'two'); 126 COMMIT; 127} {1 {FOREIGN KEY constraint failed}} 128 129reset_db 130do_execsql_test 2.2.0 { 131 PRAGMA foreign_keys = on; 132 CREATE TABLE p2(a PRIMARY KEY, b); 133 CREATE TABLE c2( 134 x PRIMARY KEY, 135 y REFERENCES p2 DEFERRABLE INITIALLY DEFERRED 136 ) WITHOUT ROWID; 137} 138 139do_catchsql_test 2.2.1 { 140 BEGIN; 141 INSERT INTO c2 VALUES(13, 13); 142 INSERT OR REPLACE INTO c2 VALUES(13, 13); 143 DELETE FROM c2; 144 COMMIT; 145} {0 {}} 146 147reset_db 148do_execsql_test 2.3.0 { 149 PRAGMA foreign_keys = on; 150 CREATE TABLE p3(a PRIMARY KEY, b) WITHOUT ROWID; 151 CREATE TABLE c3(x REFERENCES p3); 152 153 INSERT INTO p3 VALUES(1, 'one'); 154 INSERT INTO p3 VALUES(2, 'two'); 155 INSERT INTO c3 VALUES(1); 156 INSERT INTO c3 VALUES(2); 157 158 CREATE TRIGGER p3d AFTER DELETE ON p3 WHEN old.a=1 BEGIN 159 INSERT OR REPLACE INTO p3 VALUES(2, 'three'); 160 END; 161} 162 163do_catchsql_test 2.3.1 { 164 DELETE FROM p3 WHERE a=1 165} {1 {FOREIGN KEY constraint failed}} 166 167finish_test 168