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 167 168do_execsql_test 3.0 { 169 PRAGMA foreign_keys=ON; 170 CREATE TABLE t2( 171 a PRIMARY KEY, b, c, d, e, 172 FOREIGN KEY(b, c) REFERENCES t2(d, e) 173 ) WITHOUT ROWID; 174 CREATE UNIQUE INDEX idx ON t2(d, e); 175 176 INSERT INTO t2 VALUES(1, 'one', 'one', 'one', 'one'); -- row is parent of self 177 INSERT INTO t2 VALUES(2, 'one', 'one', 'one', NULL); -- parent is row 1 178} 179 180do_catchsql_test 3.1 { 181 DELETE FROM t2 WHERE a=1; 182} {1 {FOREIGN KEY constraint failed}} 183 184do_execsql_test 4.0 { 185 CREATE TABLE t1 ( 186 c1 PRIMARY KEY, 187 c2 NUMERIC, 188 FOREIGN KEY(c1) REFERENCES t1(c2) 189 ) WITHOUT ROWID ; 190 CREATE INDEX t1c1 ON t1(c1); 191 CREATE UNIQUE INDEX t1c1unique ON t1(c2); 192} 193do_catchsql_test 4.1 { 194 INSERT OR REPLACE INTO t1 VALUES(10000, 20000); 195} {1 {FOREIGN KEY constraint failed}} 196do_execsql_test 4.2 { 197 INSERT OR REPLACE INTO t1 VALUES(20000, 20000); 198} 199 200#------------------------------------------------------------------------- 201reset_db 202do_execsql_test 5.0 { 203 PRAGMA foreign_keys = true; 204 CREATE TABLE parent( 205 p TEXT PRIMARY KEY 206 ); 207 CREATE TABLE child( 208 c INTEGER UNIQUE, 209 FOREIGN KEY(c) REFERENCES parent(p) DEFERRABLE INITIALLY DEFERRED 210 ); 211 BEGIN; 212 INSERT INTO child VALUES(123); 213 INSERT INTO parent VALUES('123'); 214 COMMIT; 215} 216do_execsql_test 5.1 { 217 PRAGMA integrity_check; 218} {ok} 219 220do_execsql_test 5.2 { 221 INSERT INTO parent VALUES(1200); 222 BEGIN; 223 INSERT INTO child VALUES(456); 224 UPDATE parent SET p = '456' WHERE p=1200; 225 COMMIT; 226} 227do_execsql_test 5.3 { 228 PRAGMA integrity_check; 229} {ok} 230 231#------------------------------------------------------------------------- 232reset_db 233forcedelete test.db2 234do_execsql_test 6.1 { 235 PRAGMA foreign_keys = on; 236 CREATE TABLE c1(b); 237 INSERT INTO c1 VALUES(123); 238} 239 240do_execsql_test 6.2 { 241 ATTACH 'test.db2' AS aux; 242 CREATE TABLE aux.p1(a INTEGER PRIMARY KEY); 243 CREATE TABLE aux.c1(b REFERENCES p1(a) ON DELETE RESTRICT); 244 245 INSERT INTO aux.p1 VALUES(123); 246} 247 248do_execsql_test 6.3 { 249 DELETE FROM aux.p1 WHERE a=123; 250} 251 252 253finish_test 254