refactions2.out

derby database source code.good for you.

ij> --Unsupported cases for referential actions , some of these are not supported currently in db2 udb also.
--SQL0632N
--FOREIGN KEY "<name>" is not valid because the table cannot be defined as a dependent of 
--table "<table-name>" because of del--ete rule restrictions (reason code = "<reason-code>").  
--Explanation: A referential constraint cannot be defined because the object table of the CREATE TABLE or 
--ALTER TABLE statement cannot be defined as a dependent of table "<table-name>" for one of the following reason codes: 
--(01) The relationship is self-referencing and a self-referencing relationship already exists
-- with the SET NULL delete rule. 
--(02) 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). 
--(03) 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. 
--The delete rules of the existing relationships cause an error, not the delete rule specified in 
--the FOREIGN KEY clause of the CREATE TABLE or ALTER TABLE statement. 
--sqlcode: -632 
-- sqlstate: 42915 
-- case sql0632-01
create table t1(a int not null primary key , b int references t1(a) ON DELETE SET NULL, 
                c int references t1(a) ON DELETE CASCADE);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The table cannot be defined as a dependent of table APP.T1 because of delete rule restrictions. (The relationship is self-referencing and a self-referencing relationship already exists with the SET NULL delete rule.) '. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE SET NULL, 
                  z int references tself(b) ON DELETE SET NULL);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The table cannot be defined as a dependent of table APP.TSELF because of delete rule restrictions. (The relationship is self-referencing and a self-referencing relationship already exists with the SET NULL delete rule.) '. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE SET NULL, 
                  z int references tself(b) ON DELETE CASCADE);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The table cannot be defined as a dependent of table APP.TSELF because of delete rule restrictions. (The relationship is self-referencing and a self-referencing relationship already exists with the SET NULL delete rule.) '. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE SET NULL, 
                  z int references tself(b) ON DELETE RESTRICT);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The table cannot be defined as a dependent of table APP.TSELF because of delete rule restrictions. (The relationship is self-referencing and a self-referencing relationship already exists with the SET NULL delete rule.) '. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE SET NULL, 
                  z int references tself(b) ON DELETE NO ACTION);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The table cannot be defined as a dependent of table APP.TSELF because of delete rule restrictions. (The relationship is self-referencing and a self-referencing relationship already exists with the SET NULL delete rule.) '. 
ij> -- case sql0632 -02 (c2 fails)
create table t1(a int not null primary key, b int not null unique);
0 rows inserted/updated/deleted
ij> create table t2(x int not null primary key, y int);
0 rows inserted/updated/deleted
ij> alter table t1 add constraint c1 foreign key (b)
                             references t2(x) on delete cascade;
0 rows inserted/updated/deleted
ij> 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> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> -- constraint c4 fails
create table t1( a int not null primary key, b int);
0 rows inserted/updated/deleted
ij> create table t2(x int, y int not null unique);
0 rows inserted/updated/deleted
ij> create table t3(l int, m int not null unique , k int );
0 rows inserted/updated/deleted
ij> alter table t2 add constraint c1 foreign key (x)
                             references t1(a) on delete cascade;
0 rows inserted/updated/deleted
ij> alter table t1 add constraint c2 foreign key (b)
                              references t3(m) on delete cascade;
0 rows inserted/updated/deleted
ij> alter table t3 add constraint c3 foreign key (m)
                              references t2(y) on delete cascade;
0 rows inserted/updated/deleted
ij> alter table t3 add constraint c4 foreign key (k)
                              references t2(y) on delete set null;
ERROR 42915: Foreign  Key 'C4' 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/deleted
ij> alter table t1 drop constraint c2;
0 rows inserted/updated/deleted
ij> alter table t3 drop constraint c3;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> create table t1( a int not null primary key, b int);
0 rows inserted/updated/deleted
ij> create table t2(x int, y int not null unique, z int);
0 rows inserted/updated/deleted
ij> create table t3(l int, m int not null unique , k int );
0 rows inserted/updated/deleted
ij> create table t4(c1 int not null unique , c2 int);
0 rows inserted/updated/deleted
ij> create table t5(c1 int not null unique , c2 int);
0 rows inserted/updated/deleted
ij> create table t6(c1 int not null unique , c2 int);
0 rows inserted/updated/deleted
ij> --delete connected cycle
--different path from t2
alter table t2 add constraint c3 foreign key (z)
                              references t4(c1) on delete cascade;
0 rows inserted/updated/deleted
ij> alter table t4 add constraint c4 foreign key (c2)
                              references t5(c1) on delete cascade;
0 rows inserted/updated/deleted
ij> alter table t5 add constraint c5 foreign key (c2)
                              references t6(c1) on delete cascade;
0 rows inserted/updated/deleted
ij> --cycle forming alter -- c6 should fail
alter table t1 add constraint c1 foreign key (b)
                              references t3(m) on delete cascade;
0 rows inserted/updated/deleted
ij> alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete cascade;
0 rows inserted/updated/deleted
ij> alter table t3 add constraint c6 foreign key (k)
                              references t2(y) on delete SET NULL;
ERROR 42915: Foreign  Key 'C6' is invalid because 'The table cannot be defined as a dependent of table APP.T2 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
ij> alter table t2 drop constraint c2;
0 rows inserted/updated/deleted
ij> alter table t2 drop constraint c3;
0 rows inserted/updated/deleted
ij> alter table t4 drop constraint c4;
0 rows inserted/updated/deleted
ij> alter table t5 drop constraint c5;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table t4;
0 rows inserted/updated/deleted
ij> drop table t5;
0 rows inserted/updated/deleted
ij> drop table t6;
0 rows inserted/updated/deleted
ij> -- case sql0632 - 3 (c2 fails) 
create table t1( a int not null primary key);
0 rows inserted/updated/deleted
ij> create table t2(x int, y int not null unique);
0 rows inserted/updated/deleted
ij> create table t3(l int, m int );
0 rows inserted/updated/deleted
ij> alter table t2 add constraint c1 foreign key (x) 
                             references t1(a) on delete cascade;
0 rows inserted/updated/deleted
ij> alter table t3 add constraint c2 foreign key (l) 
                              references t1(a) on delete set null;
0 rows inserted/updated/deleted
ij> 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/deleted
ij> alter table t3 drop constraint c2;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t3;
0 rows inserted/updated/deleted
ij> --SQL0633N The delete rule of FOREIGN KEY "<name>" must be "<delete-rule>" (reason code = "<reason-code>").  
--Explanation: The delete rule specified in a FOREIGN KEY clause of the CREATE TABLE or ALTER TABLE 
--statement is not valid. The indicated delete rule is required for one of the following reason codes: 
--(01) The referential constraint is self-referencing and an existing self-referencing constraint has the
-- indicated delete rule (NO ACTION, RESTRICT or CASCADE). 
--(02) The referential constraint is self-referencing and the table is dependent in a relationship with
-- a delete rule of CASCADE. 
--(03) 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). 
-- case sql0633-01 (t1 creation should fail)
create table t1(a int not null primary key , b int references t1(a) ON DELETE CASCADE, 
                c int references t1(a) ON DELETE SET NULL);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key must be CASCADE. (The referential constraint is self-referencing and an existing self-referencing constraint has the indicated delete rule (NO ACTION, RESTRICT or CASCADE).)'. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE RESTRICT, 
                  z int references tself(b) ON DELETE CASCADE);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key must be RESTRICT. (The referential constraint is self-referencing and an existing self-referencing constraint has the indicated delete rule (NO ACTION, RESTRICT or CASCADE).)'. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE RESTRICT, 
                  z int references tself(b) ON DELETE NO ACTION);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key must be RESTRICT. (The referential constraint is self-referencing and an existing self-referencing constraint has the indicated delete rule (NO ACTION, RESTRICT or CASCADE).)'. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE RESTRICT, 
                  z int references tself(b) ON DELETE SET NULL);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key must be RESTRICT. (The referential constraint is self-referencing and an existing self-referencing constraint has the indicated delete rule (NO ACTION, RESTRICT or CASCADE).)'. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE NO ACTION, 
                  z int references tself(b) ON DELETE CASCADE);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key must be NO ACTION. (The referential constraint is self-referencing and an existing self-referencing constraint has the indicated delete rule (NO ACTION, RESTRICT or CASCADE).)'. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE NO ACTION, 
                  z int references tself(b) ON DELETE RESTRICT);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key must be NO ACTION. (The referential constraint is self-referencing and an existing self-referencing constraint has the indicated delete rule (NO ACTION, RESTRICT or CASCADE).)'. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE NO ACTION, 
                  z int references tself(b) ON DELETE SET NULL);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key must be NO ACTION. (The referential constraint is self-referencing and an existing self-referencing constraint has the indicated delete rule (NO ACTION, RESTRICT or CASCADE).)'. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE CASCADE, 
                  z int references tself(b) ON DELETE SET NULL);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key must be CASCADE. (The referential constraint is self-referencing and an existing self-referencing constraint has the indicated delete rule (NO ACTION, RESTRICT or CASCADE).)'. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE CASCADE, 
                  z int references tself(b) ON DELETE NO ACTION);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key must be CASCADE. (The referential constraint is self-referencing and an existing self-referencing constraint has the indicated delete rule (NO ACTION, RESTRICT or CASCADE).)'. 
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE CASCADE, 
                  z int references tself(b) ON DELETE RESTRICT);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key must be CASCADE. (The referential constraint is self-referencing and an existing self-referencing constraint has the indicated delete rule (NO ACTION, RESTRICT or CASCADE).)'. 
ij> --FOLLOWING CASES SHOULD PASS
create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE NO ACTION, 
                  z int references tself(b) ON DELETE NO ACTION);
0 rows inserted/updated/deleted
ij> drop table tself;
0 rows inserted/updated/deleted
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE CASCADE, 
                  z int references tself(b) ON DELETE CASCADE);
0 rows inserted/updated/deleted
ij> drop table tself;
0 rows inserted/updated/deleted
ij> create table tself( a int not null primary key, b int not null  unique,
                  x int references tself(a) ON DELETE RESTRICT, 
                  z int references tself(b) ON DELETE RESTRICT);
0 rows inserted/updated/deleted
ij> drop table tself;
0 rows inserted/updated/deleted
ij> -- END PASS CASES