altertable.sql

derby database source code.good for you.

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

-- add check constraint to 0 row table and verify
alter table t0_1 add column c2 int check(c2 != 3);
insert into t0_1 values (1, 1);
insert into t0_1 values (1, 3);
insert into t0_1 values (1, 1);
select * from t0_1;
rollback;

-- add check constraint to table with rows that are ok
alter table t0_1 add column c2 int;
insert into t0_1 values (1, 1);
insert into t0_1 values (2, 2);
alter table t0_1 add constraint ck1 check(c2 = c1);
select * from t0_1;

-- verify constraint has been added, the following should fail
insert into t0_1 values (1, 3);
rollback;


-- add check constraint to table with rows w/ 3 failures
alter table t0_1 add column c2 int;
insert into t0_1 values (1, 1);
insert into t0_1 values (2, 2);
insert into t0_1 values (2, 2);
insert into t0_1 values (666, 2);
insert into t0_1 values (2, 2);
insert into t0_1 values (3, 3);
insert into t0_1 values (666, 3);
insert into t0_1 values (666, 3);
insert into t0_1 values (3, 3);
alter table t0_1 add constraint ck1 check(c2 = c1);

-- verify constraint has NOT been added, the following should succeed
insert into t0_1 values (1, 3);
select * from t0_1;
rollback;


-- check and primary key constraints on same table and enforced
alter table t0_1 add column c2 int not null constraint p2 primary key default 0;
alter table t0_1 add check(c2 = c1);
insert into t0_1 values (1, 1);
insert into t0_1 values (1, 2);
insert into t0_1 values (1, 1);
insert into t0_1 values (2, 1);
insert into t0_1 values (2, 2);
select * from t0_1;
rollback;

-- add primary key constraint to table with > 1 row
alter table t3 add column c3 int;
alter table t3 add unique(c3);

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

-- add unique constraint to table with > 1 row
alter table t3 add unique(c1);

-- verify prepared alter table dependent on underlying table
prepare p1 as 'alter table xxx add check(c2 = 1)';
create table xxx(c1 int, c2 int);
prepare p1 as 'alter table xxx add check(c2 = 1)';
execute p1;
drop table xxx;
create table xxx(c1 int);
execute p1;
alter table xxx add column c2 int;
execute p1;
drop table xxx;

-- 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';


-- drop constraint

-- negative tests

-- drop non-existent constraint
alter table t0 drop constraint notexists;

-- constraint/table mismatch
alter table t1 drop constraint p1;


-- In DB2 compatibility mode, we cann't add a nullable primary key
alter table t0_1 add constraint p2 primary key(c1);
alter table t0_1 drop constraint p2;

-- positive tests
-- verify that we can add/drop/add/drop/... constraints
alter table t0_1 add column c2 int not null constraint p2 primary key default 0;
delete from t0_1;
alter table t0_1 drop constraint p2;
alter table t0_1 add constraint p2 primary key(c2);
alter table t0_1 drop constraint p2;
alter table t0_1 add constraint p2 primary key(c2);


-- verify that constraint is still enforced
insert into t0_1 values (1,1);
insert into t0_1 values (1,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' and tablename = 'T0_1';


-- verify that alter table works after drop/recreate of table
prepare p1 as 'alter table t0_1 drop constraint p2';
execute p1;
drop table t0_1;
create table t0_1 (c1 int, c2 int not null constraint p2 primary key);
execute p1;

-- do consistency check on scans, etc.
-- values (org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker::countOpens());

-- verify the consistency of the indexes on the system catalogs
select tablename, SYSCS_UTIL.SYSCS_CHECK_TABLE('SYS', tablename)
from sys.systables where tabletype = 'S' and tablename != 'SYSDUMMY1';

-- 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';

-- bugs 793
create table b793 (pn1 int not null constraint named_primary primary key, 
				   pn2 int constraint named_pn2 check (pn2 > 3));
alter table b793 drop constraint named_primary;
drop table b793;

-- test that drop constraint removes backing indexes
drop table t1;
create table t1(a int not null constraint t1_pri primary key);
create table reft1(a int constraint t1_ref references t1(a));
-- count should be 2
select count(*) 
from sys.sysconglomerates c, sys.systables t
where c.tableid = t.tableid and
t.tablename = 'REFT1';
alter table reft1 drop constraint t1_ref;
alter table t1 drop constraint t1_pri;
-- count should be 1
select count(*) 
from sys.sysconglomerates c, sys.systables t
where c.tableid = t.tableid and
t.tablename = 'REFT1';
drop table reft1;


-- clean up
drop view v2;
drop view v1;
drop table t0;
drop table t0_1;
drop table t0_2;
drop table t0_3;
drop table t1;
drop table t1_1;
drop table t3;
drop table t4;

------------------------------------------------------
--
-- special funky schema tests
--
------------------------------------------------------

create schema newschema;
drop table x;
create table x (x int not null, y int not null);
alter table x add constraint newcons primary key (x);

-- schemaname should be app
select schemaname, constraintname from sys.sysconstraints c, sys.sysschemas s where s.schemaid = c.schemaid order by 1;
insert into x values (1,1),(1,1);
alter table x drop constraint app.newcons;
alter table x add constraint newcons primary key (x);

-- schemaname should be app
select schemaname, constraintname from sys.sysconstraints c, sys.sysschemas s where s.schemaid = c.schemaid order by 1;

-- fail
alter table x drop constraint badschema.newcons;

-- fail
alter table x drop constraint newschema.newcons;

-- ok
alter table x drop constraint app.newcons;

-- bad schema name
alter table x add constraint badschema.newcons primary key (x);

-- two constriants, same name, different schema (second will fail)
drop table x;
create table x (x int not null, y int not null);
alter table x add constraint con check (x > 1);
alter table x add constraint newschema.con check (x > 1);
select schemaname, constraintname from sys.sysconstraints c, sys.sysschemas s where s.schemaid = c.schemaid order by 1;

create schema emptyschema;
set schema emptyschema;

-- fail, cannot find emptyschema.conn
alter table app.x drop constraint emptyschema.con;
select schemaname, constraintname from sys.sysconstraints c, sys.sysschemas s where s.schemaid = c.schemaid order by 1;

set schema newschema;

-- add constraint, default to table schema
alter table app.x add constraint con2 check (x > 1);

-- added constraint in APP (defaults to table's schema)
select schemaname, constraintname from sys.sysconstraints c, sys.sysschemas s where s.schemaid = c.schemaid order by 1,2;

drop table app.x;
drop schema newschema restrict;


-- some temporary table tests
-- declare temp table with no explicit on commit behavior.
declare global temporary table session.t1 (c11 int) not logged;
declare global temporary table session.t2 (c21 int) on commit delete rows not logged;
declare global temporary table session.t3 (c31 int) on commit preserve rows not logged;
drop table session.t1;
drop table session.t2;
drop table session.t3;

drop table t1;
create table t1(c1 int, c2 int not null primary key);
insert into t1 values (1, 1);
insert into t1 values (1, 1);
alter table t1 drop primary key;
insert into t1 values (1, 1);
select * from t1;
alter table t1 drop primary key;

alter table t1 drop constraint emptyschema.C1;
alter table t1 drop constraint nosuchschema.C2;

alter table t1 add constraint emptyschema.C1_PLUS_C2 check ((c1 + c2) < 100);
alter table t1 add constraint C1_PLUS_C2 check ((c1 + c2) < 100);

prepare alplus as 'alter table t1 drop constraint C1_PLUS_C2';
alter table APP.t1 drop constraint APP.C1_PLUS_C2;
execute alplus;
remove alplus;

drop table t1;

-- bug 5817 - make LOGGED non-reserved keyword. following test cases for that
create table LOGGED(c11 int);
drop table LOGGED;
create table logged(logged int);
drop table logged;
declare global temporary table session.logged(logged int) on commit delete rows not logged;