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 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 may have their length altered. ij> alter table alltypes alter nc set data type char(20); ERROR 42Z16: Only columns of type VARCHAR may have their length altered. ij> alter table alltypes alter dc set data type decimal (8,2); ERROR 42Z16: Only columns of type VARCHAR may have their length altered. ij> alter table alltypes alter n set data type numeric (12,8); ERROR 42Z16: Only columns of type VARCHAR 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 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. 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>