AryaWu/sqlite

1# 2021 February 192#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 alterdropcol216 17# If SQLITE_OMIT_ALTERTABLE is defined, omit this file.18ifcapable !altertable {19  finish_test20  return21}22 23# EVIDENCE-OF: R-58318-35349 The DROP COLUMN syntax is used to remove an24# existing column from a table.25do_execsql_test 1.0 {26  CREATE TABLE t1(c, b, a, PRIMARY KEY(b, a)) WITHOUT ROWID;27  INSERT INTO t1 VALUES(1, 2, 3), (4, 5, 6);28}29do_execsql_test 1.1 {30  ALTER TABLE t1 DROP c;31}32 33# EVIDENCE-OF: The DROP COLUMN command removes the named column from the table,34# and also rewrites the entire table to purge the data associated with that35# column.  36do_execsql_test 1.2.1 {37  SELECT * FROM t1;38} {2 3   5 6}39 40do_execsql_test 1.2.2 {41  SELECT sql FROM sqlite_schema;42} {43  {CREATE TABLE t1(b, a, PRIMARY KEY(b, a)) WITHOUT ROWID}44}45 46proc do_atdc_error_test {tn schema atdc error} {47  reset_db48  execsql $schema49  uplevel [list do_catchsql_test $tn $atdc [list 1 [string trim $error]]]50}51 52#-------------------------------------------------------------------------53# Test cases 2.* attempt to verify the following:54#55# EVIDENCE-OF: R-24098-10282 The DROP COLUMN command only works if the column56# is not referenced by any other parts of the schema and is not a PRIMARY KEY57# and does not have a UNIQUE constraint.58#59 60# EVIDENCE-OF: R-52436-31752 The column is a PRIMARY KEY or part of one.61#62do_atdc_error_test 2.1.1 {63  CREATE TABLE x1(a PRIMARY KEY, b, c);64} { 65  ALTER TABLE x1 DROP COLUMN a 66} {67  cannot drop PRIMARY KEY column: "a"68}69do_atdc_error_test 2.1.2 {70  CREATE TABLE x1(a,b,c,d,e, PRIMARY KEY(b,c,d));71} { 72  ALTER TABLE x1 DROP COLUMN c73} {74  cannot drop PRIMARY KEY column: "c"75}76 77# EVIDENCE-OF: R-43412-16016 The column has a UNIQUE constraint.78#79do_atdc_error_test 2.2.1 {80  CREATE TABLE x1(a PRIMARY KEY, b, c UNIQUE);81} { 82  ALTER TABLE x1 DROP COLUMN c 83} {84  cannot drop UNIQUE column: "c"85}86do_atdc_error_test 2.2.2 {87  CREATE TABLE x1(a PRIMARY KEY, b, c, UNIQUE(b, c));88} { 89  ALTER TABLE x1 DROP COLUMN c 90} {91  error in table x1 after drop column: no such column: c92}93 94# EVIDENCE-OF: R-46731-08965 The column is indexed.95#96do_atdc_error_test 2.3.1 {97  CREATE TABLE 'one two'('x y', 'z 1', 'a b');98  CREATE INDEX idx ON 'one two'('z 1');99} { 100  ALTER TABLE 'one two' DROP COLUMN 'z 1' 101} {102  error in index idx after drop column: no such column: z 1103}104do_atdc_error_test 2.3.2 {105  CREATE TABLE x1(a, b, c);106  CREATE INDEX idx ON x1(a);107} { 108  ALTER TABLE x1 DROP COLUMN a;109} {110  error in index idx after drop column: no such column: a111}112 113# EVIDENCE-OF: R-46731-08965 The column is indexed.114#115do_atdc_error_test 2.4.1 {116  CREATE TABLE x1234(a, b, c PRIMARY KEY) WITHOUT ROWID;117  CREATE INDEX i1 ON x1234(b) WHERE ((a+5) % 10)==0;118} { 119  ALTER TABLE x1234 DROP a120} {121  error in index i1 after drop column: no such column: a122}123 124# EVIDENCE-OF: R-47838-03249 The column is named in a table or column125# CHECK constraint not associated with the column being dropped.126#127do_atdc_error_test 2.5.1 {128  CREATE TABLE x1234(a, b, c PRIMARY KEY, CHECK(((a+5)%10)!=0)) WITHOUT ROWID;129} { 130  ALTER TABLE x1234 DROP a131} {132  error in table x1234 after drop column: no such column: a133}134 135# EVIDENCE-OF: R-55640-01652 The column is used in a foreign key constraint.136#137do_atdc_error_test 2.6.1 {138  CREATE TABLE p1(x, y UNIQUE);139  CREATE TABLE c1(u, v, FOREIGN KEY (v) REFERENCES p1(y))140} { 141  ALTER TABLE c1 DROP v142} {143  error in table c1 after drop column: unknown column "v" in foreign key definition144}145 146# EVIDENCE-OF: R-20795-39479 The column is used in the expression of a 147# generated column.148do_atdc_error_test 2.7.1 {149  CREATE TABLE c1(u, v, w AS (u+v));150} { 151  ALTER TABLE c1 DROP v152} {153  error in table c1 after drop column: no such column: v154}155do_atdc_error_test 2.7.2 {156  CREATE TABLE c1(u, v, w AS (u+v) STORED);157} { 158  ALTER TABLE c1 DROP u159} {160  error in table c1 after drop column: no such column: u161}162 163# EVIDENCE-OF: R-01515-49025 The column appears in a trigger or view.164#165do_atdc_error_test 2.8.1 {166  CREATE TABLE log(l);167  CREATE TABLE c1(u, v, w);168  CREATE TRIGGER tr1 AFTER INSERT ON c1 BEGIN169    INSERT INTO log VALUES(new.w);170  END;171} { 172  ALTER TABLE c1 DROP w173} {174  error in trigger tr1 after drop column: no such column: new.w175}176do_atdc_error_test 2.8.2 {177  CREATE TABLE c1(u, v, w);178  CREATE VIEW v1 AS SELECT u, v, w FROM c1;179} { 180  ALTER TABLE c1 DROP w181} {182  error in view v1 after drop column: no such column: w183}184do_atdc_error_test 2.8.3 {185  CREATE TABLE c1(u, v, w);186  CREATE VIEW v1 AS SELECT * FROM c1 WHERE w IS NOT NULL;187} { 188  ALTER TABLE c1 DROP w189} {190  error in view v1 after drop column: no such column: w191}192 193#-------------------------------------------------------------------------194# Verify that a column that is part of a CHECK constraint may be dropped195# if the CHECK constraint was specified as part of the column definition.196#197 198# STALE-EVIDENCE: R-60924-11170 However, the column being deleted can be used in a199# column CHECK constraint because the column CHECK constraint is dropped200# together with the column itself.201do_execsql_test 3.0 {202  CREATE TABLE yyy(q, w, e CHECK (e > 0), r);203  INSERT INTO yyy VALUES(1,1,1,1), (2,2,2,2);204 205  CREATE TABLE zzz(q, w, e, r, CHECK (e > 0));206  INSERT INTO zzz VALUES(1,1,1,1), (2,2,2,2);207}208do_catchsql_test 3.1.1 {209  INSERT INTO yyy VALUES(0,0,0,0);210} {1 {CHECK constraint failed: e > 0}}211do_catchsql_test 3.1.2 {212  INSERT INTO yyy VALUES(0,0,0,0);213} {1 {CHECK constraint failed: e > 0}}214 215do_execsql_test 3.2.1 {216  ALTER TABLE yyy DROP e;217}218do_catchsql_test 3.2.2 {219  ALTER TABLE zzz DROP e;220} {1 {error in table zzz after drop column: no such column: e}}221 222finish_test223