000001 # 2021 February 19
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #*************************************************************************
000011 #
000012
000013 set testdir [file dirname $argv0]
000014 source $testdir/tester.tcl
000015 set testprefix alterdropcol2
000016
000017 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file.
000018 ifcapable !altertable {
000019 finish_test
000020 return
000021 }
000022
000023 # EVIDENCE-OF: R-58318-35349 The DROP COLUMN syntax is used to remove an
000024 # existing column from a table.
000025 do_execsql_test 1.0 {
000026 CREATE TABLE t1(c, b, a, PRIMARY KEY(b, a)) WITHOUT ROWID;
000027 INSERT INTO t1 VALUES(1, 2, 3), (4, 5, 6);
000028 }
000029 do_execsql_test 1.1 {
000030 ALTER TABLE t1 DROP c;
000031 }
000032
000033 # EVIDENCE-OF: The DROP COLUMN command removes the named column from the table,
000034 # and also rewrites the entire table to purge the data associated with that
000035 # column.
000036 do_execsql_test 1.2.1 {
000037 SELECT * FROM t1;
000038 } {2 3 5 6}
000039
000040 do_execsql_test 1.2.2 {
000041 SELECT sql FROM sqlite_schema;
000042 } {
000043 {CREATE TABLE t1(b, a, PRIMARY KEY(b, a)) WITHOUT ROWID}
000044 }
000045
000046 proc do_atdc_error_test {tn schema atdc error} {
000047 reset_db
000048 execsql $schema
000049 uplevel [list do_catchsql_test $tn $atdc [list 1 [string trim $error]]]
000050 }
000051
000052 #-------------------------------------------------------------------------
000053 # Test cases 2.* attempt to verify the following:
000054 #
000055 # EVIDENCE-OF: R-24098-10282 The DROP COLUMN command only works if the column
000056 # is not referenced by any other parts of the schema and is not a PRIMARY KEY
000057 # and does not have a UNIQUE constraint.
000058 #
000059
000060 # EVIDENCE-OF: R-52436-31752 The column is a PRIMARY KEY or part of one.
000061 #
000062 do_atdc_error_test 2.1.1 {
000063 CREATE TABLE x1(a PRIMARY KEY, b, c);
000064 } {
000065 ALTER TABLE x1 DROP COLUMN a
000066 } {
000067 cannot drop PRIMARY KEY column: "a"
000068 }
000069 do_atdc_error_test 2.1.2 {
000070 CREATE TABLE x1(a,b,c,d,e, PRIMARY KEY(b,c,d));
000071 } {
000072 ALTER TABLE x1 DROP COLUMN c
000073 } {
000074 cannot drop PRIMARY KEY column: "c"
000075 }
000076
000077 # EVIDENCE-OF: R-43412-16016 The column has a UNIQUE constraint.
000078 #
000079 do_atdc_error_test 2.2.1 {
000080 CREATE TABLE x1(a PRIMARY KEY, b, c UNIQUE);
000081 } {
000082 ALTER TABLE x1 DROP COLUMN c
000083 } {
000084 cannot drop UNIQUE column: "c"
000085 }
000086 do_atdc_error_test 2.2.2 {
000087 CREATE TABLE x1(a PRIMARY KEY, b, c, UNIQUE(b, c));
000088 } {
000089 ALTER TABLE x1 DROP COLUMN c
000090 } {
000091 error in table x1 after drop column: no such column: c
000092 }
000093
000094 # EVIDENCE-OF: R-46731-08965 The column is indexed.
000095 #
000096 do_atdc_error_test 2.3.1 {
000097 CREATE TABLE 'one two'('x y', 'z 1', 'a b');
000098 CREATE INDEX idx ON 'one two'('z 1');
000099 } {
000100 ALTER TABLE 'one two' DROP COLUMN 'z 1'
000101 } {
000102 error in index idx after drop column: no such column: z 1
000103 }
000104 do_atdc_error_test 2.3.2 {
000105 CREATE TABLE x1(a, b, c);
000106 CREATE INDEX idx ON x1(a);
000107 } {
000108 ALTER TABLE x1 DROP COLUMN a;
000109 } {
000110 error in index idx after drop column: no such column: a
000111 }
000112
000113 # EVIDENCE-OF: R-46731-08965 The column is indexed.
000114 #
000115 do_atdc_error_test 2.4.1 {
000116 CREATE TABLE x1234(a, b, c PRIMARY KEY) WITHOUT ROWID;
000117 CREATE INDEX i1 ON x1234(b) WHERE ((a+5) % 10)==0;
000118 } {
000119 ALTER TABLE x1234 DROP a
000120 } {
000121 error in index i1 after drop column: no such column: a
000122 }
000123
000124 # EVIDENCE-OF: R-47838-03249 The column is named in a table or column
000125 # CHECK constraint not associated with the column being dropped.
000126 #
000127 do_atdc_error_test 2.5.1 {
000128 CREATE TABLE x1234(a, b, c PRIMARY KEY, CHECK(((a+5)%10)!=0)) WITHOUT ROWID;
000129 } {
000130 ALTER TABLE x1234 DROP a
000131 } {
000132 error in table x1234 after drop column: no such column: a
000133 }
000134
000135 # EVIDENCE-OF: R-55640-01652 The column is used in a foreign key constraint.
000136 #
000137 do_atdc_error_test 2.6.1 {
000138 CREATE TABLE p1(x, y UNIQUE);
000139 CREATE TABLE c1(u, v, FOREIGN KEY (v) REFERENCES p1(y))
000140 } {
000141 ALTER TABLE c1 DROP v
000142 } {
000143 error in table c1 after drop column: unknown column "v" in foreign key definition
000144 }
000145
000146 # EVIDENCE-OF: R-20795-39479 The column is used in the expression of a
000147 # generated column.
000148 do_atdc_error_test 2.7.1 {
000149 CREATE TABLE c1(u, v, w AS (u+v));
000150 } {
000151 ALTER TABLE c1 DROP v
000152 } {
000153 error in table c1 after drop column: no such column: v
000154 }
000155 do_atdc_error_test 2.7.2 {
000156 CREATE TABLE c1(u, v, w AS (u+v) STORED);
000157 } {
000158 ALTER TABLE c1 DROP u
000159 } {
000160 error in table c1 after drop column: no such column: u
000161 }
000162
000163 # EVIDENCE-OF: R-01515-49025 The column appears in a trigger or view.
000164 #
000165 do_atdc_error_test 2.8.1 {
000166 CREATE TABLE log(l);
000167 CREATE TABLE c1(u, v, w);
000168 CREATE TRIGGER tr1 AFTER INSERT ON c1 BEGIN
000169 INSERT INTO log VALUES(new.w);
000170 END;
000171 } {
000172 ALTER TABLE c1 DROP w
000173 } {
000174 error in trigger tr1 after drop column: no such column: new.w
000175 }
000176 do_atdc_error_test 2.8.2 {
000177 CREATE TABLE c1(u, v, w);
000178 CREATE VIEW v1 AS SELECT u, v, w FROM c1;
000179 } {
000180 ALTER TABLE c1 DROP w
000181 } {
000182 error in view v1 after drop column: no such column: w
000183 }
000184 do_atdc_error_test 2.8.3 {
000185 CREATE TABLE c1(u, v, w);
000186 CREATE VIEW v1 AS SELECT * FROM c1 WHERE w IS NOT NULL;
000187 } {
000188 ALTER TABLE c1 DROP w
000189 } {
000190 error in view v1 after drop column: no such column: w
000191 }
000192
000193 #-------------------------------------------------------------------------
000194 # Verify that a column that is part of a CHECK constraint may be dropped
000195 # if the CHECK constraint was specified as part of the column definition.
000196 #
000197
000198 # STALE-EVIDENCE: R-60924-11170 However, the column being deleted can be used in a
000199 # column CHECK constraint because the column CHECK constraint is dropped
000200 # together with the column itself.
000201 do_execsql_test 3.0 {
000202 CREATE TABLE yyy(q, w, e CHECK (e > 0), r);
000203 INSERT INTO yyy VALUES(1,1,1,1), (2,2,2,2);
000204
000205 CREATE TABLE zzz(q, w, e, r, CHECK (e > 0));
000206 INSERT INTO zzz VALUES(1,1,1,1), (2,2,2,2);
000207 }
000208 do_catchsql_test 3.1.1 {
000209 INSERT INTO yyy VALUES(0,0,0,0);
000210 } {1 {CHECK constraint failed: e > 0}}
000211 do_catchsql_test 3.1.2 {
000212 INSERT INTO yyy VALUES(0,0,0,0);
000213 } {1 {CHECK constraint failed: e > 0}}
000214
000215 do_execsql_test 3.2.1 {
000216 ALTER TABLE yyy DROP e;
000217 }
000218 do_catchsql_test 3.2.2 {
000219 ALTER TABLE zzz DROP e;
000220 } {1 {error in table zzz after drop column: no such column: e}}
000221
000222 finish_test