📄 altertable.out
字号:
ij> -- alter table tests-- add column-- (add constraint & drop constraint to be added)-- create some database objectscreate table t0(c1 int not null constraint p1 primary key);0 rows inserted/updated/deletedij> create table t0_1(c1 int);0 rows inserted/updated/deletedij> create table t0_2(c1 int);0 rows inserted/updated/deletedij> create table t0_3(c1 int);0 rows inserted/updated/deletedij> create table t1(c1 int);0 rows inserted/updated/deletedij> create table t1_1(c1 int);0 rows inserted/updated/deletedij> create table t2(c1 int);0 rows inserted/updated/deletedij> create table t3(c1 int);0 rows inserted/updated/deletedij> create table t4(c1 int not null);0 rows inserted/updated/deletedij> create view v1 as select * from t2;0 rows inserted/updated/deletedij> create view v2 as select c1 from t2;0 rows inserted/updated/deletedij> create index i0_1 on t0_1(c1);0 rows inserted/updated/deletedij> create index i0_2 on t0_2(c1);0 rows inserted/updated/deletedij> -- do some populationinsert into t1 values 1;1 row inserted/updated/deletedij> insert into t1_1 values 1;1 row inserted/updated/deletedij> insert into t2 values 1;1 row inserted/updated/deletedij> insert into t2 values 2;1 row inserted/updated/deletedij> insert into t3 values 1;1 row inserted/updated/deletedij> insert into t3 values 2;1 row inserted/updated/deletedij> insert into t3 values 3;1 row inserted/updated/deletedij> insert into t4 values 1, 2, 3, 1;4 rows inserted/updated/deletedij> autocommit off;ij> -- add column-- negative tests-- alter a non-existing tablealter 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 existsalter table t0 add column c1 int;ERROR X0Y32: Column 'C1' already exists in Table/View 'APP.T0'.ij> -- alter a system tablealter table sys.systables add column c1 int;ERROR 42X62: 'ALTER TABLE' is not allowed in the 'SYS' schema.ij> -- alter table on a viewalter 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 erroralter 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 rowalter 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 itget 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 tablealter table t1 add column c2 int not null default 0;0 rows inserted/updated/deletedij> -- add a primary key column to a non-empty tablealter table t1 add column c3 int not null default 0 primary key;0 rows inserted/updated/deletedij> -- add a column with a check constraint to a non-empty columnalter table t1 add column c4 int check(c4 = 1);0 rows inserted/updated/deletedij> 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/deletedij> -- select * views don't see added columns after alter tableselect * from v1;C1 -----------1 2 ij> -- select * prepared statements do see added columns after alter tableexecute p1;C1 |C2 -----------------------1 |NULL 2 |NULL ij> -- rollback and recheckrollback;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 verifyalter table t0 add column c2 int not null default 0;0 rows inserted/updated/deletedij> insert into t0 values (1, default);1 row inserted/updated/deletedij> select * from t0;C1 |C2 -----------------------1 |0 ij> drop table t0;0 rows inserted/updated/deletedij> rollback;ij> select * from t0;C1 -----------ij> -- add primary key to 0 row table and verifyalter table t0_1 add column c2 int not null 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 '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/deletedij> insert into t0_1 values (1, default);1 row inserted/updated/deletedij> 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/deletedij> delete from t1;1 row inserted/updated/deletedij> alter table t1 add column c2 int not null unique default 0;0 rows inserted/updated/deletedij> insert into t1 values (2, 2);1 row inserted/updated/deletedij> insert into t1 values (3, 1);1 row 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 T1_1 |1 T2 |1 T3 |1 T4 |1 ij> rollback;ij> create function countopens() returns varchar(128)language java parameter style javaexternal name 'org.apache.derbyTesting.functionTests.util.T_ConsistencyChecker.countOpens';0 rows inserted/updated/deletedij> commit;ij> -- do consistency check on scans, etc.values countopens();1 --------------------------------------------------------------------------------------------------------------------------------No open scans, etc.ij> -- some typical datacreate table tab1 (c1 int, c2 int not null constraint tab1pk primary key, c3 double, c4 int);0 rows inserted/updated/deletedij> create index i11 on tab1 (c1);0 rows inserted/updated/deletedij> create unique index i12 on tab1 (c1);0 rows inserted/updated/deletedij> create index i13 on tab1 (c3, c1, c4);0 rows inserted/updated/deletedij> create unique index i14 on tab1 (c3, c1);0 rows inserted/updated/deletedij> insert into tab1 values (6, 5, 4.5, 90);1 row inserted/updated/deletedij> insert into tab1 values (10, 3, 8.9, -5);1 row inserted/updated/deletedij> insert into tab1 values (100, 15, 4.5, 9);1 row inserted/updated/deletedij> insert into tab1 values (2, 8, 4.4, 8);1 row inserted/updated/deletedij> insert into tab1 values (11, 9, 2.5, 88);1 row inserted/updated/deletedij> insert into tab1 values(null,10, 3.5, 99);1 row inserted/updated/deletedij> create view vw1 (col_sum, col_diff) as select c1+c4, c1-c4 from tab1;0 rows inserted/updated/deletedij> create view vw2 (c1) as select c3 from tab1;0 rows inserted/updated/deletedij> 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/deletedij> 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/deletedij> create view vw3 (c1, c2) as select c5, tab3.c4 from tab2, tab3 where tab3.c1 > 0;0 rows inserted/updated/deletedij> create view vw4 (c1) as select c4 from tab3 where c2 > 8;0 rows inserted/updated/deletedij> create table tab4 (c1 int, c2 int, c3 int, c4 int);0 rows inserted/updated/deletedij> create table tab5 (c1 int);0 rows inserted/updated/deletedij> insert into tab4 values (1,2,3,4);1 row inserted/updated/deletedij> create trigger tr1 after update of c2, c3, c4 on tab4 for each row mode db2sql insert into tab5 values (1);0 rows inserted/updated/deletedij> create trigger tr2 after update of c3, c4 on tab4 for each row mode db2sql insert into tab5 values (2);0 rows inserted/updated/deletedij> -- tr1 is dropped, tr2 still OKdrop trigger tr1;0 rows inserted/updated/deletedij> select * from tab5;C1 -----------ij> -- fire tr2 onlyupdate tab4 set c3 = 33;1 row inserted/updated/deletedij> update tab4 set c4 = 44;1 row inserted/updated/deletedij> select * from tab5;C1 -----------2 2 ij> -- drop tr2drop trigger tr2;0 rows inserted/updated/deletedij> update tab4 set c4 = 444;1 row inserted/updated/deletedij> select * from tab2;C1 |C2 |C3 |C4 |C5 ----------------------------------------------------------------------ij> drop view vw2;0 rows inserted/updated/deletedij> create view vw2 (c1) as select c3 from tab1;0 rows inserted/updated/deletedij> -- vw1 should be droppeddrop view vw1;0 rows inserted/updated/deletedij> select * from vw1;ERROR 42X05: Table 'VW1' does not exist.ij> -- do the indexes still exist?-- the create index statements should failcreate index i13 on tab1 (c3, c1, c4);0 rows inserted/updated/deletedWARNING 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/deletedWARNING 01504: The new index is a duplicate of an existing index: I14.ij> create unique index i12 on tab1 (c1);0 rows inserted/updated/deletedWARNING 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;⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -