altertable.sql

derby database source code.good for you.

-- alter table tests
-- add column
-- (add constraint & drop constraint to be added)

-- create some database objects
create table t0(c1 int not null constraint p1 primary key);
create table t0_1(c1 int);
create table t0_2(c1 int);
create table t0_3(c1 int);
create table t1(c1 int);
create table t1_1(c1 int);
create table t2(c1 int);
create table t3(c1 int);
create table t4(c1 int not null);
create view v1 as select * from t2;
create view v2 as select c1 from t2;
create index i0_1 on t0_1(c1);
create index i0_2 on t0_2(c1);

-- do some population
insert into t1 values 1;
insert into t1_1 values 1;
insert into t2 values 1;
insert into t2 values 2;
insert into t3 values 1;
insert into t3 values 2;
insert into t3 values 3;
insert into t4 values 1, 2, 3, 1;

autocommit off;

-- add column

-- negative tests

-- alter a non-existing table
alter table notexists add column c1 int;

-- add a column that already exists
alter table t0 add column c1 int;

-- alter a system table
alter table sys.systables add column c1 int;

-- alter table on a view
alter table v2 add column c2 int;

-- add a primary key column to a table which already has one
-- this will produce an error
alter table t0 add column c2 int not null default 0 primary key;

-- add a unique column constraint to a table with > 1 row
alter table t3 add column c2 int not null default 0 unique;

-- cannot alter a table when there is an open cursor on it
get cursor c1 as 'select * from t1';
alter table t1 add column c2 int;
close c1;

-- positive tests

-- add a non-nullable column to a non-empty table
alter table t1 add column c2 int not null default 0;

-- add a primary key column to a non-empty table
alter table t1 add column c3 int not null default 0 primary key;

-- add a column with a check constraint to a non-empty column
alter table t1 add column c4 int check(c4 = 1);


select * from v1;
prepare p1 as 'select * from t2';
execute p1;

alter table t2 add column c2 int;

-- select * views don't see added columns after alter table
select * from v1;

-- select * prepared statements do see added columns after alter table
execute p1;

-- rollback and recheck
rollback;
select * from v1;
execute p1;
remove p1;

-- add non-nullable column to 0 row table and verify
alter table t0 add column c2 int not null default 0;
insert into t0 values (1, default);
select * from t0;
drop table t0;
rollback;
select  * from t0;

-- add primary key to 0 row table and verify
alter table t0_1 add column c2 int not null primary key default 0;
insert into t0_1 values (1, 1);
insert into t0_1 values (1, 1);
select * from t0_1;
rollback;

-- add unique constraint to 0 and 1 row tables and verify 
 alter table t0_1 add column c2 int not null unique default 0;
 insert into t0_1 values (1, default);
 insert into t0_1 values (2, default);
 insert into t0_1 values (3, 1);
 delete from t1;
 alter table t1 add column c2 int not null unique default 0;
 insert into t1 values (2, 2);
 insert into t1 values (3, 1);

-- verify the consistency of the indexes on the user tables
select tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', tablename)
from sys.systables where tabletype = 'T';

 rollback;

create function countopens() returns varchar(128)
language java parameter style java
external name 'org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker.countOpens';
commit;

-- do consistency check on scans, etc.
values countopens();


-- some typical data

create table tab1 (c1 int, c2 int not null constraint tab1pk primary key, c3 double, c4 int);
create index i11 on tab1 (c1);
create unique index i12 on tab1 (c1);
create index i13 on tab1 (c3, c1, c4);
create unique index i14 on tab1 (c3, c1);
insert into tab1 values (6, 5, 4.5, 90);
insert into tab1 values (10, 3, 8.9, -5);
insert into tab1 values (100, 15, 4.5, 9);
insert into tab1 values (2, 8, 4.4, 8);
insert into tab1 values (11, 9, 2.5, 88);
insert into tab1 values(null,10, 3.5, 99);

create view vw1 (col_sum, col_diff) as select c1+c4, c1-c4 from tab1;
create view vw2 (c1) as select c3 from tab1;

create table tab2 (c1 int not null unique, c2 double, c3 int, c4 int not null constraint c4_PK primary key, c5 int, constraint t2ck check (c2+c3<100.0));
create table tab3 (c1 int, c2 int, c3 int, c4 int, constraint t3fk foreign key (c2) references tab2(c1), constraint t3ck check (c2-c3<80));

create view vw3 (c1, c2) as select c5, tab3.c4 from tab2, tab3 where tab3.c1 > 0;
create view vw4 (c1) as select c4 from tab3 where c2 > 8;

create table tab4 (c1 int, c2 int, c3 int, c4 int);
create table tab5 (c1 int);
insert into tab4 values (1,2,3,4);
create trigger tr1 after update of c2, c3, c4 on tab4 for each row mode db2sql insert into tab5 values (1);
create trigger tr2 after update of c3, c4 on tab4 for each row mode db2sql insert into tab5 values (2);

-- tr1 is dropped, tr2 still OK
drop trigger tr1;

select * from tab5;
-- fire tr2 only
update tab4 set c3 = 33;
update tab4 set c4 = 44;
select * from tab5;

-- drop tr2
drop trigger tr2;

update tab4 set c4 = 444;
select * from tab2;

drop view vw2;
create view vw2 (c1) as select c3 from tab1;

-- vw1 should be dropped
drop view vw1;
select * from vw1;

-- do the indexes still exist?
-- the create index statements should fail
create index i13 on tab1 (c3, c1, c4);
create unique index i14 on tab1 (c3, c1);
create unique index i12 on tab1 (c1);
select c2, c3, c4 from tab1 order by c3;
drop index i12;
drop index i13;
drop index i14;

-- more data
insert into tab1 (c2, c3, c4) values (22, 8.9, 5);
insert into tab1 (c2, c3, c4) values (11, 4.5, 67);
select c2 from tab1;

-- add a new column
alter table tab1 add column c5 double;

-- drop view vw2 so can create a new one, with where clause
drop view vw2;
create view vw2 (c1) as select c5 from tab1 where c2 > 5;

-- drop vw2 as well
drop view vw2;
alter table tab1 drop constraint tab1pk;

-- any surviving index? 
-- creating the index should not fail
select c4 from tab1 order by 1;
create index i13 on tab1 (c3, c1, c4);

-- should drop t2ck
alter table tab2 drop constraint t2ck;

-- this should drop t3fk, unique constraint and backing index
alter table tab3 drop constraint t3fk;
alter table tab2 drop constraint c4_PK;
insert into tab3 values (1,2,3,4);

-- drop view vw3
drop view vw3;

-- violates t3ck
insert into tab3 (c1, c2, c3) values (81, 1, 2);
insert into tab3 (c1, c2, c3) values (81, 2, 2);

-- this should drop t3ck, vw4
alter table tab3 drop constraint t3ck;
drop view vw4;
insert into tab3 (c2, c3) values (-82, 9);
create view vw4 (c1) as select c3 from tab3 where c3+5>c4;

-- drop view vw4
drop view vw4;

rollback;

-- check that dropping a column will drop backing index on referencing
-- table
create table tt1(a int, b int not null constraint tt1uc unique);
create table reftt1(a int constraint reftt1rc references tt1(b));
-- count should be 2
select count(*) 
from sys.sysconglomerates c, sys.systables t 
where t.tableid = c.tableid
and t.tablename = 'REFTT1';

alter table reftt1 drop constraint reftt1rc;
alter table tt1 drop constraint tt1uc;

-- count should be 1
select count(*) 
from sys.sysconglomerates c, sys.systables t 
where t.tableid = c.tableid
and t.tablename = 'REFTT1';
rollback;

-- add constraint

-- negative tests

-- add primary key to table which already has one
alter table t0 add column c3 int;
alter table t0 add constraint cons1 primary key(c3);
alter table t0 add primary key(c3);

-- add constraint references non-existant column

alter table t4 add constraint t4pk primary key("c1");
alter table t4 add constraint t4uq unique("c1");
alter table t4 add constraint t4fk foreign key ("c1") references t0;
alter table t4 add constraint t4ck check ("c1" <> 4);

-- add primary key to non-empty table with duplicates
alter table t4 add primary key(c1);

-- positive tests