📄 refactions.out
字号:
1 row inserted/updated/deletedij> insert into t2 values(null) ;1 row inserted/updated/deletedij> insert into t2 values(null) ;1 row inserted/updated/deletedij> insert into t2 values(null) ;1 row inserted/updated/deletedij> insert into t2 values(null) ;1 row inserted/updated/deletedij> insert into t2 values(null) ;1 row inserted/updated/deletedij> insert into t2 values(null) ;1 row inserted/updated/deletedij> select * from t1 ;A -----------0 1 2 ij> select * from t2 ;B -----------NULL NULL NULL NULL NULL NULL NULL ij> delete from t1 where a = 0 ;1 row inserted/updated/deletedij> select * from t1 ;A -----------1 2 ij> -- null values from t1 are not deletedselect * from t2 ;B -----------NULL NULL NULL NULL NULL NULL NULL ij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --self ref foreign key without null valuescreate table t1( a int not null unique , b int references t1(a) ON DELETE SET NULL);0 rows inserted/updated/deletedij> insert into t1 values ( 1 , null) ;1 row inserted/updated/deletedij> delete from t1 where b is null ;1 row inserted/updated/deletedij> select * from t1 ;A |B -----------------------ij> drop table t1 ;0 rows inserted/updated/deletedij> create table t1( a int not null unique , b int references t1(a) ON DELETE CASCADE);0 rows inserted/updated/deletedij> insert into t1 values ( 1 , null) ;1 row inserted/updated/deletedij> insert into t1 values ( 0 , 1) ;1 row inserted/updated/deletedij> delete from t1 where b is null ;1 row inserted/updated/deletedij> select * from t1 ;A |B -----------------------ij> drop table t1 ;0 rows inserted/updated/deletedij> --mutiple tablescreate table parent( a int not null unique) ;0 rows inserted/updated/deletedij> create table child1(b int not null unique references parent(a)ON DELETE CASCADE);0 rows inserted/updated/deletedij> create table child2(c int not null unique references child1(b)ON DELETE CASCADE);0 rows inserted/updated/deletedij> insert into parent values(0) ;1 row inserted/updated/deletedij> insert into parent values(1) ;1 row inserted/updated/deletedij> insert into parent values(2) ;1 row inserted/updated/deletedij> insert into child1 values(0) ;1 row inserted/updated/deletedij> insert into child1 values(1) ;1 row inserted/updated/deletedij> insert into child1 values(2) ;1 row inserted/updated/deletedij> insert into child2 values(0) ;1 row inserted/updated/deletedij> insert into child2 values(1) ;1 row inserted/updated/deletedij> insert into child2 values(2) ;1 row inserted/updated/deletedij> select * from parent ;A -----------0 1 2 ij> select * from child1;B -----------0 1 2 ij> select * from child2 ;C -----------0 1 2 ij> delete from parent where a = 1 ;1 row inserted/updated/deletedij> select * from parent ;A -----------0 2 ij> select * from child1;B -----------0 2 ij> select * from child2 ;C -----------0 2 ij> delete from parent where a = 0 ;1 row inserted/updated/deletedij> select * from parent ;A -----------2 ij> select * from child1;B -----------2 ij> --delete all the rowsdelete from parent;1 row inserted/updated/deletedij> drop table child2;0 rows inserted/updated/deletedij> create table child2(c int references child1(b)ON DELETE SET NULL);0 rows inserted/updated/deletedij> insert into parent values(0) ;1 row inserted/updated/deletedij> insert into parent values(1) ;1 row inserted/updated/deletedij> insert into parent values(2) ;1 row inserted/updated/deletedij> insert into child1 values(0) ;1 row inserted/updated/deletedij> insert into child1 values(1) ;1 row inserted/updated/deletedij> insert into child1 values(2) ;1 row inserted/updated/deletedij> insert into child2 values(null) ;1 row inserted/updated/deletedij> insert into child2 values(1) ;1 row inserted/updated/deletedij> insert into child2 values(2) ;1 row inserted/updated/deletedij> select * from parent ;A -----------0 1 2 ij> select * from child1;B -----------0 1 2 ij> select * from child2 ;C -----------1 2 NULL ij> delete from parent where a = 1 ;1 row inserted/updated/deletedij> select * from parent ;A -----------0 2 ij> select * from child1;B -----------0 2 ij> select * from child2;C -----------2 NULL NULL ij> delete from parent where a = 0;1 row inserted/updated/deletedij> select * from parent ;A -----------2 ij> select * from child1;B -----------2 ij> select * from child2;C -----------2 NULL NULL ij> delete from child2 where c is null;2 rows inserted/updated/deletedij> delete from child2 where c is not null;1 row inserted/updated/deletedij> delete from parent where a = 2 ;1 row inserted/updated/deletedij> select * from parent ;A -----------ij> select * from child1;B -----------ij> select * from child2;C -----------ij> delete from parent;0 rows inserted/updated/deletedij> delete from child1;0 rows inserted/updated/deletedij> delete from child2;0 rows inserted/updated/deletedij> drop table child2;0 rows inserted/updated/deletedij> drop table child1;0 rows inserted/updated/deletedij> drop table parent;0 rows inserted/updated/deletedij> --foreign key on two non-nullable unique keyscreate table t1(a int not null unique , b int not null unique) ;0 rows inserted/updated/deletedij> alter table t1 add constraint c2 unique(a , b ) ;0 rows inserted/updated/deletedij> create table t2( x1 int , x2 int , constraint c1 foreign key (x1, x2)references t1(a , b ) ON DELETE CASCADE ) ;0 rows inserted/updated/deletedij> insert into t1 values (0 , 1) ;1 row inserted/updated/deletedij> insert into t1 values (1 , 2) ;1 row inserted/updated/deletedij> insert into t2 values (0 , 1) ;1 row inserted/updated/deletedij> insert into t2 values (1 , 2) ;1 row inserted/updated/deletedij> delete from t1 where a = 0;1 row inserted/updated/deletedij> select * from t1 ;A |B -----------------------1 |2 ij> select * from t2 ;X1 |X2 -----------------------1 |2 ij> insert into t1 values (0 , 0) ;1 row inserted/updated/deletedij> insert into t2 values (0 , 0) ;1 row inserted/updated/deletedij> delete from t1 where a = 0;1 row inserted/updated/deletedij> select * from t1 ;A |B -----------------------1 |2 ij> select * from t2 ;X1 |X2 -----------------------1 |2 ij> delete from t1;1 row inserted/updated/deletedij> drop table t2 ;0 rows inserted/updated/deletedij> create table t2( x1 int , x2 int , constraint c1 foreign key (x1, x2)references t1(a , b ) ON DELETE SET NULL ) ;0 rows inserted/updated/deletedij> insert into t1 values (0 , 1) ;1 row inserted/updated/deletedij> insert into t1 values (1 , 2) ;1 row inserted/updated/deletedij> insert into t2 values (0 , 1) ;1 row inserted/updated/deletedij> insert into t2 values (1 , 2) ;1 row inserted/updated/deletedij> select * from t1 ;A |B -----------------------0 |1 1 |2 ij> select * from t2 ;X1 |X2 -----------------------0 |1 1 |2 ij> delete from t1 where a = 0;1 row inserted/updated/deletedij> select * from t1 ;A |B -----------------------1 |2 ij> select * from t2 ;X1 |X2 -----------------------1 |2 NULL |NULL ij> drop table t2 ;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --cyclic non-nulls casecreate table t1(a int not null unique, b int not null unique);0 rows inserted/updated/deletedij> create table t2(x int not null unique, y int not null unique);0 rows inserted/updated/deletedij> insert into t1 values (0, 2);1 row inserted/updated/deletedij> insert into t1 values (2, 0);1 row inserted/updated/deletedij> insert into t2 values (0, 2);1 row inserted/updated/deletedij> insert into t2 values (2, 0);1 row inserted/updated/deletedij> insert into t1 values (3, 4);1 row inserted/updated/deletedij> insert into t1 values (4, 3);1 row inserted/updated/deletedij> insert into t2 values (3, 4);1 row inserted/updated/deletedij> insert into t2 values (4, 3);1 row inserted/updated/deletedij> insert into t1 values (6, 7);1 row inserted/updated/deletedij> insert into t1 values (7, 6);1 row inserted/updated/deletedij> insert into t2 values (6, 7);1 row inserted/updated/deletedij> insert into t2 values (7, 6);1 row inserted/updated/deletedij> insert into t1 values (9, 10);1 row inserted/updated/deletedij> insert into t1 values (10, 9);1 row inserted/updated/deletedij> insert into t2 values (9, 10);1 row inserted/updated/deletedij> insert into t2 values (10, 9);1 row inserted/updated/deletedij> alter table t1 add constraint c1 foreign key (b) references t2(x) on delete cascade;0 rows inserted/updated/deletedij> alter table t2 add constraint c2 foreign key (y) references t1(b) on delete cascade;0 rows inserted/updated/deletedij> select * from t1;A |B -----------------------0 |2 2 |0 3 |4 4 |3 6 |7 7 |6 9 |10 10 |9 ij> select * from t2;X |Y -----------------------0 |2 2 |0 3 |4 4 |3 6 |7 7 |6 9 |10 10 |9 ij> delete from t1 where a = 0 ;1 row inserted/updated/deletedij> select * from t1;A |B -----------------------3 |4 4 |3 6 |7 7 |6 9 |10 10 |9 ij> select * from t2;X |Y -----------------------3 |4 4 |3 6 |7 7 |6 9 |10 10 |9 ij> delete from t2 where x=3 ;1 row inserted/updated/deletedij> select * from t1;A |B -----------------------6 |7 7 |6 9 |10 10 |9 ij> select * from t2;X |Y -----------------------6 |7 7 |6 9 |10 10 |9 ij> delete from t1 where b = 9;1 row inserted/updated/deletedij> select * from t1;A |B -----------------------6 |7 7 |6 ij> select * from t2;X |Y -----------------------6 |7 7 |6 ij> delete from t2;2 rows inserted/updated/deletedij> select * from t1;A |B -----------------------ij> select * from t2;X |Y -----------------------ij> alter table t1 drop constraint c1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --END OF NULL CHECK--BEGIN NON NULL ERROR CHECK FOR ON DELETE SET NULL--do not allow ON DELETE SET NULL on non nullable foreign key column⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -