WARNING 01J14: SQL authorization is being used without first enabling authentication. 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. -- -- 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); 0 rows inserted/updated/deleted ij> create table t0_1(c1 int); 0 rows inserted/updated/deleted ij> create table t0_2(c1 int); 0 rows inserted/updated/deleted ij> create table t0_3(c1 int); 0 rows inserted/updated/deleted ij> create table t1(c1 int); 0 rows inserted/updated/deleted ij> create table t1_1(c1 int); 0 rows inserted/updated/deleted ij> create table t2(c1 int); 0 rows inserted/updated/deleted ij> create table t3(c1 int); 0 rows inserted/updated/deleted ij> create table t4(c1 int not null); 0 rows inserted/updated/deleted ij> create view v1 as select * from t2; 0 rows inserted/updated/deleted ij> create view v2 as select c1 from t2; 0 rows inserted/updated/deleted ij> create index i0_1 on t0_1(c1); 0 rows inserted/updated/deleted ij> create index i0_2 on t0_2(c1); 0 rows inserted/updated/deleted ij> -- do some population insert into t1 values 1; 1 row inserted/updated/deleted ij> insert into t1_1 values 1; 1 row inserted/updated/deleted ij> insert into t2 values 1; 1 row inserted/updated/deleted ij> insert into t2 values 2; 1 row inserted/updated/deleted ij> insert into t3 values 1; 1 row inserted/updated/deleted ij> insert into t3 values 2; 1 row inserted/updated/deleted ij> insert into t3 values 3; 1 row inserted/updated/deleted ij> insert into t4 values 1, 2, 3, 1; 4 rows inserted/updated/deleted ij> autocommit off; ij> -- add column -- negative tests -- alter a non-existing table alter table notexists add column c1 int; ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'NOTEXISTS' because it does not exist. ij> -- add a column that already exists alter table t0 add column c1 int; ERROR X0Y32: Column 'C1' already exists in Table/View 'APP.T0'. ij> -- alter a system table alter table sys.systables add column c1 int; ERROR 42X62: 'ALTER TABLE' is not allowed in the 'SYS' schema. ij> -- alter table on a view alter table v2 add column c2 int; ERROR 42Y62: 'ALTER TABLE' is not allowed on 'APP.V2' because it is a view. ij> -- 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; ERROR X0Y58: Attempt to add a primary key constraint to table 'APP.T0' failed because the table already has a constraint of that type. A table can only have a single primary key constraint. ij> -- add a unique column constraint to a table with > 1 row alter table t3 add column c2 int not null default 0 unique; 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 'xxxxGENERATED-IDxxxx' defined on 'T3'. ij> -- cannot alter a table when there is an open cursor on it get cursor c1 as 'select * from t1'; ij> alter table t1 add column c2 int; ERROR X0X95: Operation 'ALTER TABLE' cannot be performed on object 'T1' because there is an open ResultSet dependent on that object. ij> close c1; ij> -- positive tests -- add a non-nullable column to a non-empty table alter table t1 add column c2 int not null default 0; 0 rows inserted/updated/deleted ij> -- add a primary key column to a non-empty table alter table t1 add column c3 int not null default 0 primary key; 0 rows inserted/updated/deleted ij> -- add a column with a check constraint to a non-empty column alter table t1 add column c4 int check(c4 = 1); 0 rows inserted/updated/deleted ij> select * from v1; C1 ----------- 1 2 ij> prepare p1 as 'select * from t2'; ij> execute p1; C1 ----------- 1 2 ij> alter table t2 add column c2 int; 0 rows inserted/updated/deleted ij> -- select * views don't see added columns after alter table select * from v1; C1 ----------- 1 2 ij> -- select * prepared statements do see added columns after alter table execute p1; C1 |C2 ----------------------- 1 |NULL 2 |NULL ij> -- rollback and recheck rollback; ij> select * from v1; C1 ----------- 1 2 ij> execute p1; C1 ----------- 1 2 ij> remove p1; ij> -- add non-nullable column to 0 row table and verify alter table t0 add column c2 int not null default 0; 0 rows inserted/updated/deleted ij> insert into t0 values (1, default); 1 row inserted/updated/deleted ij> select * from t0; C1 |C2 ----------------------- 1 |0 ij> drop table t0; 0 rows inserted/updated/deleted ij> rollback; ij> select * from t0; C1 ----------- ij> -- add primary key to 0 row table and verify alter table t0_1 add column c2 int not null primary key default 0; 0 rows inserted/updated/deleted ij> insert into t0_1 values (1, 1); 1 row inserted/updated/deleted ij> insert into t0_1 values (1, 1); 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 'xxxxGENERATED-IDxxxx' defined on 'T0_1'. ij> select * from t0_1; C1 |C2 ----------------------- 1 |1 ij> rollback; ij> -- add unique constraint to 0 and 1 row tables and verify alter table t0_1 add column c2 int not null unique default 0; 0 rows inserted/updated/deleted ij> insert into t0_1 values (1, default); 1 row inserted/updated/deleted ij> insert into t0_1 values (2, default); 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 'xxxxGENERATED-IDxxxx' defined on 'T0_1'. ij> insert into t0_1 values (3, 1); 1 row inserted/updated/deleted ij> delete from t1; 1 row inserted/updated/deleted ij> alter table t1 add column c2 int not null unique default 0; 0 rows inserted/updated/deleted ij> insert into t1 values (2, 2); 1 row inserted/updated/deleted ij> insert into t1 values (3, 1); 1 row inserted/updated/deleted ij> -- 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'; TABLENAME |2 -------------------------------------------------------------------------------------------------------------------------------------------- T0 |1 T0_1 |1 T0_2 |1 T0_3 |1 T1 |1 T1_1 |1 T2 |1 T3 |1 T4 |1 ij> rollback; ij> create function countopens() returns varchar(128) language java parameter style java external name 'org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker.countOpens'; 0 rows inserted/updated/deleted ij> commit; ij> -- do consistency check on scans, etc. values countopens(); 1 -------------------------------------------------------------------------------------------------------------------------------- No open scans, etc. ij> -- some typical data create table tab1 (c1 int, c2 int not null constraint tab1pk primary key, c3 double, c4 int); 0 rows inserted/updated/deleted ij> create index i11 on tab1 (c1); 0 rows inserted/updated/deleted ij> create unique index i12 on tab1 (c1); 0 rows inserted/updated/deleted ij> create index i13 on tab1 (c3, c1, c4); 0 rows inserted/updated/deleted ij> create unique index i14 on tab1 (c3, c1); 0 rows inserted/updated/deleted ij> insert into tab1 values (6, 5, 4.5, 90); 1 row inserted/updated/deleted ij> insert into tab1 values (10, 3, 8.9, -5); 1 row inserted/updated/deleted ij> insert into tab1 values (100, 15, 4.5, 9); 1 row inserted/updated/deleted ij> insert into tab1 values (2, 8, 4.4, 8); 1 row inserted/updated/deleted ij> insert into tab1 values (11, 9, 2.5, 88); 1 row inserted/updated/deleted ij> insert into tab1 values(null,10, 3.5, 99); 1 row inserted/updated/deleted ij> create view vw1 (col_sum, col_diff) as select c1+c4, c1-c4 from tab1; 0 rows inserted/updated/deleted ij> create view vw2 (c1) as select c3 from tab1; 0 rows inserted/updated/deleted ij> 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)); 0 rows inserted/updated/deleted ij> 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)); 0 rows inserted/updated/deleted ij> create view vw3 (c1, c2) as select c5, tab3.c4 from tab2, tab3 where tab3.c1 > 0; 0 rows inserted/updated/deleted ij> create view vw4 (c1) as select c4 from tab3 where c2 > 8; 0 rows inserted/updated/deleted ij> create table tab4 (c1 int, c2 int, c3 int, c4 int); 0 rows inserted/updated/deleted ij> create table tab5 (c1 int); 0 rows inserted/updated/deleted ij> insert into tab4 values (1,2,3,4); 1 row inserted/updated/deleted ij> create trigger tr1 after update of c2, c3, c4 on tab4 for each row insert into tab5 values (1); 0 rows inserted/updated/deleted ij> create trigger tr2 after update of c3, c4 on tab4 for each row insert into tab5 values (2); 0 rows inserted/updated/deleted ij> -- tr1 is dropped, tr2 still OK drop trigger tr1; 0 rows inserted/updated/deleted ij> select * from tab5; C1 ----------- ij> -- fire tr2 only update tab4 set c3 = 33; 1 row inserted/updated/deleted ij> update tab4 set c4 = 44; 1 row inserted/updated/deleted ij> select * from tab5; C1 ----------- 2 2 ij> -- drop tr2 drop trigger tr2; 0 rows inserted/updated/deleted ij> update tab4 set c4 = 444; 1 row inserted/updated/deleted ij> select * from tab2; C1 |C2 |C3 |C4 |C5 ---------------------------------------------------------------------- ij> drop view vw2; 0 rows inserted/updated/deleted ij> create view vw2 (c1) as select c3 from tab1; 0 rows inserted/updated/deleted ij> -- vw1 should be dropped drop view vw1; 0 rows inserted/updated/deleted ij> select * from vw1; ERROR 42X05: Table/View 'VW1' does not exist. ij> -- do the indexes still exist? -- the create index statements should fail create index i13 on tab1 (c3, c1, c4); 0 rows inserted/updated/deleted WARNING 01504: The new index is a duplicate of an existing index: I13. ij> create unique index i14 on tab1 (c3, c1); 0 rows inserted/updated/deleted WARNING 01504: The new index is a duplicate of an existing index: I14. ij> create unique index i12 on tab1 (c1); 0 rows inserted/updated/deleted WARNING 01504: The new index is a duplicate of an existing index: I12. ij> select c2, c3, c4 from tab1 order by c3; C2 |C3 |C4 ---------------------------------------------- 9 |2.5 |88 10 |3.5 |99 8 |4.4 |8 15 |4.5 |9 5 |4.5 |90 3 |8.9 |-5 ij> drop index i12; 0 rows inserted/updated/deleted ij> drop index i13; 0 rows inserted/updated/deleted ij> drop index i14; 0 rows inserted/updated/deleted ij> -- more data insert into tab1 (c2, c3, c4) values (22, 8.9, 5); 1 row inserted/updated/deleted ij> insert into tab1 (c2, c3, c4) values (11, 4.5, 67); 1 row inserted/updated/deleted ij> select c2 from tab1; C2 ----------- 3 5 8 9 10 11 15 22 ij> -- add a new column alter table tab1 add column c5 double; 0 rows inserted/updated/deleted ij> -- drop view vw2 so can create a new one, with where clause drop view vw2; 0 rows inserted/updated/deleted ij> create view vw2 (c1) as select c5 from tab1 where c2 > 5; 0 rows inserted/updated/deleted ij> -- drop vw2 as well drop view vw2; 0 rows inserted/updated/deleted ij> alter table tab1 drop constraint tab1pk; 0 rows inserted/updated/deleted ij> -- any surviving index? -- creating the index should not fail select c4 from tab1 order by 1; C4 ----------- -5 5 8 9 67 88 90 99 ij> create index i13 on tab1 (c3, c1, c4); 0 rows inserted/updated/deleted ij> -- should drop t2ck alter table tab2 drop constraint t2ck; 0 rows inserted/updated/deleted ij> -- this should drop t3fk, unique constraint and backing index alter table tab3 drop constraint t3fk; 0 rows inserted/updated/deleted ij> alter table tab2 drop constraint c4_PK; 0 rows inserted/updated/deleted ij> insert into tab3 values (1,2,3,4); 1 row inserted/updated/deleted ij> -- drop view vw3 drop view vw3; 0 rows inserted/updated/deleted ij> -- violates t3ck insert into tab3 (c1, c2, c3) values (81, 1, 2); 1 row inserted/updated/deleted ij> insert into tab3 (c1, c2, c3) values (81, 2, 2); 1 row inserted/updated/deleted ij> -- this should drop t3ck, vw4 alter table tab3 drop constraint t3ck; 0 rows inserted/updated/deleted ij> drop view vw4; 0 rows inserted/updated/deleted ij> insert into tab3 (c2, c3) values (-82, 9); 1 row inserted/updated/deleted ij> create view vw4 (c1) as select c3 from tab3 where c3+5>c4; 0 rows inserted/updated/deleted ij> -- drop view vw4 drop view vw4; 0 rows inserted/updated/deleted ij> rollback; ij> -- check that dropping a column will drop backing index on referencing -- table create table tt1(a int, b int not null constraint tt1uc unique); 0 rows inserted/updated/deleted ij> create table reftt1(a int constraint reftt1rc references tt1(b)); 0 rows inserted/updated/deleted ij> -- count should be 2 select count(*) from sys.sysconglomerates c, sys.systables t where t.tableid = c.tableid and t.tablename = 'REFTT1'; 1 ----------- 2 ij> alter table reftt1 drop constraint reftt1rc; 0 rows inserted/updated/deleted ij> alter table tt1 drop constraint tt1uc; 0 rows inserted/updated/deleted ij> -- count should be 1 select count(*) from sys.sysconglomerates c, sys.systables t where t.tableid = c.tableid and t.tablename = 'REFTT1'; 1 ----------- 1 ij> rollback; ij> -- add constraint -- negative tests -- add primary key to table which already has one alter table t0 add column c3 int; 0 rows inserted/updated/deleted ij> alter table t0 add constraint cons1 primary key(c3); ERROR 42831: 'C3' cannot be a column of a primary key or unique key because it can contain null values. ij> alter table t0 add primary key(c3); ERROR 42831: 'C3' cannot be a column of a primary key or unique key because it can contain null values. ij> -- add constraint references non-existant column alter table t4 add constraint t4pk primary key("c1"); ERROR 42X14: 'c1' is not a column in table or VTI 'T4'. ij> alter table t4 add constraint t4uq unique("c1"); ERROR 42X14: 'c1' is not a column in table or VTI 'T4'. ij> alter table t4 add constraint t4fk foreign key ("c1") references t0; ERROR 42X14: 'c1' is not a column in table or VTI 'T4'. ij> alter table t4 add constraint t4ck check ("c1" <> 4); ERROR 42X04: Column 'c1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'c1' is not a column in the target table. ij> -- add primary key to non-empty table with duplicates alter table t4 add primary key(c1); 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 'xxxxGENERATED-IDxxxx' defined on 'T4'. ij> -- positive tests -- 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; 0 rows inserted/updated/deleted ij> insert into t0_1 values (1, 1); 1 row inserted/updated/deleted ij> insert into t0_1 values (1, 1); 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 'P2' defined on 'T0_1'. ij> select * from t0_1; C1 |C2 ----------------------- 1 |1 ij> rollback; ij> -- add check constraint to 0 row table and verify alter table t0_1 add column c2 int check(c2 != 3); 0 rows inserted/updated/deleted ij> insert into t0_1 values (1, 1); 1 row inserted/updated/deleted ij> insert into t0_1 values (1, 3); ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated while performing an INSERT or UPDATE on table 'APP.T0_1'. ij> insert into t0_1 values (1, 1); 1 row inserted/updated/deleted ij> select * from t0_1; C1 |C2 ----------------------- 1 |1 1 |1 ij> rollback; ij> -- add check constraint to table with rows that are ok alter table t0_1 add column c2 int; 0 rows inserted/updated/deleted ij> insert into t0_1 values (1, 1); 1 row inserted/updated/deleted ij> insert into t0_1 values (2, 2); 1 row inserted/updated/deleted ij> alter table t0_1 add constraint ck1 check(c2 = c1); 0 rows inserted/updated/deleted ij> select * from t0_1; C1 |C2 ----------------------- 1 |1 2 |2 ij> -- verify constraint has been added, the following should fail insert into t0_1 values (1, 3); ERROR 23513: The check constraint 'CK1' was violated while performing an INSERT or UPDATE on table 'APP.T0_1'. ij> rollback; ij> -- add check constraint to table with rows w/ 3 failures alter table t0_1 add column c2 int; 0 rows inserted/updated/deleted ij> insert into t0_1 values (1, 1); 1 row inserted/updated/deleted ij> insert into t0_1 values (2, 2); 1 row inserted/updated/deleted ij> insert into t0_1 values (2, 2); 1 row inserted/updated/deleted ij> insert into t0_1 values (666, 2); 1 row inserted/updated/deleted ij> insert into t0_1 values (2, 2); 1 row inserted/updated/deleted ij> insert into t0_1 values (3, 3); 1 row inserted/updated/deleted ij> insert into t0_1 values (666, 3); 1 row inserted/updated/deleted ij> insert into t0_1 values (666, 3); 1 row inserted/updated/deleted ij> insert into t0_1 values (3, 3); 1 row inserted/updated/deleted ij> alter table t0_1 add constraint ck1 check(c2 = c1); ERROR X0Y59: Attempt to add or enable constraint(s) on table 'APP.T0_1' failed because the table contains 3 row(s) that violate the following check constraint(s): CK1. ij> -- verify constraint has NOT been added, the following should succeed insert into t0_1 values (1, 3); 1 row inserted/updated/deleted ij> select * from t0_1; C1 |C2 ----------------------- 1 |1 2 |2 2 |2 666 |2 2 |2 3 |3 666 |3 666 |3 3 |3 1 |3 ij> rollback; ij> -- 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; 0 rows inserted/updated/deleted ij> alter table t0_1 add check(c2 = c1); 0 rows inserted/updated/deleted ij> insert into t0_1 values (1, 1); 1 row inserted/updated/deleted ij> insert into t0_1 values (1, 2); ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated while performing an INSERT or UPDATE on table 'APP.T0_1'. ij> insert into t0_1 values (1, 1); 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 'P2' defined on 'T0_1'. ij> insert into t0_1 values (2, 1); ERROR 23513: The check constraint 'xxxxGENERATED-IDxxxx' was violated while performing an INSERT or UPDATE on table 'APP.T0_1'. ij> insert into t0_1 values (2, 2); 1 row inserted/updated/deleted ij> select * from t0_1; C1 |C2 ----------------------- 1 |1 2 |2 ij> rollback; ij> -- add primary key constraint to table with > 1 row alter table t3 add column c3 int; 0 rows inserted/updated/deleted ij> alter table t3 add unique(c3); ERROR 42831: 'C3' cannot be a column of a primary key or unique key because it can contain null values. ij> -- add unique constraint to 0 and 1 row tables and verify alter table t0_2 add column c2 int not null unique default 0; 0 rows inserted/updated/deleted ij> insert into t0_2 values (1, default); 1 row inserted/updated/deleted ij> insert into t0_2 values (1, 1); 1 row inserted/updated/deleted ij> delete from t1_1; 1 row inserted/updated/deleted ij> alter table t1_1 add column c2 int not null unique default 0; 0 rows inserted/updated/deleted ij> insert into t1_1 values (1, 2); 1 row inserted/updated/deleted ij> insert into t1_1 values (1, 2); 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 'xxxxGENERATED-IDxxxx' defined on 'T1_1'. ij> insert into t1_1 values (1, 1); 1 row inserted/updated/deleted ij> -- add unique constraint to table with > 1 row alter table t3 add unique(c1); ERROR 42831: 'C1' cannot be a column of a primary key or unique key because it can contain null values. ij> -- verify prepared alter table dependent on underlying table prepare p1 as 'alter table xxx add check(c2 = 1)'; ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'XXX' because it does not exist. ij> create table xxx(c1 int, c2 int); 0 rows inserted/updated/deleted ij> prepare p1 as 'alter table xxx add check(c2 = 1)'; ij> execute p1; 0 rows inserted/updated/deleted ij> drop table xxx; 0 rows inserted/updated/deleted ij> create table xxx(c1 int); 0 rows inserted/updated/deleted ij> execute p1; ERROR 42X04: Column 'C2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'C2' is not a column in the target table. ij> alter table xxx add column c2 int; 0 rows inserted/updated/deleted ij> execute p1; 0 rows inserted/updated/deleted ij> drop table xxx; 0 rows inserted/updated/deleted ij> -- 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'; TABLENAME |2 -------------------------------------------------------------------------------------------------------------------------------------------- T0 |1 T0_1 |1 T0_2 |1 T0_3 |1 T1 |1 T1_1 |1 T2 |1 T3 |1 T4 |1 ij> -- drop constraint -- negative tests -- drop non-existent constraint alter table t0 drop constraint notexists; ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.NOTEXISTS' on table 'APP.T0'. ij> -- constraint/table mismatch alter table t1 drop constraint p1; ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.P1' on table 'APP.T1'. ij> -- In DB2 compatibility mode, we cann't add a nullable primary key alter table t0_1 add constraint p2 primary key(c1); ERROR 42831: 'C1' cannot be a column of a primary key or unique key because it can contain null values. ij> alter table t0_1 drop constraint p2; ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.P2' on table 'APP.T0_1'. ij> -- 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; 0 rows inserted/updated/deleted ij> delete from t0_1; 0 rows inserted/updated/deleted ij> alter table t0_1 drop constraint p2; 0 rows inserted/updated/deleted ij> alter table t0_1 add constraint p2 primary key(c2); 0 rows inserted/updated/deleted ij> alter table t0_1 drop constraint p2; 0 rows inserted/updated/deleted ij> alter table t0_1 add constraint p2 primary key(c2); 0 rows inserted/updated/deleted ij> -- verify that constraint is still enforced insert into t0_1 values (1,1); 1 row inserted/updated/deleted ij> insert into t0_1 values (1,1); 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 'P2' defined on 'T0_1'. ij> -- 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'; TABLENAME |2 -------------------------------------------------------------------------------------------------------------------------------------------- T0_1 |1 ij> -- verify that alter table works after drop/recreate of table prepare p1 as 'alter table t0_1 drop constraint p2'; ij> execute p1; 0 rows inserted/updated/deleted ij> drop table t0_1; 0 rows inserted/updated/deleted ij> create table t0_1 (c1 int, c2 int not null constraint p2 primary key); 0 rows inserted/updated/deleted ij> execute p1; 0 rows inserted/updated/deleted ij> -- 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'; TABLENAME |2 -------------------------------------------------------------------------------------------------------------------------------------------- SYSCONGLOMERATES |1 SYSTABLES |1 SYSCOLUMNS |1 SYSSCHEMAS |1 SYSCONSTRAINTS |1 SYSKEYS |1 SYSDEPENDS |1 SYSALIASES |1 SYSVIEWS |1 SYSCHECKS |1 SYSFOREIGNKEYS |1 SYSSTATEMENTS |1 SYSFILES |1 SYSTRIGGERS |1 SYSSTATISTICS |1 SYSTABLEPERMS |1 SYSCOLPERMS |1 SYSROUTINEPERMS |1 ij> -- 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'; TABLENAME |2 -------------------------------------------------------------------------------------------------------------------------------------------- T0 |1 T0_2 |1 T0_3 |1 T1 |1 T1_1 |1 T2 |1 T3 |1 T4 |1 T0_1 |1 ij> -- bugs 793 create table b793 (pn1 int not null constraint named_primary primary key, pn2 int constraint named_pn2 check (pn2 > 3)); 0 rows inserted/updated/deleted ij> alter table b793 drop constraint named_primary; 0 rows inserted/updated/deleted ij> drop table b793; 0 rows inserted/updated/deleted ij> -- test that drop constraint removes backing indexes drop table t1; 0 rows inserted/updated/deleted ij> create table t1(a int not null constraint t1_pri primary key); 0 rows inserted/updated/deleted ij> create table reft1(a int constraint t1_ref references t1(a)); 0 rows inserted/updated/deleted ij> -- count should be 2 select count(*) from sys.sysconglomerates c, sys.systables t where c.tableid = t.tableid and t.tablename = 'REFT1'; 1 ----------- 2 ij> alter table reft1 drop constraint t1_ref; 0 rows inserted/updated/deleted ij> alter table t1 drop constraint t1_pri; 0 rows inserted/updated/deleted ij> -- count should be 1 select count(*) from sys.sysconglomerates c, sys.systables t where c.tableid = t.tableid and t.tablename = 'REFT1'; 1 ----------- 1 ij> drop table reft1; 0 rows inserted/updated/deleted ij> -- clean up drop view v2; 0 rows inserted/updated/deleted ij> drop view v1; 0 rows inserted/updated/deleted ij> drop table t0; 0 rows inserted/updated/deleted ij> drop table t0_1; 0 rows inserted/updated/deleted ij> drop table t0_2; 0 rows inserted/updated/deleted ij> drop table t0_3; 0 rows inserted/updated/deleted ij> drop table t1; 0 rows inserted/updated/deleted ij> drop table t1_1; 0 rows inserted/updated/deleted ij> drop table t3; 0 rows inserted/updated/deleted ij> drop table t4; 0 rows inserted/updated/deleted ij> ------------------------------------------------------ -- -- special funky schema tests -- ------------------------------------------------------ create schema newschema; 0 rows inserted/updated/deleted ij> drop table x; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'X' because it does not exist. ij> create table x (x int not null, y int not null); 0 rows inserted/updated/deleted ij> alter table x add constraint newcons primary key (x); 0 rows inserted/updated/deleted ij> -- schemaname should be app select schemaname, constraintname from sys.sysconstraints c, sys.sysschemas s where s.schemaid = c.schemaid order by 1; SCHEMANAME |CONSTRAINTNAME ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- APP |NEWCONS ij> insert into x values (1,1),(1,1); 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 'NEWCONS' defined on 'X'. ij> alter table x drop constraint app.newcons; 0 rows inserted/updated/deleted ij> alter table x add constraint newcons primary key (x); 0 rows inserted/updated/deleted ij> -- schemaname should be app select schemaname, constraintname from sys.sysconstraints c, sys.sysschemas s where s.schemaid = c.schemaid order by 1; SCHEMANAME |CONSTRAINTNAME ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- APP |NEWCONS ij> -- fail alter table x drop constraint badschema.newcons; ERROR 42Y07: Schema 'BADSCHEMA' does not exist ij> -- fail alter table x drop constraint newschema.newcons; ERROR 42X86: ALTER TABLE failed. There is no constraint 'NEWSCHEMA.NEWCONS' on table 'APP.X'. ij> -- ok alter table x drop constraint app.newcons; 0 rows inserted/updated/deleted ij> -- bad schema name alter table x add constraint badschema.newcons primary key (x); ERROR 42X85: Constraint 'BADSCHEMA.NEWCONS'is required to be in the same schema as table 'X'. ij> -- two constriants, same name, different schema (second will fail) drop table x; 0 rows inserted/updated/deleted ij> create table x (x int not null, y int not null); 0 rows inserted/updated/deleted ij> alter table x add constraint con check (x > 1); 0 rows inserted/updated/deleted ij> alter table x add constraint newschema.con check (x > 1); ERROR 42X85: Constraint 'NEWSCHEMA.CON'is required to be in the same schema as table 'X'. ij> select schemaname, constraintname from sys.sysconstraints c, sys.sysschemas s where s.schemaid = c.schemaid order by 1; SCHEMANAME |CONSTRAINTNAME ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- APP |CON ij> create schema emptyschema; 0 rows inserted/updated/deleted ij> set schema emptyschema; 0 rows inserted/updated/deleted ij> -- fail, cannot find emptyschema.conn alter table app.x drop constraint emptyschema.con; ERROR 42X86: ALTER TABLE failed. There is no constraint 'EMPTYSCHEMA.CON' on table 'APP.X'. ij> select schemaname, constraintname from sys.sysconstraints c, sys.sysschemas s where s.schemaid = c.schemaid order by 1; SCHEMANAME |CONSTRAINTNAME ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- APP |CON ij> set schema newschema; 0 rows inserted/updated/deleted ij> -- add constraint, default to table schema alter table app.x add constraint con2 check (x > 1); 0 rows inserted/updated/deleted ij> -- 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; SCHEMANAME |CONSTRAINTNAME ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- APP |CON APP |CON2 ij> drop table app.x; 0 rows inserted/updated/deleted ij> drop schema newschema restrict; 0 rows inserted/updated/deleted ij> -- some temporary table tests -- declare temp table with no explicit on commit behavior. declare global temporary table session.t1 (c11 int) not logged; 0 rows inserted/updated/deleted ij> declare global temporary table session.t2 (c21 int) on commit delete rows not logged; 0 rows inserted/updated/deleted ij> declare global temporary table session.t3 (c31 int) on commit preserve rows not logged; 0 rows inserted/updated/deleted ij> drop table session.t1; 0 rows inserted/updated/deleted ij> drop table session.t2; 0 rows inserted/updated/deleted ij> drop table session.t3; 0 rows inserted/updated/deleted ij> drop table t1; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T1' because it does not exist. ij> create table t1(c1 int, c2 int not null primary key); 0 rows inserted/updated/deleted ij> insert into t1 values (1, 1); 1 row inserted/updated/deleted ij> insert into t1 values (1, 1); 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 'xxxxGENERATED-IDxxxx' defined on 'T1'. ij> alter table t1 drop primary key; 0 rows inserted/updated/deleted ij> insert into t1 values (1, 1); 1 row inserted/updated/deleted ij> select * from t1; C1 |C2 ----------------------- 1 |1 1 |1 ij> alter table t1 drop primary key; ERROR 42X86: ALTER TABLE failed. There is no constraint 'PRIMARY KEY' on table 'APP.T1'. ij> alter table t1 drop constraint emptyschema.C1; ERROR 42X86: ALTER TABLE failed. There is no constraint 'EMPTYSCHEMA.C1' on table 'APP.T1'. ij> alter table t1 drop constraint nosuchschema.C2; ERROR 42Y07: Schema 'NOSUCHSCHEMA' does not exist ij> alter table t1 add constraint emptyschema.C1_PLUS_C2 check ((c1 + c2) < 100); ERROR 42X85: Constraint 'EMPTYSCHEMA.C1_PLUS_C2'is required to be in the same schema as table 'T1'. ij> alter table t1 add constraint C1_PLUS_C2 check ((c1 + c2) < 100); 0 rows inserted/updated/deleted ij> prepare alplus as 'alter table t1 drop constraint C1_PLUS_C2'; ij> alter table APP.t1 drop constraint APP.C1_PLUS_C2; 0 rows inserted/updated/deleted ij> execute alplus; ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.C1_PLUS_C2' on table 'APP.T1'. ij> remove alplus; ij> drop table t1; 0 rows inserted/updated/deleted ij> -- bug 5817 - make LOGGED non-reserved keyword. following test cases for that create table LOGGED(c11 int); 0 rows inserted/updated/deleted ij> drop table LOGGED; 0 rows inserted/updated/deleted ij> create table logged(logged int); 0 rows inserted/updated/deleted ij> drop table logged; 0 rows inserted/updated/deleted ij> declare global temporary table session.logged(logged int) on commit delete rows not logged; 0 rows inserted/updated/deleted ij> -- tests for ALTER TABLE ALTER COLUMN [NOT] NULL create table atmcn_1 (a integer, b integer not null); 0 rows inserted/updated/deleted ij> -- should fail because b cannot be null insert into atmcn_1 (a) values (1); ERROR 23502: Column 'B' cannot accept a NULL value. ij> insert into atmcn_1 values (1,1); 1 row inserted/updated/deleted ij> select * from atmcn_1; A |B ----------------------- 1 |1 ij> alter table atmcn_1 alter column a not null; 0 rows inserted/updated/deleted ij> -- should fail because a cannot be null insert into atmcn_1 (b) values (2); ERROR 23502: Column 'A' cannot accept a NULL value. ij> insert into atmcn_1 values (2,2); 1 row inserted/updated/deleted ij> select * from atmcn_1; A |B ----------------------- 1 |1 2 |2 ij> alter table atmcn_1 alter column b null; 0 rows inserted/updated/deleted ij> insert into atmcn_1 (a) values (1); 1 row inserted/updated/deleted ij> select * from atmcn_1; A |B ----------------------- 1 |1 2 |2 1 |NULL ij> -- Now that B has a null value, trying to modify it to NOT NULL should fail alter table atmcn_1 alter column b not null; ERROR X0Y80: ALTER table 'APP.ATMCN_1' failed. Null data found in column 'B'. ij> -- show that a column which is part of the PRIMARY KEY cannot be modified NULL create table atmcn_2 (a integer not null primary key, b integer not null); 0 rows inserted/updated/deleted ij> alter table atmcn_2 alter column a null; ERROR 42Z20: Column 'A' cannot be made nullable. It is part of a primary key or unique constraint, which cannot have any nullable columns. ij> create table atmcn_3 (a integer not null, b integer not null); 0 rows inserted/updated/deleted ij> alter table atmcn_3 add constraint atmcn_3_pk primary key(a, b); 0 rows inserted/updated/deleted ij> alter table atmcn_3 alter column b null; ERROR 42Z20: Column 'B' cannot be made nullable. It is part of a primary key or unique constraint, which cannot have any nullable columns. ij> -- verify that the keyword "column" in the ALTER TABLE ... ALTER COLUMN ... -- statement is optional: create table atmcn_4 (a integer not null, b integer); 0 rows inserted/updated/deleted ij> alter table atmcn_4 alter a null; 0 rows inserted/updated/deleted ij> -- show that a column which has a UNIQUE constraint cannot be modified NULL: create table atmcn_5 (a integer not null, b integer not null unique); 0 rows inserted/updated/deleted ij> alter table atmcn_5 alter column b null; ERROR 42Z20: Column 'B' cannot be made nullable. It is part of a primary key or unique constraint, which cannot have any nullable columns. ij> -- tests for ALTER TABLE ALTER COLUMN DEFAULT create table atmod_1 (a integer, b varchar(10)); 0 rows inserted/updated/deleted ij> insert into atmod_1 values (1, 'one'); 1 row inserted/updated/deleted ij> alter table atmod_1 alter column a default -1; 0 rows inserted/updated/deleted ij> insert into atmod_1 values (default, 'minus one'); 1 row inserted/updated/deleted ij> insert into atmod_1 (b) values ('b'); 1 row inserted/updated/deleted ij> select * from atmod_1; A |B ---------------------- 1 |one -1 |minus one -1 |b ij> alter table atmod_1 alter a default 42; 0 rows inserted/updated/deleted ij> insert into atmod_1 values(3, 'three'); 1 row inserted/updated/deleted ij> insert into atmod_1 values (default, 'forty two'); 1 row inserted/updated/deleted ij> select * from atmod_1; A |B ---------------------- 1 |one -1 |minus one -1 |b 3 |three 42 |forty two ij> -- Tests for renaming a column. These tests are in altertable.sql because -- renaming a column is closely linked, conseptually, to other table -- alterations. However, the actual syntax is: -- RENAME COLUMN t.c1 TO c2 create table renc_1 (a int, b varchar(10), c timestamp, d double); 0 rows inserted/updated/deleted ij> -- table doesn't exist, should fail: rename column renc_no_such.a to b; ERROR 42Y55: 'RENAME COLUMN' cannot be performed on 'RENC_NO_SUCH' because it does not exist. ij> -- table exists, but column doesn't exist rename column renc_1.no_such to e; ERROR 42X14: 'NO_SUCH' is not a column in table or VTI 'RENC_1'. ij> -- new column name already exists in table: rename column renc_1.a to c; ERROR X0Y32: Column 'C' already exists in Table/View 'RENC_1'. ij> -- can't rename a column to itself: rename column renc_1.b to b; ERROR X0Y32: Column 'B' already exists in Table/View 'RENC_1'. ij> -- new column name is a reserved word: rename column renc_1.a to select; ERROR 42X01: Syntax error: Encountered "select" at line 2, column 27. ij> -- attempt to rename a column in a system table. Should fali: rename column sys.sysconglomerates.isindex to is_an_index; ERROR 42X62: 'RENAME COLUMN' is not allowed in the 'SYS' schema. ij> -- attempt to rename a column in a view, should fail: create view renc_vw_1 (v1, v2) as select b, d from renc_1; 0 rows inserted/updated/deleted ij> rename column renc_vw_1.v2 to v3; ERROR 42Y62: 'RENAME COLUMN' is not allowed on 'APP.RENC_VW_1' because it is a view. ij> describe renc_vw_1; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ V1 |VARCHAR |NULL|NULL|10 |NULL |20 |YES V2 |DOUBLE |NULL|2 |52 |NULL |NULL |YES ij> -- attempt to rename a column in an index, should fail: create index renc_idx_1 on renc_1 (c, d); 0 rows inserted/updated/deleted ij> show indexes from renc_1; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- RENC_1 |C |true |3 |A |NULL |NULL RENC_1 |D |true |3 |A |NULL |NULL ij> rename column renc_idx_1.d to d_new; ERROR 42Y55: 'RENAME COLUMN' cannot be performed on 'RENC_IDX_1' because it does not exist. ij> show indexes from renc_1; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- RENC_1 |C |true |3 |A |NULL |NULL RENC_1 |D |true |3 |A |NULL |NULL ij> -- A few syntax errors in the statement, to check for reasonable messages: rename column renc_1 to b; ERROR 42Y55: 'RENAME COLUMN' cannot be performed on 'RENC_1' because it does not exist. ij> rename column renc_1 rename a to b; ERROR 42X01: Syntax error: Encountered "rename" at line 1, column 22. ij> rename column renc_1.a; ERROR 42X01: Syntax error: Encountered "" at line 1, column 22. ij> rename column renc_1.a b; ERROR 42X01: Syntax error: Encountered "b" at line 1, column 24. ij> rename column renc_1.a to; ERROR 42X01: Syntax error: Encountered "" at line 1, column 25. ij> rename column renc_1.a to b, c; ERROR 42X01: Syntax error: Encountered "," at line 1, column 28. ij> rename column renc_1.a to b and c to d; ERROR 42X01: Syntax error: Encountered "and" at line 1, column 29. ij> -- Rename a column which is the primary key of the table: create table renc_2(c1 int not null constraint renc_2_p1 primary key); 0 rows inserted/updated/deleted ij> rename column renc_2.c1 to c2; 0 rows inserted/updated/deleted ij> describe renc_2; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ C2 |INTEGER |0 |10 |10 |NULL |NULL |NO ij> show indexes from renc_2; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- RENC_2 |C2 |false |3 |A |NULL |NULL ij> select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where t.tableid = c.tableid and t.tablename = 'RENC_2'; CONSTRAINTNAME |& ---------------------------------------------------------------------------------------------------------------------------------- RENC_2_P1 |P ij> create table renc_3 (a integer not null, b integer not null, c int, constraint renc_3_pk primary key(a, b)); 0 rows inserted/updated/deleted ij> rename column renc_3.b to newbie; 0 rows inserted/updated/deleted ij> describe renc_3; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ A |INTEGER |0 |10 |10 |NULL |NULL |NO NEWBIE |INTEGER |0 |10 |10 |NULL |NULL |NO C |INTEGER |0 |10 |10 |NULL |NULL |YES ij> show indexes from renc_3; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- RENC_3 |A |false |3 |A |NULL |NULL RENC_3 |NEWBIE |false |3 |A |NULL |NULL ij> select c.constraintname, c.type from sys.sysconstraints c, sys.systables t where t.tableid = c.tableid and t.tablename = 'RENC_3'; CONSTRAINTNAME |& ---------------------------------------------------------------------------------------------------------------------------------- RENC_3_PK |P ij> create table renc_4 (c1 int not null unique, c2 double, c3 int, c4 int not null constraint renc_4_c4_PK primary key, c5 int, c6 int, constraint renc_4_t2ck check (c2+c3<100.0)); 0 rows inserted/updated/deleted ij> create table renc_5 (c1 int, c2 int, c3 int, c4 int, c5 int not null, c6 int, constraint renc_5_t3fk foreign key (c2) references renc_4(c4), constraint renc_5_unq unique(c5), constraint renc_5_t3ck check (c2-c3<80)); 0 rows inserted/updated/deleted ij> -- Attempt to rename a column referenced by a foreign key constraint -- should fail: rename column renc_4.c4 to another_c4; ERROR X0Y25: Operation 'RENAME' cannot be performed on object 'RENC_4_C4_PK' because CONSTRAINT 'RENC_5_T3FK' is dependent on that object. ij> -- Rename a column with a unique constraint should work: rename column renc_4.c1 to unq_c1; 0 rows inserted/updated/deleted ij> rename column renc_5.c5 to unq_c5; 0 rows inserted/updated/deleted ij> show indexes from renc_4; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- RENC_4 |UNQ_C1 |false |3 |A |NULL |NULL RENC_4 |C4 |false |3 |A |NULL |NULL ij> show indexes from renc_5; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- RENC_5 |UNQ_C5 |false |3 |A |NULL |NULL RENC_5 |C2 |true |3 |A |NULL |NULL ij> -- Attempt to rename a column used in a check constraint should fail: rename column renc_4.c2 to some_other_name; ERROR 42Z97: Renaming column 'C2' will cause check constraint 'RENC_4_T2CK' to break. ij> -- Attempt to rename a column used in a trigger should fail: create trigger renc_5_tr1 after update of c2, c3, c6 on renc_4 for each row mode db2sql insert into renc_5 (c6) values (1); 0 rows inserted/updated/deleted ij> -- This fails, because the tigger is dependent on it: rename column renc_4.c6 to some_name; ERROR X0Y25: Operation 'RENAME' cannot be performed on object 'RENC_4(C6)' because TRIGGER 'RENC_5_TR1' is dependent on that object. ij> -- This succeeds, because the trigger is not dependent on renc_5.c6. -- DERBY-2041 requests that triggers should be marked as dependent on -- tables and columns in their body. If that improvement is made, this -- test will need to be changed, as the next rename would fail, and the -- insert after it would then succeed. rename column renc_5.c6 to new_name; 0 rows inserted/updated/deleted ij> -- The update statement will fail, because column c6 no longer exists. -- See DERBY-2041 for a discussion of this topic. insert into renc_4 values(1, 2, 3, 4, 5, 6); 1 row inserted/updated/deleted ij> update renc_4 set c6 = 92; ERROR 42X14: 'C6' is not a column in table or VTI 'APP.RENC_5'. ij> select * from renc_5; C1 |C2 |C3 |C4 |UNQ_C5 |NEW_NAME ----------------------------------------------------------------------- ij> -- Rename a column which has a granted privilege, show that the grant is -- properly processed and now applies to the new column: create table renc_6 (a int, b int, c int); 0 rows inserted/updated/deleted ij> grant select (a, b) on renc_6 to bryan; 0 rows inserted/updated/deleted ij> select p.grantee,p.type, p.columns from sys.syscolperms p, sys.systables t where t.tableid=p.tableid and t.tablename='RENC_6'; GRANTEE |&|COLUMNS -------------------------------------------------------------------------------------------------------------------------------------------------- BRYAN |s|{0, 1} ij> rename column renc_6.b to bb_gun; 0 rows inserted/updated/deleted ij> select p.grantee,p.type, p.columns from sys.syscolperms p, sys.systables t where t.tableid=p.tableid and t.tablename='RENC_6'; GRANTEE |&|COLUMNS -------------------------------------------------------------------------------------------------------------------------------------------------- BRYAN |s|{0, 1} ij> -- Attempt to rename a column should fail when there is an open cursor on it: get cursor renc_c1 as 'select * from renc_6'; ij> rename column renc_6.bb_gun to water_pistol; ERROR X0X95: Operation 'RENAME' cannot be performed on object 'RENC_6(BB_GUN)' because there is an open ResultSet dependent on that object. ij> close renc_c1; ij> -- Attempt to rename a column when there is an open prepared statement on it. -- The rename of the column will be successful; the open statement will get -- errors when it tries to re-execute. autocommit off; ij> prepare renc_p1 as 'select * from renc_6 where a = ?'; ij> execute renc_p1 using 'values (30)'; A |BB_GUN |C ----------------------------------- ij> rename column renc_6.a to abcdef; 0 rows inserted/updated/deleted ij> execute renc_p1 using 'values (30)'; ERROR 42X04: Column 'A' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'A' is not a column in the target table. ij> autocommit on; ij> -- Demonstrate that you cannot rename a column in a synonym, and demonstrate -- that renaming a column in the underlying table correctly renames it -- in the synonym too create table renc_7 (c1 varchar(50), c2 int); 0 rows inserted/updated/deleted ij> create synonym renc_7_syn for renc_7; 0 rows inserted/updated/deleted ij> insert into renc_7 values ('one', 1); 1 row inserted/updated/deleted ij> rename column renc_7_syn.c2 to c2_syn; ERROR 42Y55: 'RENAME COLUMN' cannot be performed on 'RENC_7_SYN' because it does not exist. ij> describe renc_7; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ C1 |VARCHAR |NULL|NULL|50 |NULL |100 |YES C2 |INTEGER |0 |10 |10 |NULL |NULL |YES ij> rename column renc_7.c1 to c1_renamed; 0 rows inserted/updated/deleted ij> select c1_renamed from renc_7_syn; C1_RENAMED -------------------------------------------------- one ij> -- demonstrate that you can rename a column in a table in a different schema create schema renc_schema_1; 0 rows inserted/updated/deleted ij> create schema renc_schema_2; 0 rows inserted/updated/deleted ij> set schema renc_schema_2; 0 rows inserted/updated/deleted ij> create table renc_8 (a int, b int, c int); 0 rows inserted/updated/deleted ij> set schema renc_schema_1; 0 rows inserted/updated/deleted ij> -- This should fail, as there is no table renc_8 in schema 1: rename column renc_8.b to bbb; ERROR 42Y55: 'RENAME COLUMN' cannot be performed on 'RENC_8' because it does not exist. ij> -- But this should work, and should find the table in the other schema rename column renc_schema_2.renc_8.b to b2; 0 rows inserted/updated/deleted ij> describe renc_schema_2.renc_8; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ A |INTEGER |0 |10 |10 |NULL |NULL |YES B2 |INTEGER |0 |10 |10 |NULL |NULL |YES C |INTEGER |0 |10 |10 |NULL |NULL |YES ij> -- alter table tests for ALTER TABLE DROP COLUMN. -- The overall syntax is: -- ALTER TABLE tablename DROP [ COLUMN ] columnname [ CASCADE | RESTRICT ] -- create table atdc_0 (a integer); 0 rows inserted/updated/deleted ij> create table atdc_1 (a integer, b integer); 0 rows inserted/updated/deleted ij> insert into atdc_1 values (1, 1); 1 row inserted/updated/deleted ij> select * from atdc_1; A |B ----------------------- 1 |1 ij> select columnname,columnnumber,columndatatype from sys.syscolumns where referenceid in (select tableid from sys.systables where tablename = 'ATDC_1'); COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE ------------------------------------------------------------------------------------------------------------------------------------------------------------ A |1 |INTEGER B |2 |INTEGER ij> alter table atdc_1 drop column b; 0 rows inserted/updated/deleted ij> select * from atdc_1; A ----------- 1 ij> select columnname,columnnumber,columndatatype from sys.syscolumns where referenceid in (select tableid from sys.systables where tablename = 'ATDC_1'); COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE ------------------------------------------------------------------------------------------------------------------------------------------------------------ A |1 |INTEGER ij> alter table atdc_1 add column b varchar (20); 0 rows inserted/updated/deleted ij> insert into atdc_1 values (1, 'new val'); 1 row inserted/updated/deleted ij> insert into atdc_1 (a, b) values (2, 'two val'); 1 row inserted/updated/deleted ij> select * from atdc_1; A |B -------------------------------- 1 |NULL 1 |new val 2 |two val ij> select columnname,columnnumber,columndatatype from sys.syscolumns where referenceid in (select tableid from sys.systables where tablename = 'ATDC_1'); COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE ------------------------------------------------------------------------------------------------------------------------------------------------------------ A |1 |INTEGER B |2 |VARCHAR(20) ij> alter table atdc_1 add column c integer; 0 rows inserted/updated/deleted ij> insert into atdc_1 values (3, null, 3); 1 row inserted/updated/deleted ij> select * from atdc_1; A |B |C -------------------------------------------- 1 |NULL |NULL 1 |new val |NULL 2 |two val |NULL 3 |NULL |3 ij> alter table atdc_1 drop b; 0 rows inserted/updated/deleted ij> select * from atdc_1; A |C ----------------------- 1 |NULL 1 |NULL 2 |NULL 3 |3 ij> select columnname,columnnumber,columndatatype from sys.syscolumns where referenceid in (select tableid from sys.systables where tablename = 'ATDC_1'); COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE ------------------------------------------------------------------------------------------------------------------------------------------------------------ A |1 |INTEGER C |2 |INTEGER ij> -- Demonstrate that we can drop a column which is the primary key. Also -- demonstrate that when we drop a column which is the primary key, that -- cascade processing will drop the corresponding foreign key constraint create table atdc_1_01 (a int, b int, c int not null primary key); 0 rows inserted/updated/deleted ij> alter table atdc_1_01 drop column c cascade; 0 rows inserted/updated/deleted WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_01 has been dropped. ij> create table atdc_1_02 (a int not null primary key, b int); 0 rows inserted/updated/deleted ij> create table atdc_1_03 (a03 int, constraint a03_fk foreign key (a03) references atdc_1_02(a)); 0 rows inserted/updated/deleted ij> alter table atdc_1_02 drop column a cascade; 0 rows inserted/updated/deleted WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_02 has been dropped. WARNING 01500: The constraint A03_FK on table ATDC_1_03 has been dropped. ij> -- drop column restrict should fail because column is used in a constraint: alter table atdc_1 add constraint atdc_constraint_1 check (a > 0); 0 rows inserted/updated/deleted ij> select * from sys.sysconstraints where tableid in (select tableid from sys.systables where tablename = 'ATDC_1'); CONSTRAINTID |TABLEID |CONSTRAINTNAME |&|SCHEMAID |&|REFERENCEC& --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|ATDC_CONSTRAINT_1 |C|xxxxFILTERED-UUIDxxxx|E|0 ij> select sc.* from sys.syschecks sc,sys.sysconstraints con, sys.systables st where sc.constraintid = con.constraintid and con.tableid = st.tableid and st.tablename = 'ATDC_1'; CONSTRAINTID |CHECKDEFINITION |REFERENCEDCOLU& ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- xxxxFILTERED-UUIDxxxx|(a > 0) |(1) ij> alter table atdc_1 drop column a restrict; ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'A' because CONSTRAINT 'ATDC_CONSTRAINT_1' is dependent on that object. ij> -- drop column cascade should also drop the check constraint: alter table atdc_1 drop column a cascade; 0 rows inserted/updated/deleted WARNING 01500: The constraint ATDC_CONSTRAINT_1 on table ATDC_1 has been dropped. ij> select * from sys.sysconstraints where tableid in (select tableid from sys.systables where tablename = 'ATDC_1'); CONSTRAINTID |TABLEID |CONSTRAINTNAME |&|SCHEMAID |&|REFERENCEC& --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ij> -- Verify the behavior of the various constraint types: -- check, primary key, foreign key, unique, not null create table atdc_1_constraints (a int not null primary key, b int not null, c int constraint atdc_1_c_chk check (c is not null), d int not null unique, e int, f int, constraint atdc_1_e_fk foreign key (e) references atdc_1_constraints(a)); 0 rows inserted/updated/deleted ij> -- In restrict mode, none of the columns a, c, d, or e should be droppable, -- but in cascade mode each of them should be droppable, and at the end -- we should have only column f -- column b is droppable because an unnamed NOT NULL constraint doesn't -- prevent DROP COLUMN, only an explicit CHECK constraint does. describe atdc_1_constraints; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ A |INTEGER |0 |10 |10 |NULL |NULL |NO B |INTEGER |0 |10 |10 |NULL |NULL |NO C |INTEGER |0 |10 |10 |NULL |NULL |YES D |INTEGER |0 |10 |10 |NULL |NULL |NO E |INTEGER |0 |10 |10 |NULL |NULL |YES F |INTEGER |0 |10 |10 |NULL |NULL |YES ij> alter table atdc_1_constraints drop column a restrict; ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'A' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object. ij> alter table atdc_1_constraints drop column b restrict; 0 rows inserted/updated/deleted ij> alter table atdc_1_constraints drop column c restrict; ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'C' because CONSTRAINT 'ATDC_1_C_CHK' is dependent on that object. ij> alter table atdc_1_constraints drop column d restrict; ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'D' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object. ij> alter table atdc_1_constraints drop column e restrict; ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'E' because CONSTRAINT 'ATDC_1_E_FK' is dependent on that object. ij> describe atdc_1_constraints; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ A |INTEGER |0 |10 |10 |NULL |NULL |NO C |INTEGER |0 |10 |10 |NULL |NULL |YES D |INTEGER |0 |10 |10 |NULL |NULL |NO E |INTEGER |0 |10 |10 |NULL |NULL |YES F |INTEGER |0 |10 |10 |NULL |NULL |YES ij> alter table atdc_1_constraints drop column a cascade; 0 rows inserted/updated/deleted WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_CONSTRAINTS has been dropped. WARNING 01500: The constraint ATDC_1_E_FK on table ATDC_1_CONSTRAINTS has been dropped. ij> alter table atdc_1_constraints drop column c cascade; 0 rows inserted/updated/deleted WARNING 01500: The constraint ATDC_1_C_CHK on table ATDC_1_CONSTRAINTS has been dropped. ij> alter table atdc_1_constraints drop column d cascade; 0 rows inserted/updated/deleted WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_1_CONSTRAINTS has been dropped. ij> alter table atdc_1_constraints drop column e cascade; 0 rows inserted/updated/deleted ij> describe atdc_1_constraints; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ F |INTEGER |0 |10 |10 |NULL |NULL |YES ij> -- Some negative testing of ALTER TABLE DROP COLUMN -- Table does not exist: alter table atdc_nosuch drop column a; ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'ATDC_NOSUCH' because it does not exist. ij> -- Table exists, but column does not exist: create table atdc_2 (a integer); 0 rows inserted/updated/deleted ij> alter table atdc_2 drop column b; ERROR 42X14: 'B' is not a column in table or VTI 'ATDC_2'. ij> alter table atdc_2 drop b; ERROR 42X14: 'B' is not a column in table or VTI 'ATDC_2'. ij> -- Column name is spelled incorrectly (wrong case) alter table atdc_2 drop column 'a'; ERROR 42X01: Syntax error: Encountered "\'a\'" at line 2, column 32. ij> -- Some special reserved words to cause parser errors alter table atdc_2 drop column column; ERROR 42X01: Syntax error: Encountered "column" at line 2, column 32. ij> alter table atdc_2 drop column; ERROR 42X01: Syntax error: Encountered "" at line 1, column 30. ij> alter table atdc_2 drop column constraint; ERROR 42X01: Syntax error: Encountered "constraint" at line 1, column 32. ij> alter table atdc_2 drop column primary; ERROR 42X01: Syntax error: Encountered "primary" at line 1, column 32. ij> alter table atdc_2 drop column foreign; ERROR 42X01: Syntax error: Encountered "foreign" at line 1, column 32. ij> alter table atdc_2 drop column check; ERROR 42X01: Syntax error: Encountered "check" at line 1, column 32. ij> create table atdc_3 (a integer); 0 rows inserted/updated/deleted ij> create index atdc_3_idx_1 on atdc_3 (a); 0 rows inserted/updated/deleted ij> -- This fails because a is the only column in the table. alter table atdc_3 drop column a restrict; ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'THE *LAST* COLUMN A' because TABLE 'RENC_SCHEMA_1.ATDC_3' is dependent on that object. ij> drop index atdc_3_idx_1; 0 rows inserted/updated/deleted ij> -- cascade/restrict processing doesn't currently consider indexes. -- The column being dropped is automatically dropped from all indexes -- as well. If that was the only (last) column in the index, then the -- index is dropped, too. create table atdc_4 (a int, b int, c int, d int, e int); 0 rows inserted/updated/deleted ij> insert into atdc_4 values (1,2,3,4,5); 1 row inserted/updated/deleted ij> create index atdc_4_idx_1 on atdc_4 (a); 0 rows inserted/updated/deleted ij> create index atdc_4_idx_2 on atdc_4 (b, c, d); 0 rows inserted/updated/deleted ij> create index atdc_4_idx_3 on atdc_4 (c, a); 0 rows inserted/updated/deleted ij> select conglomeratename,isindex from sys.sysconglomerates where tableid in (select tableid from sys.systables where tablename = 'ATDC_4'); CONGLOMERATENAME |ISIN& -------------------------------------------------------------------------------------------------------------------------------------- xxxxFILTERED-UUIDxxxx |false ATDC_4_IDX_1 |true ATDC_4_IDX_2 |true ATDC_4_IDX_3 |true ij> show indexes from atdc_4; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- ATDC_4 |A |true |3 |A |NULL |NULL ATDC_4 |B |true |3 |A |NULL |NULL ATDC_4 |C |true |3 |A |NULL |NULL ATDC_4 |D |true |3 |A |NULL |NULL ATDC_4 |C |true |3 |A |NULL |NULL ATDC_4 |A |true |3 |A |NULL |NULL ij> -- This succeeds, because cascade/restrict doesn't matter for indexes. The -- effect of dropping column a is that: -- index atdc_4_idx_1 is entirely dropped -- index atdc_4_idx_2 is left alone but the column positions are fixed up -- index atdc_4_idx_3 is modified to refer only to column c alter table atdc_4 drop column a restrict; 0 rows inserted/updated/deleted ij> select conglomeratename,isindex from sys.sysconglomerates where tableid in (select tableid from sys.systables where tablename = 'ATDC_4'); CONGLOMERATENAME |ISIN& -------------------------------------------------------------------------------------------------------------------------------------- xxxxFILTERED-UUIDxxxx |false ATDC_4_IDX_2 |true ATDC_4_IDX_3 |true ij> show indexes from atdc_4; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- ATDC_4 |B |true |3 |A |NULL |NULL ATDC_4 |C |true |3 |A |NULL |NULL ATDC_4 |D |true |3 |A |NULL |NULL ATDC_4 |C |true |3 |A |NULL |NULL ij> describe atdc_4; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ B |INTEGER |0 |10 |10 |NULL |NULL |YES C |INTEGER |0 |10 |10 |NULL |NULL |YES D |INTEGER |0 |10 |10 |NULL |NULL |YES E |INTEGER |0 |10 |10 |NULL |NULL |YES ij> -- The effect of dropping column c is that: -- index atdc_4_idx_2 is modified to refer to columns b and d -- index atdc_4_idx_3 is entirely dropped alter table atdc_4 drop column c restrict; 0 rows inserted/updated/deleted ij> show indexes from atdc_4; TABLE_NAME |COLUMN_NAME |NON_U&|TYPE|ASC&|CARDINA&|PAGES ---------------------------------------------------------------------------- ATDC_4 |B |true |3 |A |NULL |NULL ATDC_4 |D |true |3 |A |NULL |NULL ij> select * from atdc_4 where c = 3; ERROR 42X04: Column 'C' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'C' is not a column in the target table. ij> select count(*) from sys.sysconglomerates where conglomeratename='ATDC_4_IDX_2'; 1 ----------- 1 ij> select conglomeratename, isindex from sys.sysconglomerates where conglomeratename like 'ATDC_4%'; CONGLOMERATENAME |ISIN& -------------------------------------------------------------------------------------------------------------------------------------- ATDC_4_IDX_2 |true ij> drop index atdc_4_idx_2; 0 rows inserted/updated/deleted ij> -- drop column restrict should fail becuase column is used in a view: create table atdc_5 (a int, b int); 0 rows inserted/updated/deleted ij> create view atdc_vw_1 (vw_b) as select b from atdc_5; 0 rows inserted/updated/deleted ij> alter table atdc_5 drop column b restrict; ERROR X0Y23: Operation 'DROP COLUMN RESTRICT' cannot be performed on object 'ATDC_5(B)' because VIEW 'ATDC_VW_1' is dependent on that object. ij> select * from atdc_vw_1; VW_B ----------- ij> -- drop column cascade drops the column, and also drops the dependent view: alter table atdc_5 drop column b cascade; 0 rows inserted/updated/deleted WARNING 01501: The view ATDC_VW_1 has been dropped. ij> select * from atdc_vw_1; ERROR 42X05: Table/View 'ATDC_VW_1' does not exist. ij> -- cascade processing should transitively drop a view dependent on a view -- dependent in turn on the column being dropped: create table atdc_5a (a int, b int, c int); 0 rows inserted/updated/deleted ij> create view atdc_vw_5a_1 (vw_5a_b, vw_5a_c) as select b,c from atdc_5a; 0 rows inserted/updated/deleted ij> create view atdc_vw_5a_2 (vw_5a_c_2) as select vw_5a_c from atdc_vw_5a_1; 0 rows inserted/updated/deleted ij> alter table atdc_5a drop column b cascade; 0 rows inserted/updated/deleted WARNING 01501: The view ATDC_VW_5A_2 has been dropped. WARNING 01501: The view ATDC_VW_5A_1 has been dropped. ij> select * from atdc_vw_5a_1; ERROR 42X05: Table/View 'ATDC_VW_5A_1' does not exist. ij> select * from atdc_vw_5a_2; ERROR 42X05: Table/View 'ATDC_VW_5A_2' does not exist. ij> -- drop column restrict should fail because column is used in a trigger: create table atdc_6 (a integer, b integer); 0 rows inserted/updated/deleted ij> create trigger atdc_6_trigger_1 after update of b on atdc_6 for each row values current_date; 0 rows inserted/updated/deleted ij> alter table atdc_6 drop column b restrict; ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'B' because TRIGGER 'ATDC_6_TRIGGER_1' is dependent on that object. ij> select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1'; TRIGGERNAME -------------------------------------------------------------------------------------------------------------------------------- ATDC_6_TRIGGER_1 ij> alter table atdc_6 drop column b cascade; 0 rows inserted/updated/deleted WARNING 01502: The trigger ATDC_6_TRIGGER_1 on table ATDC_6 has been dropped. ij> select triggername from sys.systriggers where triggername='ATDC_6_TRIGGER_1'; TRIGGERNAME -------------------------------------------------------------------------------------------------------------------------------- ij> create table atdc_7 (a int, b int, c int, primary key (a)); 0 rows inserted/updated/deleted ij> alter table atdc_7 drop column a restrict; ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'A' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object. ij> alter table atdc_7 drop column a cascade; 0 rows inserted/updated/deleted WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_7 has been dropped. ij> create table atdc_8 (a int, b int, c int, primary key (b, c)); 0 rows inserted/updated/deleted ij> alter table atdc_8 drop column c restrict; ERROR X0Y25: Operation 'DROP COLUMN' cannot be performed on object 'C' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object. ij> alter table atdc_8 drop column c cascade; 0 rows inserted/updated/deleted WARNING 01500: The constraint xxxxGENERATED-IDxxxx on table ATDC_8 has been dropped. ij> create table atdc_9 (a int not null, b int); 0 rows inserted/updated/deleted ij> alter table atdc_9 drop column a restrict; 0 rows inserted/updated/deleted ij> -- Verify that a GRANTED privilege fails a drop column in RESTRICT mode, -- and verify that the privilege is dropped in CASCADE mode: create table atdc_10 (a int, b int, c int); 0 rows inserted/updated/deleted ij> grant select(a, b, c) on atdc_10 to bryan; 0 rows inserted/updated/deleted ij> select * from sys.syscolperms; COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- xxxxFILTERED-UUIDxxxx|BRYAN |APP |xxxxFILTERED-UUIDxxxx|s|{0, 1} xxxxFILTERED-UUIDxxxx|BRYAN |APP |xxxxFILTERED-UUIDxxxx|s|{0, 1, 2} ij> alter table atdc_10 drop column b restrict; 0 rows inserted/updated/deleted ij> select * from sys.syscolperms; COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- xxxxFILTERED-UUIDxxxx|BRYAN |APP |xxxxFILTERED-UUIDxxxx|s|{0, 1} xxxxFILTERED-UUIDxxxx|BRYAN |APP |xxxxFILTERED-UUIDxxxx|s|{0, 1} ij> alter table atdc_10 drop column b cascade; ERROR 42X14: 'B' is not a column in table or VTI 'ATDC_10'. ij> select * from sys.syscolperms; COLPERMSID |GRANTEE |GRANTOR |TABLEID |&|COLUMNS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- xxxxFILTERED-UUIDxxxx|BRYAN |APP |xxxxFILTERED-UUIDxxxx|s|{0, 1} xxxxFILTERED-UUIDxxxx|BRYAN |APP |xxxxFILTERED-UUIDxxxx|s|{0, 1} ij> -- Include the test from the DERBY-1909 report: drop table d1909; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'D1909' because it does not exist. ij> create table d1909 (a int, b int, c int); 0 rows inserted/updated/deleted ij> grant select (a) on d1909 to user1; 0 rows inserted/updated/deleted ij> grant select (a,b) on d1909 to user2; 0 rows inserted/updated/deleted ij> grant update(c) on d1909 to super_user; 0 rows inserted/updated/deleted ij> select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t where c.tableid = t.tableid and t.tablename='D1909'; GRANTEE |&|COLUMNS -------------------------------------------------------------------------------------------------------------------------------------------------- USER1 |s|{0} USER2 |s|{0, 1} SUPER_USER |u|{2} ij> alter table d1909 drop column a; 0 rows inserted/updated/deleted ij> select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t where c.tableid = t.tableid and t.tablename='D1909'; GRANTEE |&|COLUMNS -------------------------------------------------------------------------------------------------------------------------------------------------- USER1 |s|{} USER2 |s|{0} SUPER_USER |u|{1} ij> grant update(b) on d1909 to user1; 0 rows inserted/updated/deleted ij> grant select(c) on d1909 to user1; 0 rows inserted/updated/deleted ij> grant select(c) on d1909 to user2; 0 rows inserted/updated/deleted ij> select c.grantee, c.type, c.columns from sys.syscolperms c, sys.systables t where c.tableid = t.tableid and t.tablename='D1909'; GRANTEE |&|COLUMNS -------------------------------------------------------------------------------------------------------------------------------------------------- USER1 |s|{1} USER2 |s|{0, 1} SUPER_USER |u|{1} USER1 |u|{0} ij> -- JIRA 2371: ensure that a non-numeric, non-autogenerated column can -- have its default value modified: create table t2371 ( a varchar(10)); 0 rows inserted/updated/deleted ij> describe t2371; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ A |VARCHAR |NULL|NULL|10 |NULL |20 |YES ij> alter table t2371 alter column a default 'my val'; 0 rows inserted/updated/deleted ij> describe t2371; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ A |VARCHAR |NULL|NULL|10 |'my val' |20 |YES ij> insert into t2371 (a) values ('hi'); 1 row inserted/updated/deleted ij> insert into t2371 (a) values (default); 1 row inserted/updated/deleted ij> alter table t2371 alter column a default 'another'; 0 rows inserted/updated/deleted ij> describe t2371; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ A |VARCHAR |NULL|NULL|10 |'another' |20 |YES ij> insert into t2371 (a) values (default); 1 row inserted/updated/deleted ij> select * from t2371; A ---------- hi my val another ij>