xref: /sqlite-3.40.0/test/fkey3.test (revision 7aa3ebee) 
1# 2009 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
18
19ifcapable {!foreignkey||!trigger} {
20  finish_test
21  return
22}
23
24set testprefix fkey3
25
26# Create a table and some data to work with.
27#
28do_test fkey3-1.1 {
29  execsql {
30    PRAGMA foreign_keys=ON;
31    CREATE TABLE t1(x INTEGER PRIMARY KEY);
32    INSERT INTO t1 VALUES(100);
33    INSERT INTO t1 VALUES(101);
34    CREATE TABLE t2(y INTEGER REFERENCES t1 (x));
35    INSERT INTO t2 VALUES(100);
36    INSERT INTO t2 VALUES(101);
37    SELECT 1, x FROM t1;
38    SELECT 2, y FROM t2;
39  }
40} {1 100 1 101 2 100 2 101}
41
42do_test fkey3-1.2 {
43  catchsql {
44    DELETE FROM t1 WHERE x=100;
45  }
46} {1 {FOREIGN KEY constraint failed}}
47
48do_test fkey3-1.3 {
49  catchsql {
50    DROP TABLE t1;
51  }
52} {1 {FOREIGN KEY constraint failed}}
53
54do_test fkey3-1.4 {
55  execsql {
56    DROP TABLE t2;
57  }
58} {}
59
60do_test fkey3-1.5 {
61  execsql {
62    DROP TABLE t1;
63  }
64} {}
65
66do_test fkey3-2.1 {
67  execsql {
68    PRAGMA foreign_keys=ON;
69    CREATE TABLE t1(x INTEGER PRIMARY KEY);
70    INSERT INTO t1 VALUES(100);
71    INSERT INTO t1 VALUES(101);
72    CREATE TABLE t2(y INTEGER PRIMARY KEY REFERENCES t1 (x) ON UPDATE SET NULL);
73  }
74  execsql {
75    INSERT INTO t2 VALUES(100);
76    INSERT INTO t2 VALUES(101);
77    SELECT 1, x FROM t1;
78    SELECT 2, y FROM t2;
79  }
80} {1 100 1 101 2 100 2 101}
81
82
83#-------------------------------------------------------------------------
84# The following tests - fkey-3.* - test some edge cases to do with
85# inserting rows into tables that have foreign keys where the parent
86# table is the same as the child table. Especially cases where the
87# new row being inserted matches itself.
88#
89do_execsql_test 3.1.1 {
90  CREATE TABLE t3(a, b, c, d,
91    UNIQUE(a, b),
92    FOREIGN KEY(c, d) REFERENCES t3(a, b)
93  );
94  INSERT INTO t3 VALUES(1, 2, 1, 2);
95} {}
96do_catchsql_test 3.1.2 {
97  INSERT INTO t3 VALUES(NULL, 2, 5, 2);
98} {1 {FOREIGN KEY constraint failed}}
99do_catchsql_test 3.1.3 {
100  INSERT INTO t3 VALUES(NULL, 3, 5, 2);
101} {1 {FOREIGN KEY constraint failed}}
102
103do_execsql_test 3.2.1 {
104  CREATE TABLE t4(a UNIQUE, b REFERENCES t4(a));
105}
106do_catchsql_test 3.2.2 {
107  INSERT INTO t4 VALUES(NULL, 1);
108} {1 {FOREIGN KEY constraint failed}}
109
110do_execsql_test 3.3.1 {
111  CREATE TABLE t5(a INTEGER PRIMARY KEY, b REFERENCES t5(a));
112  INSERT INTO t5 VALUES(NULL, 1);
113} {}
114do_catchsql_test 3.3.2 {
115  INSERT INTO t5 VALUES(NULL, 3);
116} {1 {FOREIGN KEY constraint failed}}
117
118do_execsql_test 3.4.1 {
119  CREATE TABLE t6(a INTEGER PRIMARY KEY, b, c, d,
120    FOREIGN KEY(c, d) REFERENCES t6(a, b)
121  );
122  CREATE UNIQUE INDEX t6i ON t6(b, a);
123}
124do_execsql_test 3.4.2  { INSERT INTO t6 VALUES(NULL, 'a', 1, 'a'); } {}
125do_execsql_test 3.4.3  { INSERT INTO t6 VALUES(2, 'a', 2, 'a');    } {}
126do_execsql_test 3.4.4  { INSERT INTO t6 VALUES(NULL, 'a', 1, 'a'); } {}
127do_execsql_test 3.4.5  { INSERT INTO t6 VALUES(5, 'a', 2, 'a'); } {}
128do_catchsql_test 3.4.6 {
129  INSERT INTO t6 VALUES(NULL, 'a', 65, 'a');
130} {1 {FOREIGN KEY constraint failed}}
131
132do_execsql_test 3.4.7 {
133  INSERT INTO t6 VALUES(100, 'one', 100, 'one');
134  DELETE FROM t6 WHERE a = 100;
135}
136do_execsql_test 3.4.8 {
137  INSERT INTO t6 VALUES(100, 'one', 100, 'one');
138  UPDATE t6 SET c = 1, d = 'a' WHERE a = 100;
139  DELETE FROM t6 WHERE a = 100;
140}
141
142do_execsql_test 3.5.1 {
143  CREATE TABLE t7(a, b, c, d INTEGER PRIMARY KEY,
144    FOREIGN KEY(c, d) REFERENCES t7(a, b)
145  );
146  CREATE UNIQUE INDEX t7i ON t7(a, b);
147}
148do_execsql_test 3.5.2  { INSERT INTO t7 VALUES('x', 1, 'x', NULL) } {}
149do_execsql_test 3.5.3  { INSERT INTO t7 VALUES('x', 2, 'x', 2) } {}
150do_catchsql_test 3.5.4  {
151  INSERT INTO t7 VALUES('x', 450, 'x', NULL);
152} {1 {FOREIGN KEY constraint failed}}
153do_catchsql_test 3.5.5  {
154  INSERT INTO t7 VALUES('x', 450, 'x', 451);
155} {1 {FOREIGN KEY constraint failed}}
156
157
158do_execsql_test 3.6.1 {
159  CREATE TABLE t8(a, b, c, d, e, FOREIGN KEY(c, d) REFERENCES t8(a, b));
160  CREATE UNIQUE INDEX t8i1 ON t8(a, b);
161  CREATE UNIQUE INDEX t8i2 ON t8(c);
162  INSERT INTO t8 VALUES(1, 1, 1, 1, 1);
163}
164do_catchsql_test 3.6.2 {
165  UPDATE t8 SET d = 2;
166} {1 {FOREIGN KEY constraint failed}}
167do_execsql_test 3.6.3 { UPDATE t8 SET d = 1; }
168do_execsql_test 3.6.4 { UPDATE t8 SET e = 2; }
169
170do_catchsql_test 3.6.5 {
171  CREATE TABLE TestTable (
172    id INTEGER PRIMARY KEY,
173    name text,
174    source_id integer not null,
175    parent_id integer,
176
177    foreign key(source_id, parent_id) references TestTable(source_id, id)
178  );
179  CREATE UNIQUE INDEX testindex on TestTable(source_id, id);
180  PRAGMA foreign_keys=1;
181  INSERT INTO TestTable VALUES (1, 'parent', 1, null);
182  INSERT INTO TestTable VALUES (2, 'child', 1, 1);
183  UPDATE TestTable SET parent_id=1000 where id=2;
184} {1 {FOREIGN KEY constraint failed}}
185
186finish_test
187