📄 refactions2.out
字号:
0 rows inserted/updated/deletedij> drop table t4;0 rows inserted/updated/deletedij> --- END OF ACTUAL ERROR CASES--- MISC CASES --Following should give error because of delete-rule restrictionscreate table t1( a int not null primary key);0 rows inserted/updated/deletedij> create table t2(x int references t1(a) ON DELETE CASCADE, y int not null unique);0 rows inserted/updated/deletedij> create table t3(l int references t1(a) ON DELETE CASCADE , m int references t2(y) ON DELETE SET NULL);ERROR 42915: Foreign Key 'xxxxGENERATED-IDxxxx' is invalid because 'the delete rule of foreign key must be CASCADE. (The relationship would cause the table to be delete-connected to the same table through multiple relationships and such relationships must have the same delete rule (NO ACTION, RESTRICT or CASCADE).) '. ij> drop table t3 ;ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T3' because it does not exist.ij> drop table t2 ;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --DB21034E The command was processed as an SQL statement because it was not a--valid Command Line Processor command. During SQL processing it returned:--SQL0633N The delete rule of FOREIGN KEY "M..." must be "CASCADE" (reason code--= "3"). SQLSTATE=42915create table t1( a int not null primary key);0 rows inserted/updated/deletedij> create table t2(x int references t1(a) ON DELETE SET NULL, y int not null unique);0 rows inserted/updated/deletedij> create table t3(l int references t1(a) ON DELETE CASCADE , m int references t2(y) ON DELETE SET NULL);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> --Following should pass.create table t1( a int not null primary key);0 rows inserted/updated/deletedij> create table t4(s int not null unique);0 rows inserted/updated/deletedij> create table t2(x int references t4(s) ON DELETE CASCADE, y int not null unique);0 rows inserted/updated/deletedij> create table t3(l int references t1(a) ON DELETE CASCADE , m int references t2(y) ON DELETE SET NULL);0 rows inserted/updated/deletedij> drop table t3;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table t4;0 rows inserted/updated/deletedij> drop table t1;0 rows inserted/updated/deletedij> --Following should give error because of delete-rule restrictionscreate table t1( a int not null primary key);0 rows inserted/updated/deletedij> create table t2(x int, y int not null unique);0 rows inserted/updated/deletedij> create table t3(l int, m int );0 rows inserted/updated/deletedij> -- all should passalter table t2 add constraint c1 foreign key (x) references t1(a) on delete cascade;0 rows inserted/updated/deletedij> alter table t3 add constraint c2 foreign key (l) references t1(a) on delete cascade;0 rows inserted/updated/deletedij> alter table t3 add constraint c3 foreign key (m) references t2(y) on delete cascade;0 rows inserted/updated/deletedij> alter table t2 drop constraint c1;0 rows inserted/updated/deletedij> alter table t3 drop constraint c2;0 rows inserted/updated/deletedij> alter table t3 drop constraint c3;0 rows inserted/updated/deletedij> -- c3 fails: sql0633N - 3alter table t2 add constraint c1 foreign key (x) references t1(a) on delete cascade;0 rows inserted/updated/deletedij> alter table t3 add constraint c2 foreign key (l) references t1(a) on delete cascade;0 rows inserted/updated/deletedij> alter table t3 add constraint c3 foreign key (m) references t2(y) on delete set null;ERROR 42915: Foreign Key 'C3' is invalid because 'the delete rule of foreign key must be CASCADE. (The relationship would cause the table to be delete-connected to the same table through multiple relationships and such relationships must have the same delete rule (NO ACTION, RESTRICT or CASCADE).) '. ij> alter table t2 drop constraint c1;0 rows inserted/updated/deletedij> alter table t3 drop constraint c2;0 rows inserted/updated/deletedij> alter table t3 drop constraint c3;ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.C3' on table 'APP.T3'. ij> -- c3 fails; sql0632N - 3 alter table t2 add constraint c1 foreign key (x) references t1(a) on delete CASCADE;0 rows inserted/updated/deletedij> alter table t3 add constraint c2 foreign key (l) references t1(a) on delete set null;0 rows inserted/updated/deletedij> alter table t3 add constraint c3 foreign key (m) references t2(y) on delete cascade;ERROR 42915: Foreign Key 'C3' is invalid because 'The table cannot be defined as a dependent of table APP.T2 because of delete rule restrictions. (The relationship causes the table to be delete-connected to the indicated table through multiple relationships and the delete rule of the existing relationship is SET NULL.). '. ij> alter table t2 drop constraint c1;0 rows inserted/updated/deletedij> alter table t3 drop constraint c2;0 rows inserted/updated/deletedij> alter table t3 drop constraint c3;ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.C3' on table 'APP.T3'. ij> -- passesalter table t2 add constraint c1 foreign key (x) references t1(a) on delete set null;0 rows inserted/updated/deletedij> alter table t3 add constraint c2 foreign key (l) references t1(a) on delete cascade;0 rows inserted/updated/deletedij> alter table t3 add constraint c3 foreign key (m) references t2(y) on delete cascade;0 rows inserted/updated/deletedij> alter table t2 drop constraint c1;0 rows inserted/updated/deletedij> alter table t3 drop constraint c2;0 rows inserted/updated/deletedij> alter table t3 drop constraint c3;0 rows inserted/updated/deletedij> -- succedsalter table t2 add constraint c1 foreign key (x) references t1(a) on delete set null;0 rows inserted/updated/deletedij> alter table t3 add constraint c2 foreign key (l) references t1(a) on delete set null;0 rows inserted/updated/deletedij> alter table t3 add constraint c3 foreign key (m) references t2(y) on delete set null;0 rows inserted/updated/deletedij> alter table t2 drop constraint c1;0 rows inserted/updated/deletedij> alter table t3 drop constraint c2;0 rows inserted/updated/deletedij> alter table t3 drop constraint c3;0 rows inserted/updated/deletedij> -- succedsalter table t2 add constraint c1 foreign key (x) references t1(a) on delete set null;0 rows inserted/updated/deletedij> alter table t3 add constraint c2 foreign key (l) references t1(a) on delete set null;0 rows inserted/updated/deletedij> alter table t3 add constraint c3 foreign key (m) references t2(y) on delete cascade;0 rows inserted/updated/deletedij> alter table t2 drop constraint c1;0 rows inserted/updated/deletedij> alter table t3 drop constraint c2;0 rows inserted/updated/deletedij> alter table t3 drop constraint c3;0 rows inserted/updated/deletedij> -- passes alter table t2 add constraint c1 foreign key (x) references t1(a) on delete set null;0 rows inserted/updated/deletedij> alter table t3 add constraint c2 foreign key (l) references t1(a) on delete cascade;0 rows inserted/updated/deletedij> alter table t3 add constraint c3 foreign key (m) references t2(y) on delete set null;0 rows inserted/updated/deletedij> alter table t2 drop constraint c1;0 rows inserted/updated/deletedij> alter table t3 drop constraint c2;0 rows inserted/updated/deletedij> alter table t3 drop constraint c3;0 rows inserted/updated/deletedij> -- c3 fails - sql0632 - 3alter table t2 add constraint c1 foreign key (x) references t1(a) on delete cascade;0 rows inserted/updated/deletedij> alter table t3 add constraint c2 foreign key (l) references t1(a) on delete set null;0 rows inserted/updated/deletedij> alter table t3 add constraint c3 foreign key (m) references t2(y) on delete set null;ERROR 42915: Foreign Key 'C3' is invalid because 'The table cannot be defined as a dependent of table APP.T2 because of delete rule restrictions. (The relationship causes the table to be delete-connected to the indicated table through multiple relationships and the delete rule of the existing relationship is SET NULL.). '. ij> alter table t2 drop constraint c1;0 rows inserted/updated/deletedij> alter table t3 drop constraint c2;0 rows inserted/updated/deletedij> alter table t3 drop constraint c3;ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.C3' on table 'APP.T3'. ij> drop table t1;0 rows inserted/updated/deletedij> drop table t2;0 rows inserted/updated/deletedij> drop table t3;0 rows inserted/updated/deletedij> --cyclic case with two tables.create table t1(a int not null primary key, b int not null unique);0 rows inserted/updated/deletedij> create table t2(x int not null primary key, y int);0 rows inserted/updated/deletedij> --passesalter 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> alter table t1 drop constraint c1;0 rows inserted/updated/deletedij> alter table t2 drop constraint c2;0 rows inserted/updated/deletedij> alter table t1 add constraint c1 foreign key (b) references t2(x) on delete NO ACTION;0 rows inserted/updated/deletedij> alter table t2 add constraint c2 foreign key (y) references t1(b) on delete cascade;ERROR 42915: Foreign Key 'C2' is invalid because 'The delete rule of foreign key cannot be CASCADE. (The relationship would form a cycle that would cause a table to be delete-connected to itself. One of the existing delete rules in the cycle is not CASCADE, so this relationship may be definable if the delete rule is not CASCADE.) '. ij> alter table t1 drop constraint c1;0 rows inserted/updated/deletedij> alter table t2 drop constraint c2;ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.C2' on table 'APP.T2'. ij> --c2 fails - sql0632N - reason code 2alter 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 set null;ERROR 42915: Foreign Key 'C2' is invalid because 'The table cannot be defined as a dependent of table APP.T1 because of delete rule restrictions. (The relationship forms a cycle of two or more tables that cause the table to be delete-connected to itself (all other delete rules in the cycle would be CASCADE)). '. ij> alter table t1 drop constraint c1;0 rows inserted/updated/deleted⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -