AryaWu/sqlite
0
1# 2025 September 182#3# The author disclaims copyright to this source code. In place of4# 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 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15set testprefix altercons16 17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.18ifcapable !altertable {19 finish_test20 return21}22 23foreach {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 -- comment80 CONSTRAINT abc NOT NULL81 ) }82 { CREATE TABLE t1(a, b, c) }83 84 19 { CREATE TABLE t1(a, b, c, -- comment85 CONSTRAINT abc CHECK (a>b) CONSTRAINT two86 ) }87 { CREATE TABLE t1(a, b, c, CONSTRAINT two88 ) }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_db100 101 do_execsql_test 1.$tn.0 $before102 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 114do_execsql_test 2.0 {115 CREATE TABLE t2(x, y CONSTRAINT ccc UNIQUE);116}117do_catchsql_test 2.1 {118 ALTER TABLE t2 DROP CONSTRAINT ccc119} {1 {constraint may not be dropped: ccc}}120do_catchsql_test 2.2 {121 ALTER TABLE t2 DROP CONSTRAINT ddd122} {1 {no such constraint: ddd}}123 124#-------------------------------------------------------------------------125reset_db126foreach {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_db161 162 do_execsql_test 3.$tn.0 $before163 164 do_execsql_test 3.$tn.1 "165 ALTER TABLE t1 ALTER COLUMN $col DROP NOT NULL166 "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#175reset_db176do_execsql_test 4.0 {177 CREATE TABLE t2(x, y CONSTRAINT ccc UNIQUE);178}179do_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#187reset_db188 189do_execsql_test 5.1 {190 CREATE TABLE t3(a INTEGER PRIMARY KEY, b);191 INSERT INTO t3 VALUES(1000, NULL);192}193 194do_catchsql_test 5.2.1 {195 ALTER TABLE t3 ALTER b SET NOT NULL196} {1 {constraint failed}}197 198do_test 5.2.2 {199 sqlite3_errcode db200} {SQLITE_CONSTRAINT}201 202foreach {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_db224 do_execsql_test 5.3.$tn.1 $before225 do_execsql_test 5.3.$tn.2 $alter226 do_execsql_test 5.3.$tn.3 {227 SELECT sql FROM sqlite_schema WHERE name='t1';228 } [list [string trim $after]]229}230 231do_execsql_test 5.4.1 {232 CREATE TABLE x1(a, b, c);233}234do_catchsql_test 5.4.2 {235 ALTER TABLE x1 ALTER d SET NOT NULL;236} {1 {no such column: d}}237do_catchsql_test 5.4.3 {238 ALTER TABLE x2 ALTER c SET NOT NULL;239} {1 {no such table: x2}}240do_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#246reset_db247 248do_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 254do_catchsql_test 6.2.1 {255 ALTER TABLE t1 ADD CONSTRAINT nn CHECK (c!=6);256} {1 {constraint failed}}257do_execsql_test 6.2.2 {258 DELETE FROM t1 WHERE c=6;259 ALTER TABLE t1 ADD CONSTRAINT nn CHECK (c!=6);260} {}261do_catchsql_test 6.2.3 {262 INSERT INTO t1 VALUES(4, 5, 6);263} {1 {CHECK constraint failed: nn}}264 265foreach {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_db279 do_execsql_test 6.3.$tn.1 $before280 do_execsql_test 6.3.$tn.2 $alter281 do_execsql_test 6.3.$tn.3 {282 SELECT sql FROM sqlite_schema WHERE type='table';283 } [list [string trim $after]]284}285 286do_execsql_test 6.4.1 {287 CREATE TABLE b1(a, b, CONSTRAINT abc CHECK (a!=2));288}289do_catchsql_test 6.4.2 {290 ALTER TABLE b1 ADD CONSTRAINT abc CHECK (a!=3);291} {1 {constraint abc already exists}}292do_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 296do_execsql_test 6.5 {297 CREATE TABLE abc(x,y);298}299 300do_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#307reset_db308do_execsql_test 7.0 {309 CREATE TABLE x1(a, b AS (a+1));310 INSERT INTO x1 VALUES(1), (2), (3), (NULL);311}312 313do_catchsql_test 7.1 {314 ALTER TABLE x1 ALTER b SET NOT NULL;315} {1 {constraint failed}}316 317do_catchsql_test 7.2 {318 DELETE FROM x1 WHERE b IS NULL;319 ALTER TABLE x1 ALTER b SET NOT NULL;320} {0 {}}321 322do_execsql_test 7.3 {323 SELECT b FROM x1324} {2 3 4}325 326do_catchsql_test 7.4 {327 ALTER TABLE x1 ALTER rowid SET NOT NULL;328} {1 {no such column: rowid}}329 330do_execsql_test 7.5 {331 CREATE VIEW v1 AS SELECT a, b FROM x1;332}333do_catchsql_test 7.6 {334 ALTER TABLE v1 RENAME a TO c;335} {1 {cannot rename columns of view "v1"}}336do_catchsql_test 7.7 {337 ALTER TABLE v1 ALTER a SET NOT NULL;338} {1 {cannot edit constraints of view "v1"}}339do_catchsql_test 7.8 {340 ALTER TABLE sqlite_schema ALTER sql SET NOT NULL;341} {1 {table sqlite_master may not be altered}}342do_catchsql_test 7.9 {343 ALTER TABLE v1 ALTER a DROP NOT NULL344} {1 {cannot edit constraints of view "v1"}}345 346#-------------------------------------------------------------------------347reset_db348do_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 355do_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 362do_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 366do_execsql_test 8.1.3 {367 SELECT * FROM t1 WHERE a=2;368} {2 2 2 2}369 370do_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 377do_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 381do_execsql_test 8.2.3 {382 SELECT * FROM t1 WHERE a=3;383} {3 3 3 3}384 385#-------------------------------------------------------------------------386reset_db387forcedelete test.db2388do_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 399do_catchsql_test 9.1.1 {400 ALTER TABLE aux.t1 ALTER COLUMN z SET NOT NULL401} {1 {constraint failed}}402do_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)}}407do_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)}}411do_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)}}415do_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 420do_catchsql_test 9.2.1 {421 ALTER TABLE aux.t1 ADD CONSTRAINT bill CHECK (y!=2);422} {1 {constraint failed}}423do_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))}}428do_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)}}432do_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!=''))}}436do_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 441finish_test442 443