1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
|
#!/usr/bin/env tarantool
test = require("sqltester")
test:plan(25)
-- This file implements regression tests for foreign keys.
test:do_execsql_test(
"fkey1-1.1",
[[
CREATE TABLE t2(x INT PRIMARY KEY, y TEXT, UNIQUE (x, y));
]], {
-- <fkey1-1.1>
-- </fkey1-1.1>
})
test:do_execsql_test(
"fkey1-1.2",
[[
CREATE TABLE t1(
a INT PRIMARY KEY,
b INTEGER
REFERENCES t1 ON DELETE CASCADE
REFERENCES t2 (x),
c TEXT UNIQUE,
FOREIGN KEY (b, c) REFERENCES t2(x, y) ON UPDATE CASCADE);
]], {
-- <fkey1-1.1>
-- </fkey1-1.1>
})
test:do_execsql_test(
"fkey1-1.3",
[[
CREATE TABLE t3(
a INT PRIMARY KEY REFERENCES t2,
b TEXT REFERENCES t1(c),
FOREIGN KEY (a, b) REFERENCES t2(x, y));
]], {
-- <fkey1-1.3>
-- </fkey1-1.3>
})
test:do_execsql_test(
"fkey1-2.1",
[[
CREATE TABLE t4(a INTEGER PRIMARY KEY);
CREATE TABLE t5(x INTEGER PRIMARY KEY REFERENCES t4);
CREATE TABLE t6(x INTEGER PRIMARY KEY REFERENCES t4);
CREATE TABLE t7(x INTEGER PRIMARY KEY REFERENCES t4);
CREATE TABLE t8(x INTEGER PRIMARY KEY REFERENCES t4);
CREATE TABLE t9(x INTEGER PRIMARY KEY REFERENCES t4);
CREATE TABLE t10(x INTEGER PRIMARY KEY REFERENCES t4);
DROP TABLE t7;
DROP TABLE t9;
DROP TABLE t5;
DROP TABLE t8;
DROP TABLE t6;
DROP TABLE t10;
]], {
-- <fkey1-2.1>
-- </fkey1-1.1>
})
test:do_execsql_test(
"fkey1-3.1",
[[
CREATE TABLE t5(a INTEGER PRIMARY KEY, b INT , c INT UNIQUE, UNIQUE(a, b) );
CREATE TABLE t6(d INT REFERENCES t5, e INT PRIMARY KEY REFERENCES t5(c));
PRAGMA foreign_key_list(t6);
]], {
-- <fkey1-3.1>
0, 0, 'T5', 'D', 'A', 'no_action', 'no_action', 'NONE',
1, 0, 'T5', 'E', 'C', 'no_action', 'no_action', 'NONE'
-- </fkey1-3.1>
})
test:do_execsql_test(
"fkey1-3.2",
[[
CREATE TABLE t7(d INT PRIMARY KEY, e INT , f INT , FOREIGN KEY (d, e) REFERENCES t5(a, b));
PRAGMA foreign_key_list(t7);
]], {
-- <fkey1-3.2>
0, 0, 'T5', 'D', 'A', 'no_action', 'no_action', 'NONE',
0, 1, 'T5', 'E', 'B', 'no_action', 'no_action', 'NONE'
-- </fkey1-3.2>
})
test:do_execsql_test(
"fkey1-3.3",
[[
CREATE TABLE t8(
d INT PRIMARY KEY, e INT , f INT ,
FOREIGN KEY (d, e) REFERENCES t5(a, b) ON DELETE CASCADE ON UPDATE SET NULL);
PRAGMA foreign_key_list(t8);
]], {
-- <fkey1-3.3>
0, 0, 'T5', 'D', 'A', 'cascade', 'set_null', 'NONE',
0, 1, 'T5', 'E', 'B', 'cascade', 'set_null', 'NONE'
-- </fkey1-3.3>
})
test:do_execsql_test(
"fkey1-3.4",
[[
CREATE TABLE t9(
d INT PRIMARY KEY, e INT , f INT ,
FOREIGN KEY (d, e) REFERENCES t5(a, b) ON DELETE CASCADE ON UPDATE SET DEFAULT);
PRAGMA foreign_key_list(t9);
]], {
-- <fkey1-3.4>
0, 0, 'T5', 'D', 'A', 'cascade', 'set_default', 'NONE',
0, 1, 'T5', 'E', 'B', 'cascade', 'set_default', 'NONE'
-- </fkey1-3.4>
})
test:do_execsql_test(
"fkey1-4.1",
[[
CREATE TABLE "xx1"("xx2" TEXT PRIMARY KEY, "xx3" TEXT);
INSERT INTO "xx1"("xx2","xx3") VALUES('abc','def');
CREATE TABLE "xx4"("xx5" TEXT PRIMARY KEY REFERENCES "xx1" ON DELETE CASCADE);
INSERT INTO "xx4"("xx5") VALUES('abc');
INSERT INTO "xx1"("xx2","xx3") VALUES('uvw','xyz');
SELECT 1, "xx5" FROM "xx4";
]], {
-- <fkey1-4.1>
1, 'abc'
-- </fkey1-4.1>
})
test:do_execsql_test(
"fkey1-4.2",
[[
DELETE FROM "xx1";
SELECT 2, "xx5" FROM "xx4";
]], {
-- <fkey1-4.2>
-- </fkey1-4.2>
})
test:do_execsql_test(
"fkey1-5.1",
[[
CREATE TABLE t11(
x INTEGER PRIMARY KEY,
parent INT REFERENCES t11 ON DELETE CASCADE);
INSERT INTO t11 VALUES(1, NULL), (2, 1), (3, 2);
]], {
-- <fkey1-5.1>
-- </fkey1-5.1>
})
test:do_catchsql_test(
"fkey1-5.2",
[[
INSERT OR REPLACE INTO t11 VALUES (2, 3);
]], {
-- <fkey1-5.2>
1, "Failed to execute SQL statement: FOREIGN KEY constraint failed"
-- </fkey1-5.2>
})
test:do_execsql_test(
"fkey1-5.3",
[[
SELECT * FROM t11;
]], {
-- <fkey1-5.3>
1, "", 2, 1, 3, 2
-- </fkey1-5.3>
})
test:do_execsql_test(
"fkey1-5.4",
[[
CREATE TABLE Foo (
Id INT PRIMARY KEY,
ParentId INTEGER REFERENCES Foo(Id) ON DELETE CASCADE,
C1 TEXT);
INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (1, null, 'A');
INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 1, 'A-2-1');
INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (3, 2, 'A-3-2');
INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (4, 3, 'A-4-3');
]], {
-- <fkey1-5.4>
-- </fkey1-5.4>
})
test:do_catchsql_test(
"fkey1-5.5",
[[
INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 3, 'A-2-3');
]], {
-- <fkey1-5.5>
1, "Failed to execute SQL statement: FOREIGN KEY constraint failed"
-- </fkey1-5.5>
})
test:do_execsql_test(
"fkey1-5.6",
[[
SELECT * FROM Foo;
]], {
-- <fkey1-5.6>
1, "", 'A', 2, 1, 'A-2-1', 3, 2, 'A-3-2', 4, 3, 'A-4-3'
-- </fkey1-5.6>
})
test:do_catchsql_test(
"fkey1-6.1",
[[
CREATE TABLE p1(id INT PRIMARY KEY, x INT, y INT);
CREATE INDEX p1x ON p1(x);
INSERT INTO p1 VALUES(1, 1, 1);
CREATE TABLE c1(a INT PRIMARY KEY REFERENCES p1(x));
]], {
-- <fkey1-6.1>
1, "Failed to create foreign key constraint 'fk_unnamed_C1_1': referenced fields don't compose unique index"
-- </fkey1-6.1>
})
test:do_execsql_test(
"fkey1-6.3",
[[
CREATE UNIQUE INDEX p1x2 ON p1(x);
DROP TABLE IF EXISTS c1;
CREATE TABLE c1(a INT PRIMARY KEY REFERENCES p1(x));
INSERT INTO c1 VALUES(1);
]], {
-- <fkey1-6.3>
-- </fkey1-6.3>
})
-- gh-3645: update col=Null do not activates ON UPDATE trigger.
test:do_select_tests(
"fkey1-7",
{
{"0",
[[
CREATE TABLE T12 (A INTEGER PRIMARY KEY,
B VARCHAR(5) UNIQUE);
CREATE TABLE T13 (A INTEGER PRIMARY KEY,
B VARCHAR(5) UNIQUE,
FOREIGN KEY (B) REFERENCES T12 (B) ON UPDATE SET NULL);
INSERT INTO T12 VALUES (1,'a');
INSERT INTO T13 VALUES (1,'a');
]], {}},
{"1", "UPDATE T12 SET B = NULL", {}},
{"2", "SELECT * FROM T12", {1, ""}},
{"3", "SELECT * FROM T13", {1, ""}},
{"4", "UPDATE T12 SET B = 'a'", {}},
{"5", "SELECT * FROM T12", {1, "a"}},
{"6", "SELECT * FROM T13", {1, ""}},
})
test:finish_test()
|
