📄 altertable.out
字号:
T1_1 |1 T2 |1 T3 |1 T4 |1 ij> -- drop constraint-- negative tests-- drop non-existent constraintalter table t0 drop constraint notexists;ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.NOTEXISTS' on table 'APP.T0'. ij> -- constraint/table mismatchalter 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 keyalter table t0_1 add constraint p2 primary key(c1);ERROR 42831: 'C1' cannot be a column of a primary key or unique key because it can contain null values.ij> alter table t0_1 drop constraint p2;ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.P2' on table 'APP.T0_1'. ij> -- positive tests-- verify that we can add/drop/add/drop/... constraintsalter table t0_1 add column c2 int not null constraint p2 primary key default 0;0 rows inserted/updated/deletedij> delete from t0_1;0 rows inserted/updated/deletedij> alter table t0_1 drop constraint p2;0 rows inserted/updated/deletedij> alter table t0_1 add constraint p2 primary key(c2);0 rows inserted/updated/deletedij> alter table t0_1 drop constraint p2;0 rows inserted/updated/deletedij> alter table t0_1 add constraint p2 primary key(c2);0 rows inserted/updated/deletedij> -- verify that constraint is still enforcedinsert into t0_1 values (1,1);1 row inserted/updated/deletedij> 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 tablesselect 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 tableprepare p1 as 'alter table t0_1 drop constraint p2';ij> execute p1;0 rows inserted/updated/deletedij> drop table t0_1;0 rows inserted/updated/deletedij> create table t0_1 (c1 int, c2 int not null constraint p2 primary key);0 rows inserted/updated/deletedij> execute p1;0 rows inserted/updated/deletedij> -- do consistency check on scans, etc.-- values (org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker::countOpens());-- verify the consistency of the indexes on the system catalogsselect 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 ij> -- verify the consistency of the indexes on the user tablesselect 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 793create table b793 (pn1 int not null constraint named_primary primary key, pn2 int constraint named_pn2 check (pn2 > 3));0 rows inserted/updated/deletedij> alter table b793 drop constraint named_primary;0 rows inserted/updated/deletedij> drop table b793;0 rows inserted/updated/deletedij> -- test that drop constraint removes backing indexesdrop table t1;0 rows inserted/updated/deletedij> create table t1(a int not null constraint t1_pri primary key);0 rows inserted/updated/deletedij> create table reft1(a int constraint t1_ref references t1(a));0 rows inserted/updated/deletedij> -- count should be 2select count(*) from sys.sysconglomerates c, sys.systables twhere c.tableid = t.tableid andt.tablename = 'REFT1';1 -----------2 ij> alter table reft1 drop constraint t1_ref;0 rows inserted/updated/deletedij> alter table t1 drop constraint t1_pri;0 rows inserted/updated/deletedij> -- count should be 1select count(*) from sys.sysconglomerates c, sys.systables twhere c.tableid = t.tableid andt.tablename = 'REFT1';1 -----------1 ij> drop table reft1;0 rows inserted/updated/deletedij> -- clean updrop view v2;0 rows inserted/updated/deletedij> drop view v1;0 rows inserted/updated/deletedij> drop table t0;0 rows inserted/updated/deletedij> drop table t0_1;0 rows inserted/updated/deletedij> drop table t0_2;0 rows inserted/updated/deletedij> drop table t0_3;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> drop table t1_1;0 rows inserted/updated/deletedij> drop table t3;0 rows inserted/updated/deletedij> drop table t4;0 rows inserted/updated/deletedij> ---------------------------------------------------------- special funky schema tests--------------------------------------------------------create schema newschema;0 rows inserted/updated/deletedij> 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/deletedij> alter table x add constraint newcons primary key (x);0 rows inserted/updated/deletedij> -- schemaname should be appselect 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/deletedij> alter table x add constraint newcons primary key (x);0 rows inserted/updated/deletedij> -- schemaname should be appselect schemaname, constraintname from sys.sysconstraints c, sys.sysschemas s where s.schemaid = c.schemaid order by 1;SCHEMANAME |CONSTRAINTNAME -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------APP |NEWCONS ij> -- failalter table x drop constraint badschema.newcons;ERROR 42Y07: Schema 'BADSCHEMA' does not existij> -- failalter table x drop constraint newschema.newcons;ERROR 42X86: ALTER TABLE failed. There is no constraint 'NEWSCHEMA.NEWCONS' on table 'APP.X'. ij> -- okalter table x drop constraint app.newcons;0 rows inserted/updated/deletedij> -- bad schema namealter 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/deletedij> create table x (x int not null, y int not null);0 rows inserted/updated/deletedij> alter table x add constraint con check (x > 1);0 rows inserted/updated/deletedij> 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/deletedij> set schema emptyschema;0 rows inserted/updated/deletedij> -- fail, cannot find emptyschema.connalter 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/deletedij> -- add constraint, default to table schemaalter table app.x add constraint con2 check (x > 1);0 rows inserted/updated/deletedij> -- 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/deletedij> drop schema newschema restrict;0 rows inserted/updated/deletedij> -- 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/deletedij> declare global temporary table session.t2 (c21 int) on commit delete rows not logged;0 rows inserted/updated/deletedij> declare global temporary table session.t3 (c31 int) on commit preserve rows not logged;0 rows inserted/updated/deletedij> drop table session.t1;0 rows inserted/updated/deletedij> drop table session.t2;0 rows inserted/updated/deletedij> drop table session.t3;0 rows inserted/updated/deletedij> 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/deletedij> insert into t1 values (1, 1);1 row inserted/updated/deletedij> 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/deletedij> insert into t1 values (1, 1);1 row inserted/updated/deletedij> 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 existij> 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/deletedij> 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/deletedij> 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/deletedij> -- bug 5817 - make LOGGED non-reserved keyword. following test cases for thatcreate table LOGGED(c11 int);0 rows inserted/updated/deletedij> drop table LOGGED;0 rows inserted/updated/deletedij> create table logged(logged int);0 rows inserted/updated/deletedij> drop table logged;0 rows inserted/updated/deletedij> declare global temporary table session.logged(logged int) on commit delete rows not logged;0 rows inserted/updated/deletedij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -