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:
16: set testdir [file dirname $argv0]
17: source $testdir/tester.tcl
18:
19: ifcapable {!foreignkey||!trigger} {
20: finish_test
21: return
22: }
23:
24: set testprefix fkey3
25:
26: # Create a table and some data to work with.
27: #
28: do_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:
42: do_test fkey3-1.2 {
43: catchsql {
44: DELETE FROM t1 WHERE x=100;
45: }
46: } {1 {foreign key constraint failed}}
47:
48: do_test fkey3-1.3 {
49: catchsql {
50: DROP TABLE t1;
51: }
52: } {1 {foreign key constraint failed}}
53:
54: do_test fkey3-1.4 {
55: execsql {
56: DROP TABLE t2;
57: }
58: } {}
59:
60: do_test fkey3-1.5 {
61: execsql {
62: DROP TABLE t1;
63: }
64: } {}
65:
66: do_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: #
89: do_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: } {}
96: do_catchsql_test 3.1.2 {
97: INSERT INTO t3 VALUES(NULL, 2, 5, 2);
98: } {1 {foreign key constraint failed}}
99: do_catchsql_test 3.1.3 {
100: INSERT INTO t3 VALUES(NULL, 3, 5, 2);
101: } {1 {foreign key constraint failed}}
102:
103: do_execsql_test 3.2.1 {
104: CREATE TABLE t4(a UNIQUE, b REFERENCES t4(a));
105: }
106: do_catchsql_test 3.2.2 {
107: INSERT INTO t4 VALUES(NULL, 1);
108: } {1 {foreign key constraint failed}}
109:
110: do_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: } {}
114: do_catchsql_test 3.3.2 {
115: INSERT INTO t5 VALUES(NULL, 3);
116: } {1 {foreign key constraint failed}}
117:
118: do_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: }
124: do_execsql_test 3.4.2 { INSERT INTO t6 VALUES(NULL, 'a', 1, 'a'); } {}
125: do_execsql_test 3.4.3 { INSERT INTO t6 VALUES(2, 'a', 2, 'a'); } {}
126: do_execsql_test 3.4.4 { INSERT INTO t6 VALUES(NULL, 'a', 1, 'a'); } {}
127: do_execsql_test 3.4.5 { INSERT INTO t6 VALUES(5, 'a', 2, 'a'); } {}
128: do_catchsql_test 3.4.6 {
129: INSERT INTO t6 VALUES(NULL, 'a', 65, 'a');
130: } {1 {foreign key constraint failed}}
131:
132: do_execsql_test 3.4.7 {
133: INSERT INTO t6 VALUES(100, 'one', 100, 'one');
134: DELETE FROM t6 WHERE a = 100;
135: }
136: do_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:
142: do_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: }
148: do_execsql_test 3.5.2 { INSERT INTO t7 VALUES('x', 1, 'x', NULL) } {}
149: do_execsql_test 3.5.3 { INSERT INTO t7 VALUES('x', 2, 'x', 2) } {}
150: do_catchsql_test 3.5.4 {
151: INSERT INTO t7 VALUES('x', 450, 'x', NULL);
152: } {1 {foreign key constraint failed}}
153: do_catchsql_test 3.5.5 {
154: INSERT INTO t7 VALUES('x', 450, 'x', 451);
155: } {1 {foreign key constraint failed}}
156:
157:
158: do_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: }
164: do_catchsql_test 3.6.2 {
165: UPDATE t8 SET d = 2;
166: } {1 {foreign key constraint failed}}
167: do_execsql_test 3.6.3 { UPDATE t8 SET d = 1; }
168: do_execsql_test 3.6.4 { UPDATE t8 SET e = 2; }
169:
170: do_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:
186: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to fkey3.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test