1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
|
ij> --
-- Licensed to the Apache Software Foundation (ASF) under one or more
-- contributor license agreements. See the NOTICE file distributed with
-- this work for additional information regarding copyright ownership.
-- The ASF licenses this file to You under the Apache License, Version 2.0
-- (the "License"); you may not use this file except in compliance with
-- the License. You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
-- Testing changing the length of a column.
-- Also testing the new syntax for generated column spec and identity attribute
create table alltypes (i int, tn int, s smallint, l bigint,
c char(10), v varchar(50), lvc long varchar,
nc char(10), nvc varchar(10),
d double precision, r real, f float,
dt date, t time, ts timestamp,
b char(2) for bit data, bv varchar(2) for bit data, lbv long varchar for bit data,
dc decimal(5,2), n numeric(8,4), o bigint);
0 rows inserted/updated/deleted
ij> -- lets start with negative tests first.
alter table alltypes alter c set data type char(20);
ERROR 42Z16: Only columns of type VARCHAR, CLOB, and BLOB may have their length altered.
ij> alter table alltypes alter b set data type char(4) for bit data;
ERROR 42Z16: Only columns of type VARCHAR, CLOB, and BLOB may have their length altered.
ij> alter table alltypes alter nc set data type char(20);
ERROR 42Z16: Only columns of type VARCHAR, CLOB, and BLOB may have their length altered.
ij> alter table alltypes alter dc set data type decimal (8,2);
ERROR 42Z16: Only columns of type VARCHAR, CLOB, and BLOB may have their length altered.
ij> alter table alltypes alter n set data type numeric (12,8);
ERROR 42Z16: Only columns of type VARCHAR, CLOB, and BLOB may have their length altered.
ij> alter table alltypes alter c set data type varchar(10);
ERROR 42Z15: Invalid type specified for column 'C'. The type of a column may not be changed.
ij> alter table alltypes alter b set data type varchar(2) for bit data;
ERROR 42Z15: Invalid type specified for column 'B'. The type of a column may not be changed.
ij> alter table alltypes alter dc set data type numeric(8,2);
ERROR 42Z15: Invalid type specified for column 'DC'. The type of a column may not be changed.
ij> alter table alltypes alter tn set data type int;
ERROR 42Z16: Only columns of type VARCHAR, CLOB, and BLOB may have their length altered.
ij> alter table alltypes alter v set data type varchar(1);
ERROR 42Z17: Invalid length specified for column 'V'. Length must be greater than the current column length.
ij> alter table alltypes alter v set data type varchar(49);
ERROR 42Z17: Invalid length specified for column 'V'. Length must be greater than the current column length.
ij> alter table alltypes alter bv set data type varchar(1) for bit data;
ERROR 42Z17: Invalid length specified for column 'BV'. Length must be greater than the current column length.
ij> alter table alltypes alter bv set data type varchar(2) for bit data;
0 rows inserted/updated/deleted
ij> alter table alltypes alter nvc set data type varchar(0);
ERROR 42X44: Invalid length '0' in column specification.
ij> alter table alltypes alter nvc set data type varchar(9);
ERROR 42Z17: Invalid length specified for column 'NVC'. Length must be greater than the current column length.
ij> drop table alltypes;
0 rows inserted/updated/deleted
ij> create table t0 (i int not null, v varchar(1) not null, constraint pk primary key(v,i));
0 rows inserted/updated/deleted
ij> -- this should work. primary key constraint has no referencing fkey
-- constraints.
alter table t0 alter v set data type varchar(2);
0 rows inserted/updated/deleted
ij> create table t1 (i int, v varchar(2), constraint fk foreign key (v,i) references t0(v,i));
0 rows inserted/updated/deleted
ij> alter table t0 alter v set data type varchar(3);
ERROR 42Z19: Column 'V' is being referenced by at least one foreign key constraint 'FK'. To alter the length of this column, you should drop referencing constraints, perform the ALTER TABLE and then recreate the constraints.
ij> -- should fail; can't muck around with fkey constraints.
alter table t1 alter v set data type varchar(3);
ERROR 42Z18: Column 'V' is part of a foreign key constraint 'FK'. To alter the length of this column, you should drop the constraint first, perform the ALTER TABLE, and then recreate the constraint.
ij> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t0;
0 rows inserted/updated/deleted
ij> -- do the same thing over again with a unique key constraint this time.
create table t0 (i int not null, v varchar(1) not null, constraint uq unique(v,i));
0 rows inserted/updated/deleted
ij> -- this should work. unique constraint has no referencing fkey
-- constraints.
alter table t0 alter v set data type varchar(2);
0 rows inserted/updated/deleted
ij> create table t1 (i int, v varchar(2), constraint fk foreign key (v,i) references t0(v,i));
0 rows inserted/updated/deleted
ij> -- this should fail-- someone is referencing me.
alter table t0 alter v set data type varchar(3);
ERROR 42Z19: Column 'V' is being referenced by at least one foreign key constraint 'FK'. To alter the length of this column, you should drop referencing constraints, perform the ALTER TABLE and then recreate the constraints.
ij> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t0;
0 rows inserted/updated/deleted
ij> --
-- test that we can't alter a column with an autoincrement default to nullable
create table t1(a int generated always as identity (start with 1, increment by 1));
0 rows inserted/updated/deleted
ij> insert into t1 values(DEFAULT);
1 row inserted/updated/deleted
ij> select * from t1;
A
-----------
1
ij> -- this should fail
alter table t1 modify a null;
ERROR 42X01: Syntax error: Encountered "modify" at line 2, column 16.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
ij> insert into t1 values(DEFAULT);
1 row inserted/updated/deleted
ij> select * from t1;
A
-----------
1
2
ij> drop table t1;
0 rows inserted/updated/deleted
ij> -- lets get to positive tests.
create table t1 (vc varchar(1) not null, nvc varchar(1) not null, bv varchar(1) for bit data not null);
0 rows inserted/updated/deleted
ij> alter table t1 add constraint uq unique (vc, nvc, bv);
0 rows inserted/updated/deleted
ij> insert into t1 values ('p', 'p', x'01');
1 row inserted/updated/deleted
ij> insert into t1 values ('pe', 'p', x'01');
ERROR 22001: A truncation error was encountered trying to shrink VARCHAR 'pe' to length 1.
ij> alter table t1 alter vc set data type varchar(2);
0 rows inserted/updated/deleted
ij> insert into t1 values ('pe', 'p', x'01');
1 row inserted/updated/deleted
ij> insert into t1 values ('pe', 'pe', x'01');
ERROR 22001: A truncation error was encountered trying to shrink VARCHAR 'pe' to length 1.
ij> alter table t1 alter nvc set data type varchar(2);
0 rows inserted/updated/deleted
ij> insert into t1 values ('pe', 'pe', x'01');
1 row inserted/updated/deleted
ij> insert into t1 values ('pe', 'pe', x'1000');
ERROR 22001: A truncation error was encountered trying to shrink VARCHAR () FOR BIT DATA '1000' to length 1.
ij> alter table t1 alter bv set data type varchar(2) for bit data;
0 rows inserted/updated/deleted
ij> insert into t1 values ('pe', 'pe', x'1000');
1 row inserted/updated/deleted
ij> -- make sure constraints aren't lost due to an alter.
insert into t1 values ('pe','pe', x'01');
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'UQ' defined on 'T1'.
ij> -- do some selects to ensure consistency of data.
select * from t1 where vc='pe';
VC|N&|BV
----------
pe|p |01
pe|pe|01
pe|pe|1000
ij> select * from t1 where vc='pe';
VC|N&|BV
----------
pe|p |01
pe|pe|01
pe|pe|1000
ij> alter table t1 alter vc set data type varchar(3);
0 rows inserted/updated/deleted
ij> select * from t1 where vc='pe';
VC |N&|BV
-----------
pe |p |01
pe |pe|01
pe |pe|1000
ij> select * from t1 where vc='pe';
VC |N&|BV
-----------
pe |p |01
pe |pe|01
pe |pe|1000
ij> -- clean up
drop table t1;
0 rows inserted/updated/deleted
ij> -- DERBY-882
-- ALTER TABLE to increase size of varchar could convert a non-null column to nullable
-- before fix for DERBY-882
create table a (id integer not null, name varchar(20) not null, primary key(name));
0 rows inserted/updated/deleted
ij> insert into a values (1, 'abc');
1 row inserted/updated/deleted
ij> -- Should fail
insert into a values (2, null);
ERROR 23502: Column 'NAME' cannot accept a NULL value.
ij> alter table a alter name set data type varchar(50);
0 rows inserted/updated/deleted
ij> insert into a values (3, 'hijk');
1 row inserted/updated/deleted
ij> -- Used to pass before the fix
insert into a values (4, null);
ERROR 23502: Column 'NAME' cannot accept a NULL value.
ij> select * from a;
ID |NAME
--------------------------------------------------------------
1 |abc
3 |hijk
ij> drop table a;
0 rows inserted/updated/deleted
ij> -- Now test the otherway, nullable column to start with
create table a (id integer not null, name varchar(20));
0 rows inserted/updated/deleted
ij> insert into a values (1, 'abc');
1 row inserted/updated/deleted
ij> insert into a values (2, null);
1 row inserted/updated/deleted
ij> alter table a alter name set data type varchar(50);
0 rows inserted/updated/deleted
ij> insert into a values (3, 'hijk');
1 row inserted/updated/deleted
ij> insert into a values (4, null);
1 row inserted/updated/deleted
ij> select * from a;
ID |NAME
--------------------------------------------------------------
1 |abc
2 |NULL
3 |hijk
4 |NULL
ij> drop table a;
0 rows inserted/updated/deleted
ij>
|
