Debian Codegraph @

test/altercons.test

raw
1 # 2025 September 18
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 #
12
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set testprefix altercons
16
17 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
18 ifcapable !altertable {
19 finish_test
20 return
21 }
22
23 foreach {tn before after} {
24 1 { CREATE TABLE t1(a, b CONSTRAINT abc CHECK(t1.a != t1.b)) }
25 { CREATE TABLE t1(a, b) }
26
27 2 { CREATE TABLE t1(a, b CONSTRAINT abc CHECK(t1.a != t1.b) NOT NULL) }
28 { CREATE TABLE t1(a, b NOT NULL) }
29
30 3 { CREATE TABLE t1(a, b CONSTRAINT abc CHECK(t1.a != t1.b)NOT NULL) }
31 { CREATE TABLE t1(a, b NOT NULL) }
32
33 3 { CREATE TABLE t1(a, b NOT NULL CONSTRAINT abc CHECK(t1.a != t1.b)); }
34 { CREATE TABLE t1(a, b NOT NULL) }
35
36 4 { CREATE TABLE t1(a, b, CONSTRAINT abc CHECK(t1.a != t1.b)) }
37 { CREATE TABLE t1(a, b) }
38
39 5 { CREATE TABLE t1(a, b, CONSTRAINT abc CHECK(t1.a != t1.b), PRIMARY KEY(a))}
40 { CREATE TABLE t1(a, b, PRIMARY KEY(a)) }
41
42 6 { CREATE TABLE t1(a, b,CONSTRAINT abc CHECK(t1.a != t1.b),PRIMARY KEY(a))}
43 { CREATE TABLE t1(a, b,PRIMARY KEY(a)) }
44
45 7 { CREATE TABLE t1(a, b CONSTRAINT abc CHECK(t1.a != t1.b) CONSTRAINT def UNIQUE) }
46 { CREATE TABLE t1(a, b CONSTRAINT def UNIQUE) }
47
48 8 { CREATE TABLE t1(a, b CONSTRAINT abc CHECK(t1.a != t1.b) CHECK (123)) }
49 { CREATE TABLE t1(a, b CHECK (123)) }
50
51 9 { CREATE TABLE t1(a, b CONSTRAINT abc CHECK(t1.a != t1.b) DEFAULT NULL) }
52 { CREATE TABLE t1(a, b DEFAULT NULL) }
53
54 10 { CREATE TABLE t1(a, b CONSTRAINT abc CHECK(t1.a != t1.b) COLLATE nocase) }
55 { CREATE TABLE t1(a, b COLLATE nocase) }
56
57 11 { CREATE TABLE t1(a, b CONSTRAINT abc CHECK(t1.a != t1.b) REFERENCES t2) }
58 { CREATE TABLE t1(a, b REFERENCES t2) }
59
60 12 { CREATE TABLE t1(a, b, c, CONSTRAINT one CONSTRAINT abc CHECK(a!=b) CONSTRAINT three) }
61 { CREATE TABLE t1(a, b, c, CONSTRAINT one CONSTRAINT three) }
62
63 13 { CREATE TABLE t1(a, b, c, CONSTRAINT abc CONSTRAINT one CHECK(a!=b) CONSTRAINT three) }
64 { CREATE TABLE t1(a, b, c, CONSTRAINT one CHECK(a!=b) CONSTRAINT three) }
65
66 14 { CREATE TABLE t1(a, b, c, CONSTRAINT abc) }
67 { CREATE TABLE t1(a, b, c) }
68
69 15 { CREATE TABLE t1(a, b, c,
70 CONSTRAINT abc, CHECK( a!=b )) }
71 { CREATE TABLE t1(a, b, c, CHECK( a!=b )) }
72
73 16 { CREATE TABLE t1(a, b, c, CONSTRAINT abc /* hello */ CHECK( a!=b )) }
74 { CREATE TABLE t1(a, b, c) }
75
76 17 { CREATE TABLE t1(a, b, c, /* world */ CONSTRAINT abc CHECK( a!=b )) }
77 { CREATE TABLE t1(a, b, c) }
78
79 18 { CREATE TABLE t1(a, b, c -- comment
80 CONSTRAINT abc NOT NULL
81 ) }
82 { CREATE TABLE t1(a, b, c) }
83
84 19 { CREATE TABLE t1(a, b, c, -- comment
85 CONSTRAINT abc CHECK (a>b) CONSTRAINT two
86 ) }
87 { CREATE TABLE t1(a, b, c, CONSTRAINT two
88 ) }
89
90 20 { CREATE TABLE t1(a, b, c, CONSTRAINT one CONSTRAINT abc CHECK (a>b)CONSTRAINT two) }
91 { CREATE TABLE t1(a, b, c, CONSTRAINT one CONSTRAINT two) }
92
93 21 { CREATE TABLE t1(a, b, c CONSTRAINT abc AS (b+1)) }
94 { CREATE TABLE t1(a, b, c AS (b+1)) }
95
96 22 { CREATE TABLE t1(a, b, c CONSTRAINT abc GENERATED ALWAYS AS (b+1) STORED) }
97 { CREATE TABLE t1(a, b, c GENERATED ALWAYS AS (b+1) STORED) }
98 } {
99 reset_db
100
101 do_execsql_test 1.$tn.0 $before
102
103 do_execsql_test 1.$tn.1 {
104 ALTER TABLE t1 DROP CONSTRAINT abc;
105 } {}
106
107 do_execsql_test 1.$tn.2 {
108 SELECT sql FROM sqlite_schema WHERE name='t1'
109 } [list [string trim $after]]
110 }
111
112 #-------------------------------------------------------------------------
113
114 do_execsql_test 2.0 {
115 CREATE TABLE t2(x, y CONSTRAINT ccc UNIQUE);
116 }
117 do_catchsql_test 2.1 {
118 ALTER TABLE t2 DROP CONSTRAINT ccc
119 } {1 {constraint may not be dropped: ccc}}
120 do_catchsql_test 2.2 {
121 ALTER TABLE t2 DROP CONSTRAINT ddd
122 } {1 {no such constraint: ddd}}
123
124 #-------------------------------------------------------------------------
125 reset_db
126 foreach {tn col before after} {
127 1 a { CREATE TABLE t1(a NOT NULL, b) }
128 { CREATE TABLE t1(a, b) }
129
130 2 a { CREATE TABLE t1(a NOT NULL ON CONFLICT FAIL, b) }
131 { CREATE TABLE t1(a, b) }
132
133 3 a { CREATE TABLE t1(a NOT NULL ON CONFLICT FAIL UNIQUE, b) }
134 { CREATE TABLE t1(a UNIQUE, b) }
135
136 4 b { CREATE TABLE t1(a NOT NULL ON CONFLICT FAIL UNIQUE, b) }
137 { CREATE TABLE t1(a NOT NULL ON CONFLICT FAIL UNIQUE, b) }
138
139 5 a { CREATE TABLE t1(a CHECK(a<b) NOT NULL, b) }
140 { CREATE TABLE t1(a CHECK(a<b), b) }
141
142 6 a { CREATE TABLE t1(a CHECK(a<b) CONSTRAINT nn NOT NULL, b) }
143 { CREATE TABLE t1(a CHECK(a<b), b) }
144
145 7 b { CREATE TABLE t1(a, b NOT NULL PRIMARY KEY) }
146 { CREATE TABLE t1(a, b PRIMARY KEY) }
147
148 8 b { CREATE TABLE t1(a, b CHECK ((b+a) IS NOT NULL) NOT NULL PRIMARY KEY) }
149 { CREATE TABLE t1(a, b CHECK ((b+a) IS NOT NULL) PRIMARY KEY) }
150
151 9 b { CREATE TABLE t1(a, b CONSTRAINT nn CHECK (b IS NOT NULL) NOT NULL) }
152 { CREATE TABLE t1(a, b CONSTRAINT nn CHECK (b IS NOT NULL)) }
153
154 10 b { CREATE TABLE t1(a, b NOT NULL AS (a+1)) }
155 { CREATE TABLE t1(a, b AS (a+1)) }
156
157 11 b { CREATE TABLE t1(a, b NOT NULL GENERATED ALWAYS AS (a+1)) }
158 { CREATE TABLE t1(a, b GENERATED ALWAYS AS (a+1)) }
159 } {
160 reset_db
161
162 do_execsql_test 3.$tn.0 $before
163
164 do_execsql_test 3.$tn.1 "
165 ALTER TABLE t1 ALTER COLUMN $col DROP NOT NULL
166 "
167
168 do_execsql_test 3.$tn.2 {
169 SELECT sql FROM sqlite_schema WHERE name='t1'
170 } [list [string trim $after]]
171 }
172
173 #-------------------------------------------------------------------------
174 #
175 reset_db
176 do_execsql_test 4.0 {
177 CREATE TABLE t2(x, y CONSTRAINT ccc UNIQUE);
178 }
179 do_execsql_test 4.1 {
180 ALTER TABLE t2 ALTER x DROP NOT NULL;
181 ALTER TABLE t2 ALTER x DROP NOT NULL;
182 ALTER TABLE t2 ALTER x DROP NOT NULL;
183 } {}
184
185 #-------------------------------------------------------------------------
186 #
187 reset_db
188
189 do_execsql_test 5.1 {
190 CREATE TABLE t3(a INTEGER PRIMARY KEY, b);
191 INSERT INTO t3 VALUES(1000, NULL);
192 }
193
194 do_catchsql_test 5.2.1 {
195 ALTER TABLE t3 ALTER b SET NOT NULL
196 } {1 {constraint failed}}
197
198 do_test 5.2.2 {
199 sqlite3_errcode db
200 } {SQLITE_CONSTRAINT}
201
202 foreach {tn before alter after} {
203 1 { CREATE TABLE t1(a, b) }
204 { ALTER TABLE t1 ALTER a SET NOT NULL }
205 { CREATE TABLE t1(a NOT NULL, b) }
206
207 2 { CREATE TABLE t1(a, b) }
208 { ALTER TABLE t1 ALTER a SET NOT NULL ON CONFLICT FAIL }
209 { CREATE TABLE t1(a NOT NULL ON CONFLICT FAIL, b) }
210
211 3 { CREATE TABLE t1(a, b) }
212 { ALTER TABLE t1 ALTER a SET NOT NULL ON CONFLICT fail; }
213 { CREATE TABLE t1(a NOT NULL ON CONFLICT fail, b) }
214
215 4 { CREATE TABLE t1(a, b) }
216 { ALTER TABLE t1 ALTER b SET NOT NULL ON CONFLICT IGNORE ; }
217 { CREATE TABLE t1(a, b NOT NULL ON CONFLICT IGNORE) }
218
219 5 { CREATE TABLE t1(a, 'a b c' VARCHAR(10), UNIQUE(a)) }
220 { ALTER TABLE t1 ALTER 'a b c' SET NOT NULL }
221 { CREATE TABLE t1(a, 'a b c' VARCHAR(10) NOT NULL, UNIQUE(a)) }
222 } {
223 reset_db
224 do_execsql_test 5.3.$tn.1 $before
225 do_execsql_test 5.3.$tn.2 $alter
226 do_execsql_test 5.3.$tn.3 {
227 SELECT sql FROM sqlite_schema WHERE name='t1';
228 } [list [string trim $after]]
229 }
230
231 do_execsql_test 5.4.1 {
232 CREATE TABLE x1(a, b, c);
233 }
234 do_catchsql_test 5.4.2 {
235 ALTER TABLE x1 ALTER d SET NOT NULL;
236 } {1 {no such column: d}}
237 do_catchsql_test 5.4.3 {
238 ALTER TABLE x2 ALTER c SET NOT NULL;
239 } {1 {no such table: x2}}
240 do_catchsql_test 5.4.4 {
241 ALTER TABLE temp.x1 ALTER c SET NOT NULL;
242 } {1 {no such table: temp.x1}}
243
244 #-------------------------------------------------------------------------
245 #
246 reset_db
247
248 do_execsql_test 6.1 {
249 CREATE TABLE t1(a, b, c);
250 INSERT INTO t1 VALUES(1, 2, 3);
251 INSERT INTO t1 VALUES(4, 5, 6);
252 }
253
254 do_catchsql_test 6.2.1 {
255 ALTER TABLE t1 ADD CONSTRAINT nn CHECK (c!=6);
256 } {1 {constraint failed}}
257 do_execsql_test 6.2.2 {
258 DELETE FROM t1 WHERE c=6;
259 ALTER TABLE t1 ADD CONSTRAINT nn CHECK (c!=6);
260 } {}
261 do_catchsql_test 6.2.3 {
262 INSERT INTO t1 VALUES(4, 5, 6);
263 } {1 {CHECK constraint failed: nn}}
264
265 foreach {tn before alter after} {
266 1 { CREATE TABLE t1(a, b) }
267 { ALTER TABLE t1 ADD CONSTRAINT nn CHECK (a>=0) }
268 { CREATE TABLE t1(a, b, CONSTRAINT nn CHECK (a>=0)) }
269
270 2 { CREATE TABLE t1(a, b ) }
271 { ALTER TABLE t1 ADD CONSTRAINT nn CHECK (a>=0) }
272 { CREATE TABLE t1(a, b , CONSTRAINT nn CHECK (a>=0)) }
273
274 3 { CREATE TABLE t1(a, b ) }
275 { ALTER TABLE t1 ADD CHECK (a>=0) }
276 { CREATE TABLE t1(a, b , CHECK (a>=0)) }
277 } {
278 reset_db
279 do_execsql_test 6.3.$tn.1 $before
280 do_execsql_test 6.3.$tn.2 $alter
281 do_execsql_test 6.3.$tn.3 {
282 SELECT sql FROM sqlite_schema WHERE type='table';
283 } [list [string trim $after]]
284 }
285
286 do_execsql_test 6.4.1 {
287 CREATE TABLE b1(a, b, CONSTRAINT abc CHECK (a!=2));
288 }
289 do_catchsql_test 6.4.2 {
290 ALTER TABLE b1 ADD CONSTRAINT abc CHECK (a!=3);
291 } {1 {constraint abc already exists}}
292 do_execsql_test 6.4.1 {
293 SELECT sql FROM sqlite_schema WHERE tbl_name='b1'
294 } {{CREATE TABLE b1(a, b, CONSTRAINT abc CHECK (a!=2))}}
295
296 do_execsql_test 6.5 {
297 CREATE TABLE abc(x,y);
298 }
299
300 do_catchsql_test 6.6 {
301 ALTER TABLE abc ADD CHECK (z>=0);
302 } {1 {no such column: z}}
303
304 #-------------------------------------------------------------------------
305 # Try attaching a NOT NULL to a generated column.
306 #
307 reset_db
308 do_execsql_test 7.0 {
309 CREATE TABLE x1(a, b AS (a+1));
310 INSERT INTO x1 VALUES(1), (2), (3), (NULL);
311 }
312
313 do_catchsql_test 7.1 {
314 ALTER TABLE x1 ALTER b SET NOT NULL;
315 } {1 {constraint failed}}
316
317 do_catchsql_test 7.2 {
318 DELETE FROM x1 WHERE b IS NULL;
319 ALTER TABLE x1 ALTER b SET NOT NULL;
320 } {0 {}}
321
322 do_execsql_test 7.3 {
323 SELECT b FROM x1
324 } {2 3 4}
325
326 do_catchsql_test 7.4 {
327 ALTER TABLE x1 ALTER rowid SET NOT NULL;
328 } {1 {no such column: rowid}}
329
330 do_execsql_test 7.5 {
331 CREATE VIEW v1 AS SELECT a, b FROM x1;
332 }
333 do_catchsql_test 7.6 {
334 ALTER TABLE v1 RENAME a TO c;
335 } {1 {cannot rename columns of view "v1"}}
336 do_catchsql_test 7.7 {
337 ALTER TABLE v1 ALTER a SET NOT NULL;
338 } {1 {cannot edit constraints of view "v1"}}
339 do_catchsql_test 7.8 {
340 ALTER TABLE sqlite_schema ALTER sql SET NOT NULL;
341 } {1 {table sqlite_master may not be altered}}
342 do_catchsql_test 7.9 {
343 ALTER TABLE v1 ALTER a DROP NOT NULL
344 } {1 {cannot edit constraints of view "v1"}}
345
346 #-------------------------------------------------------------------------
347 reset_db
348 do_execsql_test 8.0 {
349 CREATE TABLE t1(a INTEGER PRIMARY KEY, b NOT NULL, c CHECK (c!=555), d);
350 INSERT INTO t1 VALUES(1, 1, 1, 1);
351 INSERT INTO t1 VALUES(2, 2, 2, 2);
352 INSERT INTO t1 VALUES(3, 3, 3, 3);
353 }
354
355 do_execsql_test 8.1.1 {
356 ALTER TABLE t1 ALTER a SET NOT NULL;
357 ALTER TABLE t1 ALTER b SET NOT NULL;
358 ALTER TABLE t1 ALTER c SET NOT NULL;
359 ALTER TABLE t1 ALTER d SET NOT NULL;
360 }
361
362 do_execsql_test 8.1.2 {
363 SELECT sql FROM sqlite_schema WHERE tbl_name = 't1'
364 } {{CREATE TABLE t1(a INTEGER PRIMARY KEY NOT NULL, b NOT NULL, c CHECK (c!=555) NOT NULL, d NOT NULL)}}
365
366 do_execsql_test 8.1.3 {
367 SELECT * FROM t1 WHERE a=2;
368 } {2 2 2 2}
369
370 do_execsql_test 8.2.1 {
371 ALTER TABLE t1 ALTER a DROP NOT NULL;
372 ALTER TABLE t1 ALTER b DROP NOT NULL;
373 ALTER TABLE t1 ALTER c DROP NOT NULL;
374 ALTER TABLE t1 ALTER d DROP NOT NULL;
375 }
376
377 do_execsql_test 8.2.2 {
378 SELECT sql FROM sqlite_schema WHERE tbl_name = 't1'
379 } {{CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c CHECK (c!=555), d)}}
380
381 do_execsql_test 8.2.3 {
382 SELECT * FROM t1 WHERE a=3;
383 } {3 3 3 3}
384
385 #-------------------------------------------------------------------------
386 reset_db
387 forcedelete test.db2
388 do_execsql_test 9.0 {
389 CREATE TABLE t1(x, y, z);
390 ATTACH 'test.db2' AS aux;
391 CREATE TABLE aux.t1(x, y, z);
392 INSERT INTO aux.t1 VALUES(1, 1, 1);
393 INSERT INTO aux.t1 VALUES(2, 2, 2);
394 INSERT INTO aux.t1 VALUES(3, 3, NULL);
395
396 CREATE TABLE aux.t2(x, y, z);
397 }
398
399 do_catchsql_test 9.1.1 {
400 ALTER TABLE aux.t1 ALTER COLUMN z SET NOT NULL
401 } {1 {constraint failed}}
402 do_execsql_test 9.1.2 {
403 UPDATE aux.t1 SET z=x;
404 ALTER TABLE aux.t1 ALTER COLUMN z SET NOT NULL;
405 SELECT sql FROM aux.sqlite_schema WHERE name='t1';
406 } {{CREATE TABLE t1(x, y, z NOT NULL)}}
407 do_execsql_test 9.1.3 {
408 ALTER TABLE aux.t1 ALTER z DROP NOT NULL;
409 SELECT sql FROM aux.sqlite_schema WHERE name='t1';
410 } {{CREATE TABLE t1(x, y, z)}}
411 do_execsql_test 9.1.4 {
412 ALTER TABLE t2 ALTER x SET NOT NULL;
413 SELECT sql FROM aux.sqlite_schema WHERE name='t2';
414 } {{CREATE TABLE t2(x NOT NULL, y, z)}}
415 do_execsql_test 9.1.5 {
416 ALTER TABLE t2 ALTER x DROP NOT NULL;
417 SELECT sql FROM aux.sqlite_schema WHERE name='t2';
418 } {{CREATE TABLE t2(x, y, z)}}
419
420 do_catchsql_test 9.2.1 {
421 ALTER TABLE aux.t1 ADD CONSTRAINT bill CHECK (y!=2);
422 } {1 {constraint failed}}
423 do_execsql_test 9.2.2 {
424 UPDATE aux.t1 SET y=4 WHERE y=2;
425 ALTER TABLE aux.t1 ADD CONSTRAINT bill CHECK (y!=2);
426 SELECT sql FROM aux.sqlite_schema WHERE name='t1';
427 } {{CREATE TABLE t1(x, y, z, CONSTRAINT bill CHECK (y!=2))}}
428 do_execsql_test 9.2.3 {
429 ALTER TABLE aux.t1 DROP CONSTRAINT bill;
430 SELECT sql FROM aux.sqlite_schema WHERE name='t1';
431 } {{CREATE TABLE t1(x, y, z)}}
432 do_execsql_test 9.2.4 {
433 ALTER TABLE t2 ADD CONSTRAINT william CHECK (z!='');
434 SELECT sql FROM aux.sqlite_schema WHERE name='t2';
435 } {{CREATE TABLE t2(x, y, z, CONSTRAINT william CHECK (z!=''))}}
436 do_execsql_test 9.2.5 {
437 ALTER TABLE t2 DROP CONSTRAINT william;
438 SELECT sql FROM aux.sqlite_schema WHERE name='t2';
439 } {{CREATE TABLE t2(x, y, z)}}
440
441 # Cannot add constraints the use internal functions.
442 # Forum post 2026-05-10T01:11:28Z
443 #
444 reset_db
445 do_execsql_test 10.1 {
446 CREATE TABLE t1(x,y,z);
447 INSERT INTO t1 VALUES(1,'two',x'3333');
448 }
449 do_catchsql_test 10.2 {
450 ALTER TABLE t1 ADD CONSTRAINT c1 CHECK( sqlite_drop_column(22,'CREATE TABLE a(b,c)', 0));
451 } {1 {no such function: sqlite_drop_column}}
452 do_catchsql_test 10.3 {
453 ALTER TABLE t1 ADD CONSTRAINT c1 CHECK( x>#2 );
454 } {1 {near "#2": syntax error}}
455
456 finish_test