gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/fkey1.test (about) 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 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 set testprefix fkey1 19 20 ifcapable {!foreignkey} { 21 finish_test 22 return 23 } 24 25 # Create a table and some data to work with. 26 # 27 do_test fkey1-1.0 { 28 execsql { 29 CREATE TABLE t1( 30 a INTEGER PRIMARY KEY, 31 b INTEGER 32 REFERENCES t1 ON DELETE CASCADE 33 REFERENCES t2, 34 c TEXT, 35 FOREIGN KEY (b,c) REFERENCES t2(x,y) ON UPDATE CASCADE 36 ); 37 } 38 } {} 39 do_test fkey1-1.1 { 40 execsql { 41 CREATE TABLE t2( 42 x INTEGER PRIMARY KEY, 43 y TEXT 44 ); 45 } 46 } {} 47 do_test fkey1-1.2 { 48 execsql { 49 CREATE TABLE t3( 50 a INTEGER REFERENCES t2, 51 b INTEGER REFERENCES t1, 52 FOREIGN KEY (a,b) REFERENCES t2(x,y) 53 ); 54 } 55 } {} 56 57 do_test fkey1-2.1 { 58 execsql { 59 CREATE TABLE t4(a integer primary key); 60 CREATE TABLE t5(x references t4); 61 CREATE TABLE t6(x references t4); 62 CREATE TABLE t7(x references t4); 63 CREATE TABLE t8(x references t4); 64 CREATE TABLE t9(x references t4); 65 CREATE TABLE t10(x references t4); 66 DROP TABLE t7; 67 DROP TABLE t9; 68 DROP TABLE t5; 69 DROP TABLE t8; 70 DROP TABLE t6; 71 DROP TABLE t10; 72 } 73 } {} 74 75 do_test fkey1-3.1 { 76 execsql { 77 CREATE TABLE t5(a PRIMARY KEY, b, c); 78 CREATE TABLE t6( 79 d REFERENCES t5, 80 e REFERENCES t5(c) 81 ); 82 PRAGMA foreign_key_list(t6); 83 } 84 } [concat \ 85 {0 0 t5 e c {NO ACTION} {NO ACTION} NONE} \ 86 {1 0 t5 d {} {NO ACTION} {NO ACTION} NONE} \ 87 ] 88 do_test fkey1-3.2 { 89 execsql { 90 CREATE TABLE t7(d, e, f, 91 FOREIGN KEY (d, e) REFERENCES t5(a, b) 92 ); 93 PRAGMA foreign_key_list(t7); 94 } 95 } [concat \ 96 {0 0 t5 d a {NO ACTION} {NO ACTION} NONE} \ 97 {0 1 t5 e b {NO ACTION} {NO ACTION} NONE} \ 98 ] 99 do_test fkey1-3.3 { 100 execsql { 101 CREATE TABLE t8(d, e, f, 102 FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL 103 ); 104 PRAGMA foreign_key_list(t8); 105 } 106 } [concat \ 107 {0 0 t5 d {} {SET NULL} CASCADE NONE} \ 108 {0 1 t5 e {} {SET NULL} CASCADE NONE} \ 109 ] 110 do_test fkey1-3.4 { 111 execsql { 112 CREATE TABLE t9(d, e, f, 113 FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET DEFAULT 114 ); 115 PRAGMA foreign_key_list(t9); 116 } 117 } [concat \ 118 {0 0 t5 d {} {SET DEFAULT} CASCADE NONE} \ 119 {0 1 t5 e {} {SET DEFAULT} CASCADE NONE} \ 120 ] 121 do_test fkey1-3.5 { 122 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 123 } {0 0 0} 124 125 # Stress the dequoting logic. The first test is not so bad. 126 do_execsql_test fkey1-4.0 { 127 PRAGMA foreign_keys=ON; 128 CREATE TABLE "xx1"("xx2" TEXT PRIMARY KEY, "xx3" TEXT); 129 INSERT INTO "xx1"("xx2","xx3") VALUES('abc','def'); 130 CREATE TABLE "xx4"("xx5" TEXT REFERENCES "xx1" ON DELETE CASCADE); 131 INSERT INTO "xx4"("xx5") VALUES('abc'); 132 INSERT INTO "xx1"("xx2","xx3") VALUES('uvw','xyz'); 133 SELECT 1, "xx5" FROM "xx4"; 134 DELETE FROM "xx1"; 135 SELECT 2, "xx5" FROM "xx4"; 136 } {1 abc} 137 138 # This case is identical to the previous except the "xx" in each name 139 # is changed to a single escaped double-quote character. 140 do_execsql_test fkey1-4.1 { 141 PRAGMA foreign_keys=ON; 142 CREATE TABLE """1"("""2" TEXT PRIMARY KEY, """3" TEXT); 143 INSERT INTO """1"("""2","""3") VALUES('abc','def'); 144 CREATE TABLE """4"("""5" TEXT REFERENCES """1" ON DELETE CASCADE); 145 INSERT INTO """4"("""5") VALUES('abc'); 146 INSERT INTO """1"("""2","""3") VALUES('uvw','xyz'); 147 SELECT 1, """5" FROM """4"; 148 DELETE FROM """1"; 149 SELECT 2, """5" FROM """4"; 150 } {1 abc} 151 do_execsql_test fkey1-4.2 { 152 PRAGMA table_info="""1"; 153 } {0 {"2} TEXT 0 {} 1 1 {"3} TEXT 0 {} 0} 154 155 #------------------------------------------------------------------------- 156 # 157 do_execsql_test fkey1-5.1 { 158 CREATE TABLE t11( 159 x INTEGER PRIMARY KEY, 160 parent REFERENCES t11 ON DELETE CASCADE 161 ); 162 INSERT INTO t11 VALUES (1, NULL), (2, 1), (3, 2); 163 } {} 164 165 # The REPLACE part of this statement deletes the row (2, 1). Then the 166 # DELETE CASCADE caused by deleting that row removes the (3, 2) row. Which 167 # would have been the parent of the new row being inserted. Causing an 168 # FK violation. 169 # 170 do_catchsql_test fkey1-5.2 { 171 INSERT OR REPLACE INTO t11 VALUES (2, 3); 172 } {1 {FOREIGN KEY constraint failed}} 173 174 # Make sure sqlite3_trace() output works with triggers used to implement 175 # FK constraints 176 # 177 ifcapable trace { 178 proc sqltrace {txt} { 179 global traceoutput 180 lappend traceoutput $txt 181 } 182 do_test fkey1-5.2.1 { 183 unset -nocomplain traceoutput 184 db trace sqltrace 185 catch {db eval {INSERT OR REPLACE INTO t11 VALUES(2,3);}} 186 set traceoutput 187 } {{INSERT OR REPLACE INTO t11 VALUES(2,3);} {INSERT OR REPLACE INTO t11 VALUES(2,3);} {INSERT OR REPLACE INTO t11 VALUES(2,3);}} 188 } 189 190 # A similar test to the above. 191 do_execsql_test fkey1-5.3 { 192 CREATE TABLE Foo ( 193 Id INTEGER PRIMARY KEY, 194 ParentId INTEGER REFERENCES Foo(Id) ON DELETE CASCADE, C1 195 ); 196 INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (1, null, 'A'); 197 INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 1, 'A-2-1'); 198 INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (3, 2, 'A-3-2'); 199 INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (4, 3, 'A-4-3'); 200 } 201 do_catchsql_test fkey1-5.4 { 202 INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 3, 'A-2-3'); 203 } {1 {FOREIGN KEY constraint failed}} 204 205 #------------------------------------------------------------------------- 206 # Check that foreign key processing is not fooled by partial indexes 207 # on the parent table. 208 # 209 do_execsql_test 6.0 { 210 CREATE TABLE p1(x, y); 211 CREATE UNIQUE INDEX p1x ON p1(x) WHERE y<2; 212 INSERT INTO p1 VALUES(1, 1); 213 CREATE TABLE c1(a REFERENCES p1(x)); 214 } 215 216 do_catchsql_test 6.1 { 217 INSERT INTO c1 VALUES(1); 218 } {1 {foreign key mismatch - "c1" referencing "p1"}} 219 220 do_execsql_test 6.2 { 221 CREATE UNIQUE INDEX p1x2 ON p1(x); 222 INSERT INTO c1 VALUES(1); 223 } {} 224 225 # 2021-07-03 https://sqlite.org/forum/forumpost/a6b0c05277 226 # 2021-07-07 https://sqlite.org/forum/forumpost/79c9e4797d 227 # Failure to allocate enough registers in the VDBE for a 228 # PRAGMA foreign_key_check when the foreign key has more 229 # columns than the table. 230 # 231 reset_db 232 do_execsql_test 7.1 { 233 PRAGMA foreign_keys=OFF; 234 CREATE TABLE t1(a,b,c,FOREIGN KEY(a,a,a,a,a,a,a,a,a,a,a,a,a,a) REFERENCES t0); 235 INSERT INTO t1 VALUES(1,2,3); 236 PRAGMA foreign_key_check; 237 } {t1 1 t0 0} 238 do_execsql_test 7.2 { 239 DROP TABLE t1; 240 CREATE TABLE t1(a,b,c AS(1),d, FOREIGN KEY(c,d,b,a,b,d,b,c) REFERENCES t0); 241 PRAGMA foreign_key_check; 242 } {} 243 244 # 2021-12-31 forum https://sqlite.org/forum/forumpost/24bd1fef7e9323ef 245 # Memory leak caused by sqlite3NestedParse() running on a corrupt system 246 # table. Discovered by Jingzhou Fu. 247 # 248 reset_db 249 do_execsql_test 8.1 { 250 PRAGMA writable_schema=ON; 251 PRAGMA foreign_keys = ON; 252 CREATE TABLE sqlite_stat1 (tbl INTEGER PRIMARY KEY DESC, idx UNIQUE DEFAULT NULL) WITHOUT ROWID; 253 PRAGMA writable_schema=OFF; 254 CREATE TABLE sqlsim4(stat PRIMARY KEY);; 255 CREATE TABLE t1(sqlsim7 REFERENCES sqlite_stat1 ON DELETE CASCADE); 256 DROP table "sqlsim4"; 257 } {} 258 # 2022-01-01 dbsqlfuzz 1c57440219f6f0aedf5e8f72a8ddd75f15aea381 259 # Follow-up case to the above. Assertion is not true if the schema 260 # is corrupt. 261 reset_db 262 database_may_be_corrupt 263 do_execsql_test 8.2 { 264 CREATE TABLE t1(a REFERENCES sqlite_stat1 ON DELETE CASCADE); 265 CREATE TABLE t2(a TEXT PRIMARY KEY); 266 PRAGMA writable_schema=ON; 267 CREATE TABLE sqlite_stat1(tbl INTEGER PRIMARY KEY DESC, idx UNIQUE DEFAULT NULL) WITHOUT ROWID; 268 UPDATE sqlite_schema SET name='sqlite_autoindex_sqlite_stat1_1' WHERE name='sqlite_autoindex_sqlite_stat1_2'; 269 PRAGMA writable_schema=RESET; 270 } {} 271 do_catchsql_test 8.3 { 272 REINDEX; 273 } {1 {database disk image is malformed}} 274 275 finish_test