📄 refactions.out
字号:
ij> --no cascade delete , just default checkcreate table t1(a int not null primary key);0 rows inserted/updated/deletedij> create table t2(b int references t1(a));0 rows inserted/updated/deletedij> insert into t1 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 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> drop table t2;0 rows inserted/updated/deletedij> --simple cascade deletecreate table t2(b int references t1(a) ON DELETE CASCADE);0 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> delete from t1 where a =2 ;1 row inserted/updated/deletedij> select * from t2;B -----------1 3 4 ij> delete from t1 ;3 rows inserted/updated/deletedij> select * from t2;B -----------ij> --multiple rows in the dependent table for a single row in the parentinsert into t1 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> delete from t1 where a = 3 ;1 row inserted/updated/deletedij> select * from t1;A -----------1 2 4 ij> delete from t1;3 rows inserted/updated/deletedij> select * from t2;B -----------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 not null primary key references t4(d) ON DELETE CASCADE) ;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 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 -----------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 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 CASCADE ) ;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 -----------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 CASCADE ) ;0 rows inserted/updated/deletedij> create table t3( y int references t1(a) ON DELETE CASCADE) ;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_delete after DELETE on t2referencing old as deletedrowfor each row mode db2sqlinsert into t4 values(deletedrow.x , 'bd');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 |bd 6 |bd 9 |bd ij> select * from t1;A |B |C -----------------------------------ij> select * from t2;X -----------ij> select * from t3;Y -----------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 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 , 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 -----------------------ij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --check for the unique nulls case--check for sefl referencing---ON DELETE SET NULL TEST CASES--simple cascade delete set to nullcreate table t1(a int not null primary key);0 rows inserted/updated/deletedij> create table t2(b int references t1(a) ON DELETE SET NULL);0 rows inserted/updated/deletedij> insert into t1 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> delete from t1 where a =2 ;1 row inserted/updated/deletedij> select * from t2;B -----------1 3 4 NULL ij> delete from t1 ;3 rows inserted/updated/deletedij> select * from t2;B -----------NULL NULL NULL NULL ij> --multiple rows in the dependent table for a single row in the parentinsert into t1 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> insert into t2 values (1) , (2) , (3) , (4);4 rows inserted/updated/deletedij> delete from t1 where a = 3 ;1 row inserted/updated/deletedij> select * from t1;A -----------1 2 4 ij> delete from t1;3 rows inserted/updated/deletedij> select * from t2;B -----------NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -