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 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 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); 0 rows inserted/updated/deleted 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); 0 rows inserted/updated/deleted 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 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 SYSROLES |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, 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, 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> --set column, part of unique constraint, to 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; 0 rows inserted/updated/deleted 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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> rename column renc_1.a; ERROR 42X01: Syntax error: Encountered "" at line 1, column 22. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> rename column renc_1.a b; ERROR 42X01: Syntax error: Encountered "b" at line 1, column 24. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> rename column renc_1.a to; ERROR 42X01: Syntax error: Encountered "" at line 1, column 25. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> rename column renc_1.a to b, c; ERROR 42X01: Syntax error: Encountered "," at line 1, column 28. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> rename column renc_1.a to b and c to d; ERROR 42X01: Syntax error: Encountered "and" at line 1, column 29. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> alter table atdc_2 drop column; ERROR 42X01: Syntax error: Encountered "" at line 1, column 30. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> alter table atdc_2 drop column constraint; ERROR 42X01: Syntax error: Encountered "constraint" at line 1, column 32. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> alter table atdc_2 drop column primary; ERROR 42X01: Syntax error: Encountered "primary" at line 1, column 32. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> alter table atdc_2 drop column foreign; ERROR 42X01: Syntax error: Encountered "foreign" at line 1, column 32. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. ij> alter table atdc_2 drop column check; ERROR 42X01: Syntax error: Encountered "check" at line 1, column 32. Issue the 'help' command for general information on IJ command syntax. Any unrecognized commands are treated as potential SQL commands and executed directly. Consult your DBMS server reference documentation for details of the SQL syntax supported by your server. 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 3175: Null Pointer Exception or SanityManager ASSERT because -- autoincrement properties of generated column are not maintained properly -- when a column before it in the table is dropped: create table d3175 (x varchar(12), y varchar(12), id int primary key generated by default as identity); 0 rows inserted/updated/deleted ij> select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and t.tablename='D3175'; REFERENCEID |COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE |COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |TABLEID |TABLENAME |&|SCHEMAID |& ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ xxxxFILTERED-UUIDxxxx|ID |3 |INTEGER NOT NU&|GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|1 |1 |1 |xxxxFILTERED-UUIDxxxx|D3175 |T|xxxxFILTERED-UUIDxxxx|R xxxxFILTERED-UUIDxxxx|X |1 |VARCHAR(12) |NULL |NULL |NULL |NULL |NULL |xxxxFILTERED-UUIDxxxx|D3175 |T|xxxxFILTERED-UUIDxxxx|R xxxxFILTERED-UUIDxxxx|Y |2 |VARCHAR(12) |NULL |NULL |NULL |NULL |NULL |xxxxFILTERED-UUIDxxxx|D3175 |T|xxxxFILTERED-UUIDxxxx|R ij> insert into d3175(x) values 'b'; 1 row inserted/updated/deleted ij> alter table d3175 drop column y; 0 rows inserted/updated/deleted ij> insert into d3175(x) values 'a'; 1 row inserted/updated/deleted ij> select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and t.tablename='D3175'; REFERENCEID |COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE |COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |TABLEID |TABLENAME |&|SCHEMAID |& ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ xxxxFILTERED-UUIDxxxx|ID |2 |INTEGER NOT NU&|GENERATED_BY_D&|xxxxFILTERED-UUIDxxxx|3 |1 |1 |xxxxFILTERED-UUIDxxxx|D3175 |T|xxxxFILTERED-UUIDxxxx|R xxxxFILTERED-UUIDxxxx|X |1 |VARCHAR(12) |NULL |NULL |NULL |NULL |NULL |xxxxFILTERED-UUIDxxxx|D3175 |T|xxxxFILTERED-UUIDxxxx|R ij> -- JIRA 3177 appears to be aduplicate of JIRA 3175, but the reproduction -- test script is different. In the interests of additional testing, we -- include the JIRA 3177 test script, as it has a number of additional -- examples of interesting ALTER TABLE statements -- -- In the original JIRA 3177 bug, by the time we get to the end of the -- ALTER TABLE processing, the select from SYS.SYSCOLUMNS retrieves NULL -- for the autoinc columns, instead of the correct value (1). create table d3177_SchemaVersion ( version INTEGER NOT NULL ); 0 rows inserted/updated/deleted ij> insert into d3177_SchemaVersion (version) values (0); 1 row inserted/updated/deleted ij> create table d3177_BinaryData ( id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), CRC32 BIGINT NOT NULL , data BLOB NOT NULL , CONSTRAINT d3177_BinaryData_id_pk PRIMARY KEY(id) ); 0 rows inserted/updated/deleted ij> create table d3177_MailServers ( id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), port INTEGER NOT NULL , username varchar(80) NOT NULL , protocol varchar(80) NOT NULL , SSLProtocol varchar(10), emailAddress varchar(80) NOT NULL , server varchar(80) NOT NULL , password varchar(80) NOT NULL , CONSTRAINT d3177_MailServers_id_pk PRIMARY KEY(id) ); 0 rows inserted/updated/deleted ij> create table d3177_Mailboxes ( id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), port INTEGER NOT NULL , folder varchar(80) NOT NULL , username varchar(80) NOT NULL , SSLProtocol varchar(10), hostname varchar(80) NOT NULL , storeType varchar(80) NOT NULL , password varchar(80) NOT NULL , timeout INTEGER NOT NULL , MailServerID INTEGER NOT NULL , CONSTRAINT d3177_Mailboxes_id_pk PRIMARY KEY(id) ); 0 rows inserted/updated/deleted ij> create table d3177_MESSAGES ( Message_From varchar(1000), Message_Cc varchar(1000), Message_Subject varchar(1000), Message_ID varchar(256) NOT NULL , Message_Bcc varchar(1000), Message_Date TIMESTAMP, Content_Type varchar(256), MailboxID INTEGER NOT NULL , Search_Text CLOB NOT NULL , id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Message_To varchar(1000), Display_Text CLOB NOT NULL , Message_Data_ID INTEGER NOT NULL , CONSTRAINT d3177_MESSAGES_id_pk PRIMARY KEY(id) ); 0 rows inserted/updated/deleted ij> select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and c.columnname='ID' and t.tablename='D3177_MESSAGES'; REFERENCEID |COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE |COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |TABLEID |TABLENAME |&|SCHEMAID |& ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ xxxxFILTERED-UUIDxxxx|ID |10 |INTEGER NOT NU&|NULL |NULL |1 |1 |1 |xxxxFILTERED-UUIDxxxx|D3177_MESSAGES |T|xxxxFILTERED-UUIDxxxx|R ij> create table D3177_ATTACHMENTS ( id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), Inline INTEGER, CRC32 BIGINT NOT NULL , Attachment_Name varchar(256) NOT NULL , Attachment_File varchar(512) NOT NULL , Message_ID INTEGER NOT NULL , Content_Type varchar(256) NOT NULL , CONSTRAINT D3177_ATTACHMENTS_id_pk PRIMARY KEY(id) ); 0 rows inserted/updated/deleted ij> alter table D3177_ATTACHMENTS ADD CONSTRAINT ATTACHMENTS_Message_ID_MESSAGES_ID FOREIGN KEY ( Message_ID ) REFERENCES D3177_MESSAGES ( ID ); 0 rows inserted/updated/deleted ij> alter table D3177_MESSAGES ADD CONSTRAINT MESSAGES_MailboxID_Mailboxes_ID FOREIGN KEY ( MailboxID ) REFERENCES d3177_Mailboxes ( ID ); 0 rows inserted/updated/deleted ij> alter table D3177_MESSAGES ADD CONSTRAINT MESSAGES_Message_Data_ID_d3177_BinaryData_ID FOREIGN KEY ( Message_Data_ID ) REFERENCES d3177_BinaryData ( ID ); 0 rows inserted/updated/deleted ij> alter table d3177_Mailboxes ADD CONSTRAINT Mailboxes_MailServerID_MailServers_ID FOREIGN KEY ( MailServerID ) REFERENCES d3177_MailServers ( ID ); 0 rows inserted/updated/deleted ij> update d3177_SchemaVersion set version=1; 1 row inserted/updated/deleted ij> alter table D3177_MESSAGES alter Message_To SET DATA TYPE varchar(10000); 0 rows inserted/updated/deleted ij> alter table D3177_MESSAGES alter Message_From SET DATA TYPE varchar(10000); 0 rows inserted/updated/deleted ij> alter table D3177_MESSAGES alter Message_Cc SET DATA TYPE varchar(10000); 0 rows inserted/updated/deleted ij> alter table D3177_MESSAGES alter Message_Bcc SET DATA TYPE varchar(10000); 0 rows inserted/updated/deleted ij> select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and c.columnname='ID' and t.tablename='D3177_MESSAGES'; REFERENCEID |COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE |COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |TABLEID |TABLENAME |&|SCHEMAID |& ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ xxxxFILTERED-UUIDxxxx|ID |10 |INTEGER NOT NU&|NULL |NULL |1 |1 |1 |xxxxFILTERED-UUIDxxxx|D3177_MESSAGES |T|xxxxFILTERED-UUIDxxxx|R ij> update d3177_SchemaVersion set version=2; 1 row inserted/updated/deleted ij> create table D3177_MailStatistics ( id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), ProcessedCount INTEGER DEFAULT 0 NOT NULL , HourOfDay INTEGER NOT NULL , LastModified TIMESTAMP NOT NULL , RejectedMailCount INTEGER DEFAULT 0 NOT NULL , DayOfWeek INTEGER NOT NULL , CONSTRAINT D3177_MailStatistics_id_pk PRIMARY KEY(id) ); 0 rows inserted/updated/deleted ij> CREATE INDEX D3177_MailStatistics_HourOfDay_idx ON D3177_MailStatistics(HourOfDay); 0 rows inserted/updated/deleted ij> CREATE INDEX D3177_MailStatistics_DayOfWeek_idx ON D3177_MailStatistics(DayOfWeek); 0 rows inserted/updated/deleted ij> alter table D3177_MESSAGES alter CONTENT_TYPE SET DATA TYPE varchar(256); 0 rows inserted/updated/deleted ij> update d3177_SchemaVersion set version=3; 1 row inserted/updated/deleted ij> alter table D3177_messages alter column Message_ID NULL; 0 rows inserted/updated/deleted ij> CREATE INDEX D3177_MESSAGES_Message_ID_idx ON D3177_MESSAGES(Message_ID); 0 rows inserted/updated/deleted ij> update d3177_SchemaVersion set version=4; 1 row inserted/updated/deleted ij> alter table D3177_MESSAGES add filename varchar(256); 0 rows inserted/updated/deleted ij> alter table D3177_MESSAGES add CRC32 BIGINT; 0 rows inserted/updated/deleted ij> select id,crc32,data from d3177_BinaryData; ID |CRC32 |DATA ----------------------------------------------------------------------------------------------------------------------------------------------------------------- ij> select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and c.columnname='ID' and t.tablename='D3177_MESSAGES'; REFERENCEID |COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE |COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |TABLEID |TABLENAME |&|SCHEMAID |& ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ xxxxFILTERED-UUIDxxxx|ID |10 |INTEGER NOT NU&|NULL |NULL |1 |1 |1 |xxxxFILTERED-UUIDxxxx|D3177_MESSAGES |T|xxxxFILTERED-UUIDxxxx|R ij> alter table D3177_messages alter column filename NOT NULL; 0 rows inserted/updated/deleted ij> alter table D3177_messages alter column crc32 NOT NULL; 0 rows inserted/updated/deleted ij> alter table D3177_messages alter column mailboxid NULL; 0 rows inserted/updated/deleted ij> ALTER TABLE D3177_MESSAGES DROP CONSTRAINT MESSAGES_message_data_id_BinaryData_id; ERROR 42X86: ALTER TABLE failed. There is no constraint 'RENC_SCHEMA_1.MESSAGES_MESSAGE_DATA_ID_BINARYDATA_ID' on table '"RENC_SCHEMA_1"."D3177_MESSAGES"'. ij> alter table D3177_messages drop column message_data_id; 0 rows inserted/updated/deleted WARNING 01500: The constraint MESSAGES_MESSAGE_DATA_ID_D3177_BINARYDATA_ID on table D3177_MESSAGES has been dropped. ij> drop table d3177_BinaryData; 0 rows inserted/updated/deleted ij> update d3177_SchemaVersion set version=6; 1 row inserted/updated/deleted ij> create table D3177_EmailAddresses ( id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), address varchar(256) NOT NULL , CONSTRAINT D3177_EmailAddresses_id_pk PRIMARY KEY(id), CONSTRAINT D3177_EmailAddresses_address_uq UNIQUE(address) ); 0 rows inserted/updated/deleted ij> CREATE UNIQUE INDEX D3177_EmailAddresses_address_idx ON D3177_EmailAddresses(address); 0 rows inserted/updated/deleted WARNING 01504: The new index is a duplicate of an existing index: xxxxGENERATED-IDxxxx. ij> create table D3177_EmailAddressesToMessages ( MessageID INTEGER NOT NULL , EmailAddressID INTEGER NOT NULL ); 0 rows inserted/updated/deleted ij> alter table D3177_EmailAddressesToMessages ADD CONSTRAINT EmailAddressesToMessages_MessageID_Messages_ID FOREIGN KEY ( MessageID ) REFERENCES D3177_Messages ( ID ); 0 rows inserted/updated/deleted ij> alter table D3177_EmailAddressesToMessages ADD CONSTRAINT EmailAddressesToMessages_EmailAddressID_EmailAddresses_ID FOREIGN KEY ( EmailAddressID ) REFERENCES D3177_EmailAddresses ( ID ); 0 rows inserted/updated/deleted ij> create table AuthenticationServers ( id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), port INTEGER NOT NULL , protocol varchar(20) NOT NULL , hostname varchar(40) NOT NULL , CONSTRAINT AuthenticationServers_id_pk PRIMARY KEY(id) ); 0 rows inserted/updated/deleted ij> alter table d3177_Mailboxes add AuthenticationServerID INTEGER; 0 rows inserted/updated/deleted ij> select id,filename from D3177_messages; ID |FILENAME -------------------------------------------------------------------------------------------------------------------------------------------- ij> alter table D3177_MESSAGES drop column message_to; 0 rows inserted/updated/deleted ij> alter table D3177_MESSAGES drop column message_cc; 0 rows inserted/updated/deleted ij> alter table D3177_MESSAGES drop column message_from; 0 rows inserted/updated/deleted ij> select * from sys.syscolumns c,sys.systables t where c.referenceid = t.tableid and c.columnname='ID' and t.tablename='D3177_MESSAGES'; REFERENCEID |COLUMNNAME |COLUMNNUMB&|COLUMNDATATYPE |COLUMNDEFAULT |COLUMNDEFAULTID |AUTOINCREMENTVALUE |AUTOINCREMENTSTART |AUTOINCREMENTINC |TABLEID |TABLENAME |&|SCHEMAID |& ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ xxxxFILTERED-UUIDxxxx|ID |8 |INTEGER NOT NU&|NULL |NULL |1 |1 |1 |xxxxFILTERED-UUIDxxxx|D3177_MESSAGES |T|xxxxFILTERED-UUIDxxxx|R ij> update d3177_SchemaVersion set version=7; 1 row inserted/updated/deleted 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> -- DERBY-3355: Exercise ALTER TABLE ... NOT NULL with table and column -- names which are in mixed case. This is important because -- AlterTableConstantAction.validateNotNullConstraint generates and -- executes some SQL on-the-fly, and it's important that it properly -- delimits the table and column names in that SQL. We also include a few -- other "unusual" table and column names. create table d3355 ( c1 varchar(10), "c2" varchar(10), c3 varchar(10)); 0 rows inserted/updated/deleted ij> create table "d3355_a" ( c1 varchar(10), "c2" varchar(10), c3 varchar(10)); 0 rows inserted/updated/deleted ij> create table d3355_qt_col ("""c""4" int, """""C5" int, "c 6" int); 0 rows inserted/updated/deleted ij> create table "d3355_qt_""tab" ( c4 int, c5 int, c6 int); 0 rows inserted/updated/deleted ij> insert into d3355 values ('a', 'b', 'c'); 1 row inserted/updated/deleted ij> insert into "d3355_a" values ('d', 'e', 'f'); 1 row inserted/updated/deleted ij> insert into d3355_qt_col values (4, 5, 6); 1 row inserted/updated/deleted ij> insert into "d3355_qt_""tab" values (4, 5, 6); 1 row inserted/updated/deleted ij> -- All of these ALTER TABLE statements should succeed. alter table d3355 alter column c1 not null; 0 rows inserted/updated/deleted ij> alter table d3355 alter column "c2" not null; 0 rows inserted/updated/deleted ij> alter table d3355 alter column "C3" not null; 0 rows inserted/updated/deleted ij> alter table "d3355_a" alter column c1 not null; 0 rows inserted/updated/deleted ij> alter table "d3355_a" alter column "c2" not null; 0 rows inserted/updated/deleted ij> alter table "d3355_a" alter column "C3" not null; 0 rows inserted/updated/deleted ij> alter table d3355_qt_col alter column """""C5" not null; 0 rows inserted/updated/deleted ij> alter table d3355_qt_col alter column "c 6" not null; 0 rows inserted/updated/deleted ij> alter table "d3355_qt_""tab" alter column c5 not null; 0 rows inserted/updated/deleted ij> -- These ALTER TABLE statements should fail, with no-such-column and/or -- no-such-table errors: alter table d3355 alter column "c1" not null; ERROR 42X14: 'c1' is not a column in table or VTI 'D3355'. ij> alter table d3355 alter column c2 not null; ERROR 42X14: 'C2' is not a column in table or VTI 'D3355'. ij> alter table d3355_a alter column c1 not null; ERROR 42Y55: 'ALTER TABLE' cannot be performed on 'D3355_A' because it does not exist. ij> alter table "d3355_a" alter column "c1" not null; ERROR 42X14: 'c1' is not a column in table or VTI 'd3355_a'. ij>