xref: /sqlite-3.40.0/test/fkey1.test (revision 39075608) 
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 fkey1
19
20ifcapable {!foreignkey} {
21  finish_test
22  return
23}
24
25# Create a table and some data to work with.
26#
27do_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} {}
39do_test fkey1-1.1 {
40  execsql {
41    CREATE TABLE t2(
42      x INTEGER PRIMARY KEY,
43      y TEXT
44    );
45  }
46} {}
47do_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
57do_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
75do_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]
88do_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]
99do_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]
110do_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]
121do_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.
126do_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.
140do_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}
151do_execsql_test fkey1-4.2 {
152  PRAGMA table_info="""1";
153} {0 {"2} TEXT 0 {} 1 1 {"3} TEXT 0 {} 0}
154
155#-------------------------------------------------------------------------
156#
157do_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#
170do_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#
177ifcapable 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.
191do_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}
201do_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#
209do_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
216do_catchsql_test 6.1 {
217  INSERT INTO c1 VALUES(1);
218} {1 {foreign key mismatch - "c1" referencing "p1"}}
219
220do_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#
231reset_db
232do_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}
238do_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#
248reset_db
249do_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.
261reset_db
262database_may_be_corrupt
263do_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} {}
271do_catchsql_test 8.3 {
272  REINDEX;
273} {1 {database disk image is malformed}}
274
275finish_test
276