📄 altertable.out
字号:
0 rows inserted/updated/deletedij> drop index i13;0 rows inserted/updated/deletedij> drop index i14;0 rows inserted/updated/deletedij> -- more datainsert into tab1 (c2, c3, c4) values (22, 8.9, 5);1 row inserted/updated/deletedij> insert into tab1 (c2, c3, c4) values (11, 4.5, 67);1 row inserted/updated/deletedij> select c2 from tab1;C2 -----------3 5 8 9 10 11 15 22 ij> -- add a new columnalter table tab1 add column c5 double;0 rows inserted/updated/deletedij> -- drop view vw2 so can create a new one, with where clausedrop view vw2;0 rows inserted/updated/deletedij> create view vw2 (c1) as select c5 from tab1 where c2 > 5;0 rows inserted/updated/deletedij> -- drop vw2 as welldrop view vw2;0 rows inserted/updated/deletedij> alter table tab1 drop constraint tab1pk;0 rows inserted/updated/deletedij> -- any surviving index? -- creating the index should not failselect 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/deletedij> -- should drop t2ckalter table tab2 drop constraint t2ck;0 rows inserted/updated/deletedij> -- this should drop t3fk, unique constraint and backing indexalter table tab3 drop constraint t3fk;0 rows inserted/updated/deletedij> alter table tab2 drop constraint c4_PK;0 rows inserted/updated/deletedij> insert into tab3 values (1,2,3,4);1 row inserted/updated/deletedij> -- drop view vw3drop view vw3;0 rows inserted/updated/deletedij> -- violates t3ckinsert into tab3 (c1, c2, c3) values (81, 1, 2);1 row inserted/updated/deletedij> insert into tab3 (c1, c2, c3) values (81, 2, 2);1 row inserted/updated/deletedij> -- this should drop t3ck, vw4alter table tab3 drop constraint t3ck;0 rows inserted/updated/deletedij> drop view vw4;0 rows inserted/updated/deletedij> insert into tab3 (c2, c3) values (-82, 9);1 row inserted/updated/deletedij> create view vw4 (c1) as select c3 from tab3 where c3+5>c4;0 rows inserted/updated/deletedij> -- drop view vw4drop view vw4;0 rows inserted/updated/deletedij> rollback;ij> -- check that dropping a column will drop backing index on referencing-- tablecreate table tt1(a int, b int not null constraint tt1uc unique);0 rows inserted/updated/deletedij> create table reftt1(a int constraint reftt1rc references tt1(b));0 rows inserted/updated/deletedij> -- count should be 2select count(*) from sys.sysconglomerates c, sys.systables t where t.tableid = c.tableidand t.tablename = 'REFTT1';1 -----------2 ij> alter table reftt1 drop constraint reftt1rc;0 rows inserted/updated/deletedij> alter table tt1 drop constraint tt1uc;0 rows inserted/updated/deletedij> -- count should be 1select count(*) from sys.sysconglomerates c, sys.systables t where t.tableid = c.tableidand t.tablename = 'REFTT1';1 -----------1 ij> rollback;ij> -- add constraint-- negative tests-- add primary key to table which already has onealter table t0 add column c3 int;0 rows inserted/updated/deletedij> alter table t0 add constraint cons1 primary key(c3);ERROR 42831: 'C3' cannot be a column of a primary key or unique key because it can contain null values.ij> alter table t0 add primary key(c3);ERROR 42831: 'C3' cannot be a column of a primary key or unique key because it can contain null values.ij> -- add constraint references non-existant columnalter 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 duplicatesalter 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 verifyalter table t0_1 add column c2 int not null constraint p2 primary key default 0;0 rows inserted/updated/deletedij> insert 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> select * from t0_1;C1 |C2 -----------------------1 |1 ij> rollback;ij> -- add check constraint to 0 row table and verifyalter table t0_1 add column c2 int check(c2 != 3);0 rows inserted/updated/deletedij> insert into t0_1 values (1, 1);1 row inserted/updated/deletedij> 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/deletedij> select * from t0_1;C1 |C2 -----------------------1 |1 1 |1 ij> rollback;ij> -- add check constraint to table with rows that are okalter table t0_1 add column c2 int;0 rows inserted/updated/deletedij> insert into t0_1 values (1, 1);1 row inserted/updated/deletedij> insert into t0_1 values (2, 2);1 row inserted/updated/deletedij> alter table t0_1 add constraint ck1 check(c2 = c1);0 rows inserted/updated/deletedij> select * from t0_1;C1 |C2 -----------------------1 |1 2 |2 ij> -- verify constraint has been added, the following should failinsert 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 failuresalter table t0_1 add column c2 int;0 rows inserted/updated/deletedij> insert into t0_1 values (1, 1);1 row inserted/updated/deletedij> insert into t0_1 values (2, 2);1 row inserted/updated/deletedij> insert into t0_1 values (2, 2);1 row inserted/updated/deletedij> insert into t0_1 values (666, 2);1 row inserted/updated/deletedij> insert into t0_1 values (2, 2);1 row inserted/updated/deletedij> insert into t0_1 values (3, 3);1 row inserted/updated/deletedij> insert into t0_1 values (666, 3);1 row inserted/updated/deletedij> insert into t0_1 values (666, 3);1 row inserted/updated/deletedij> insert into t0_1 values (3, 3);1 row inserted/updated/deletedij> 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 succeedinsert into t0_1 values (1, 3);1 row inserted/updated/deletedij> 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 enforcedalter table t0_1 add column c2 int not null constraint p2 primary key default 0;0 rows inserted/updated/deletedij> alter table t0_1 add check(c2 = c1);0 rows inserted/updated/deletedij> insert into t0_1 values (1, 1);1 row inserted/updated/deletedij> 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/deletedij> select * from t0_1;C1 |C2 -----------------------1 |1 2 |2 ij> rollback;ij> -- add primary key constraint to table with > 1 rowalter table t3 add column c3 int;0 rows inserted/updated/deletedij> alter table t3 add unique(c3);ERROR 42831: 'C3' cannot be a column of a primary key or unique key because it can contain null values.ij> -- add unique constraint to 0 and 1 row tables and verify alter table t0_2 add column c2 int not null unique default 0;0 rows inserted/updated/deletedij> insert into t0_2 values (1, default);1 row inserted/updated/deletedij> insert into t0_2 values (1, 1);1 row inserted/updated/deletedij> delete from t1_1;1 row inserted/updated/deletedij> alter table t1_1 add column c2 int not null unique default 0;0 rows inserted/updated/deletedij> insert into t1_1 values (1, 2);1 row inserted/updated/deletedij> 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/deletedij> -- add unique constraint to table with > 1 rowalter table t3 add unique(c1);ERROR 42831: 'C1' cannot be a column of a primary key or unique key because it can contain null values.ij> -- verify prepared alter table dependent on underlying tableprepare 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/deletedij> prepare p1 as 'alter table xxx add check(c2 = 1)';ij> execute p1;0 rows inserted/updated/deletedij> drop table xxx;0 rows inserted/updated/deletedij> create table xxx(c1 int);0 rows inserted/updated/deletedij> 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/deletedij> execute p1;0 rows inserted/updated/deletedij> drop table xxx;0 rows inserted/updated/deletedij> -- 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_1 |1 T0_2 |1 T0_3 |1 T1 |1 ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -