📄 refactions.out
字号:
NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --chain of cascade delete --every table has one depedent table referencing itcreate table t1 (a int not null primary key ) ;0 rows inserted/updated/deletedij> create table t2 (b int not null primary key references t1(a) ON DELETE CASCADE);0 rows inserted/updated/deletedij> create table t3 (c int not null primary key references t2(b) ON DELETE CASCADE) ;0 rows inserted/updated/deletedij> create table t4 (d int not null primary key references t3(c) ON DELETE CASCADE) ;0 rows inserted/updated/deletedij> create table t5 (e int references t4(d) ON DELETE SET NULL) ;0 rows inserted/updated/deletedij> insert into t1 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;8 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;8 rows inserted/updated/deletedij> insert into t3 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;8 rows inserted/updated/deletedij> insert into t4 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;8 rows inserted/updated/deletedij> insert into t5 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;8 rows inserted/updated/deletedij> delete from t1 where a = 5;1 row inserted/updated/deletedij> select * from t1;A -----------1 2 3 4 6 7 8 ij> select * from t2;B -----------1 2 3 4 6 7 8 ij> select * from t3;C -----------1 2 3 4 6 7 8 ij> select * from t4;D -----------1 2 3 4 6 7 8 ij> select * from t5;E -----------1 2 3 4 6 7 8 NULL ij> delete from t1 ;7 rows inserted/updated/deletedij> select * from t1;A -----------ij> select * from t2;B -----------ij> select * from t3;C -----------ij> select * from t4;D -----------ij> select * from t5;E -----------NULL NULL NULL NULL NULL NULL NULL NULL ij> --check the prepared statement cascade deleteinsert into t1 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;8 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;8 rows inserted/updated/deletedij> insert into t3 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;8 rows inserted/updated/deletedij> insert into t4 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;8 rows inserted/updated/deletedij> insert into t5 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;8 rows inserted/updated/deletedij> autocommit off;ij> prepare sdelete as 'delete from t1 where a = ?';ij> execute sdelete using 'values (2)';1 row inserted/updated/deletedij> select * from t1;A -----------1 3 4 5 6 7 8 ij> select * from t2;B -----------1 3 4 5 6 7 8 ij> select * from t3;C -----------1 3 4 5 6 7 8 ij> select * from t4;D -----------1 3 4 5 6 7 8 ij> select * from t5;E -----------1 3 4 5 6 7 8 NULL NULL NULL NULL NULL NULL NULL NULL NULL ij> prepare sdelete1 as 'delete from t2 where b = ?';ij> execute sdelete1 using 'values (3)';1 row inserted/updated/deletedij> --Make sure the ps recompile on a DDL actiondrop table t5 ;0 rows inserted/updated/deletedij> execute sdelete using 'values (5)';1 row inserted/updated/deletedij> execute sdelete1 using 'values (6)';1 row inserted/updated/deletedij> select * from t1;A -----------1 3 4 6 7 8 ij> select * from t2;B -----------1 4 7 8 ij> select * from t3;C -----------1 4 7 8 ij> select * from t4;D -----------1 4 7 8 ij> drop table t4;0 rows inserted/updated/deletedij> drop table t3 ;0 rows inserted/updated/deletedij> execute sdelete using 'values (7)';1 row inserted/updated/deletedij> execute sdelete1 using 'values (8)';1 row inserted/updated/deletedij> select * from t1;A -----------1 3 4 6 8 ij> select * from t2;B -----------1 4 ij> remove sdelete;ij> remove sdelete1;ij> autocommit on;ij> delete from t1 ;5 rows inserted/updated/deletedij> select * from t1;A -----------ij> select * from t2;B -----------ij> drop table t2 ;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --two foreign keys and less number of columns on the dependent table.create table t1( a int not null primary key , b int , c int not null unique) ;0 rows inserted/updated/deletedij> create table t2( x int references t1(c) ON DELETE CASCADE ) ;0 rows inserted/updated/deletedij> create table t3( y int references t1(a) ON DELETE SET NULL ) ;0 rows inserted/updated/deletedij> insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;3 rows inserted/updated/deletedij> insert into t2 values (3) , (6), (9) ;3 rows inserted/updated/deletedij> insert into t3 values (1) , (4) , (7) ;3 rows inserted/updated/deletedij> delete from t1 ;3 rows inserted/updated/deletedij> select * from t1;A |B |C -----------------------------------ij> select * from t2;X -----------ij> select * from t3;Y -----------NULL NULL NULL ij> drop table t3;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --triggers on the dependen tables create table t1( a int not null primary key , b int , c int not null unique) ;0 rows inserted/updated/deletedij> create table t2( x int references t1(c) ON DELETE SET NULL ) ;0 rows inserted/updated/deletedij> create table t3( y int references t1(a) ON DELETE SET NULL) ;0 rows inserted/updated/deletedij> create table t4(z int , op char(2));0 rows inserted/updated/deletedij> --create triggers such a way that the all deleted row--in t2 are inserted into t4create trigger trig_update after UPDATE on t2referencing old as updatedrowfor each row mode db2sqlinsert into t4 values(updatedrow.x , 'bu');0 rows inserted/updated/deletedij> insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;3 rows inserted/updated/deletedij> insert into t2 values (3) , (6), (9) ;3 rows inserted/updated/deletedij> insert into t3 values (1) , (4) , (7) ;3 rows inserted/updated/deletedij> delete from t1 ;3 rows inserted/updated/deletedij> select * from t4;Z |OP ----------------3 |bu 6 |bu 9 |bu ij> select * from t1;A |B |C -----------------------------------ij> select * from t2;X -----------NULL NULL NULL ij> select * from t3;Y -----------NULL NULL NULL ij> drop table t4;0 rows inserted/updated/deletedij> drop table t3;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --test for multiple fkeys on the same table referrring to--different columns on the parent table.create table t1(a int not null unique , b int not null unique);0 rows inserted/updated/deletedij> create table t2(x int references t1(a) ON DELETE SET NULL ,y int);0 rows inserted/updated/deletedij> insert into t1 values(1 , 4) , (2,3) , (3, 2) , (4, 1);4 rows inserted/updated/deletedij> insert into t2 values(1 , 4) , (2,3) , (3, 2) , (4, 1);4 rows inserted/updated/deletedij> delete from t1;4 rows inserted/updated/deletedij> select * from t1;A |B -----------------------ij> select * from t2;X |Y -----------------------NULL |4 NULL |3 NULL |2 NULL |1 ij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> create table t1(a int not null unique , b int not null unique);0 rows inserted/updated/deletedij> create table t2(x int references t1(a) ON DELETE SET NULL ,y int);0 rows inserted/updated/deletedij> insert into t1 values(1 , 4) , (2,3) , (3, 2) , (4, 1);4 rows inserted/updated/deletedij> insert into t2 values(1 , 3) , (2,3) , (3, 4) , (4, 1);4 rows inserted/updated/deletedij> delete from t1 where a =1 ;1 row inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --following is ACTAULL CASCADE DELETE CASEcreate table t1(a int not null unique , b int not null unique);0 rows inserted/updated/deletedij> create table t2(x int references t1(a) ON DELETE CASCADE ,y int references t1(b) ON DELETE CASCADE);0 rows inserted/updated/deletedij> insert into t1 values(1 , 4) , (2,3) , (3, 2) , (4, 1);4 rows inserted/updated/deletedij> insert into t2 values(1 , 3) , (2,3) , (3, 4) , (4, 1);4 rows inserted/updated/deletedij> delete from t1 where a =1 ;1 row inserted/updated/deletedij> --Above delete should delete two rows.drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> create table t1 (a int not null primary key ) ;0 rows inserted/updated/deletedij> create table t2 (b int not null primary key references t1(a) ON DELETE NO ACTION);0 rows inserted/updated/deletedij> insert into t1 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;8 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;8 rows inserted/updated/deletedij> delete from t1;ERROR 23503: DELETE on table 'T1' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (1). The statement has been rolled back.ij> select * from t2;B -----------1 2 3 4 5 6 7 8 ij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --test for DELETE RESTRICT--first check with an after trigger and NO ACTIONcreate table t1(a int not null unique, b int not null unique);0 rows inserted/updated/deletedij> create table t2(x int references t1(a) ON DELETE NO ACTION , y int);0 rows inserted/updated/deletedij> create trigger trig_delete after DELETE on t1referencing old as deletedrowfor each row mode db2sqldelete from t2 where x = deletedrow.a;0 rows inserted/updated/deletedij> insert into t1 values(1 , 2);1 row inserted/updated/deletedij> insert into t1 values(2 , 3);1 row inserted/updated/deletedij> insert into t2 values(1, 2);1 row inserted/updated/deletedij> insert into t2 values(2, 3);1 row inserted/updated/deletedij> -- should fail-- parent row can not be deleted because of a dependent relationship from another tabledelete from t1 where a =1;ERROR 23503: DELETE on table 'T1' caused a violation of foreign key constraint 'xxxxGENERATED-IDxxxx' for key (1). The statement has been rolled back.ij> drop table t2;0 rows inserted/updated/deleted⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -