Debian Codegraph @

test/alterdropcol2.test

raw
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