gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/alterdropcol2.test (about) 1 # 2021 February 19 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 alterdropcol2 16 17 # If SQLITE_OMIT_ALTERTABLE is defined, omit this file. 18 ifcapable !altertable { 19 finish_test 20 return 21 } 22 23 # EVIDENCE-OF: R-58318-35349 The DROP COLUMN syntax is used to remove an 24 # existing column from a table. 25 do_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 } 29 do_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 that 35 # column. 36 do_execsql_test 1.2.1 { 37 SELECT * FROM t1; 38 } {2 3 5 6} 39 40 do_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 46 proc do_atdc_error_test {tn schema atdc error} { 47 reset_db 48 execsql $schema 49 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 column 56 # is not referenced by any other parts of the schema and is not a PRIMARY KEY 57 # 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 # 62 do_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 } 69 do_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 c 73 } { 74 cannot drop PRIMARY KEY column: "c" 75 } 76 77 # EVIDENCE-OF: R-43412-16016 The column has a UNIQUE constraint. 78 # 79 do_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 } 86 do_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: c 92 } 93 94 # EVIDENCE-OF: R-46731-08965 The column is indexed. 95 # 96 do_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 1 103 } 104 do_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: a 111 } 112 113 # EVIDENCE-OF: R-46731-08965 The column is indexed. 114 # 115 do_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 a 120 } { 121 error in index i1 after drop column: no such column: a 122 } 123 124 # EVIDENCE-OF: R-47838-03249 The column is named in a table or column 125 # CHECK constraint not associated with the column being dropped. 126 # 127 do_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 a 131 } { 132 error in table x1234 after drop column: no such column: a 133 } 134 135 # EVIDENCE-OF: R-55640-01652 The column is used in a foreign key constraint. 136 # 137 do_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 v 142 } { 143 error in table c1 after drop column: unknown column "v" in foreign key definition 144 } 145 146 # EVIDENCE-OF: R-20795-39479 The column is used in the expression of a 147 # generated column. 148 do_atdc_error_test 2.7.1 { 149 CREATE TABLE c1(u, v, w AS (u+v)); 150 } { 151 ALTER TABLE c1 DROP v 152 } { 153 error in table c1 after drop column: no such column: v 154 } 155 do_atdc_error_test 2.7.2 { 156 CREATE TABLE c1(u, v, w AS (u+v) STORED); 157 } { 158 ALTER TABLE c1 DROP u 159 } { 160 error in table c1 after drop column: no such column: u 161 } 162 163 # EVIDENCE-OF: R-01515-49025 The column appears in a trigger or view. 164 # 165 do_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 BEGIN 169 INSERT INTO log VALUES(new.w); 170 END; 171 } { 172 ALTER TABLE c1 DROP w 173 } { 174 error in trigger tr1 after drop column: no such column: new.w 175 } 176 do_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 w 181 } { 182 error in view v1 after drop column: no such column: w 183 } 184 do_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 w 189 } { 190 error in view v1 after drop column: no such column: w 191 } 192 193 #------------------------------------------------------------------------- 194 # Verify that a column that is part of a CHECK constraint may be dropped 195 # 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 a 199 # column CHECK constraint because the column CHECK constraint is dropped 200 # together with the column itself. 201 do_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 } 208 do_catchsql_test 3.1.1 { 209 INSERT INTO yyy VALUES(0,0,0,0); 210 } {1 {CHECK constraint failed: e > 0}} 211 do_catchsql_test 3.1.2 { 212 INSERT INTO yyy VALUES(0,0,0,0); 213 } {1 {CHECK constraint failed: e > 0}} 214 215 do_execsql_test 3.2.1 { 216 ALTER TABLE yyy DROP e; 217 } 218 do_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 222 finish_test