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
|
--
-- 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);
-- lets start with negative tests first.
alter table alltypes alter c set data type char(20);
alter table alltypes alter b set data type char(4) for bit data;
alter table alltypes alter nc set data type char(20);
alter table alltypes alter dc set data type decimal (8,2);
alter table alltypes alter n set data type numeric (12,8);
alter table alltypes alter c set data type varchar(10);
alter table alltypes alter b set data type varchar(2) for bit data;
alter table alltypes alter dc set data type numeric(8,2);
alter table alltypes alter tn set data type int;
alter table alltypes alter v set data type varchar(1);
alter table alltypes alter v set data type varchar(49);
alter table alltypes alter bv set data type varchar(1) for bit data;
alter table alltypes alter bv set data type varchar(2) for bit data;
alter table alltypes alter nvc set data type varchar(0);
alter table alltypes alter nvc set data type varchar(9);
drop table alltypes;
create table t0 (i int not null, v varchar(1) not null, constraint pk primary key(v,i));
-- this should work. primary key constraint has no referencing fkey
-- constraints.
alter table t0 alter v set data type varchar(2);
create table t1 (i int, v varchar(2), constraint fk foreign key (v,i) references t0(v,i));
alter table t0 alter v set data type varchar(3);
-- should fail; can't muck around with fkey constraints.
alter table t1 alter v set data type varchar(3);
drop table t1;
drop table t0;
-- 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));
-- this should work. unique constraint has no referencing fkey
-- constraints.
alter table t0 alter v set data type varchar(2);
create table t1 (i int, v varchar(2), constraint fk foreign key (v,i) references t0(v,i));
-- this should fail-- someone is referencing me.
alter table t0 alter v set data type varchar(3);
drop table t1;
drop table t0;
--
-- 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));
insert into t1 values(DEFAULT);
select * from t1;
-- this should fail
alter table t1 modify a null;
insert into t1 values(DEFAULT);
select * from t1;
drop table t1;
-- 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);
alter table t1 add constraint uq unique (vc, nvc, bv);
insert into t1 values ('p', 'p', x'01');
insert into t1 values ('pe', 'p', x'01');
alter table t1 alter vc set data type varchar(2);
insert into t1 values ('pe', 'p', x'01');
insert into t1 values ('pe', 'pe', x'01');
alter table t1 alter nvc set data type varchar(2);
insert into t1 values ('pe', 'pe', x'01');
insert into t1 values ('pe', 'pe', x'1000');
alter table t1 alter bv set data type varchar(2) for bit data;
insert into t1 values ('pe', 'pe', x'1000');
-- make sure constraints aren't lost due to an alter.
insert into t1 values ('pe','pe', x'01');
-- do some selects to ensure consistency of data.
select * from t1 where vc='pe';
select * from t1 where vc='pe';
alter table t1 alter vc set data type varchar(3);
select * from t1 where vc='pe';
select * from t1 where vc='pe';
-- clean up
drop table t1;
-- 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));
insert into a values (1, 'abc');
-- Should fail
insert into a values (2, null);
alter table a alter name set data type varchar(50);
insert into a values (3, 'hijk');
-- Used to pass before the fix
insert into a values (4, null);
select * from a;
drop table a;
-- Now test the otherway, nullable column to start with
create table a (id integer not null, name varchar(20));
insert into a values (1, 'abc');
insert into a values (2, null);
alter table a alter name set data type varchar(50);
insert into a values (3, 'hijk');
insert into a values (4, null);
select * from a;
drop table a;
|
