refactions2.out

derby database source code.good for you.

-- case sql0633-02 (t2 fails)
create table t1(a int not null primary key) ;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE CASCADE, 
                y int not null unique, z 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 referential constraint is self-referencing and the table is dependent in a relationship with a delete rule of CASCADE.)'. 
ij> create table t2(x int references t1(a) ON DELETE CASCADE, 
                y int not null unique, z int references t2(y) 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 the table is dependent in a relationship with a delete rule of CASCADE.)'. 
ij> create table t2(x int references t1(a) ON DELETE CASCADE, 
                y int not null unique, z int references t2(y) 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 the table is dependent in a relationship with a delete rule of CASCADE.)'. 
ij> --START  PASS CASES
--These cases is to make sure we don;t throw errors for the valid cases.
create table t2(x int references t1(a) ON DELETE CASCADE, 
                y int not null unique, z int references t2(y) ON DELETE CASCADE);
0 rows inserted/updated/deleted
ij> drop table t2 ;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE RESTRICT, 
                y int not null unique, z int references t2(y) ON DELETE SET NULL);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE RESTRICT, 
                y int not null unique, z int references t2(y) ON DELETE RESTRICT);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE RESTRICT, 
                y int not null unique, z int references t2(y) ON DELETE CASCADE);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE RESTRICT, 
                y int not null unique, z int references t2(y) ON DELETE NO ACTION);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE NO ACTION, 
                y int not null unique, z int references t2(y) ON DELETE CASCADE);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE NO ACTION, 
                y int not null unique, z int references t2(y) ON DELETE NO ACTION);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE NO ACTION, 
                y int not null unique, z int references t2(y) ON DELETE SET NULL);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE NO ACTION, 
                y int not null unique, z int references t2(y) ON DELETE RESTRICT);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE SET NULL, 
                y int not null unique, z int references t2(y) ON DELETE SET NULL);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE SET NULL, 
                y int not null unique, z int references t2(y) ON DELETE RESTRICT);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE SET NULL, 
                y int not null unique, z int references t2(y) ON DELETE NO ACTION);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE SET NULL, 
                y int not null unique, z int references t2(y) ON DELETE CASCADE);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> --END PASS CASES
-- case sql0633-03 (c3 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 cascade;
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 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 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> -- table t3 creation should fail.
create table t1( a int not null primary key);
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE CASCADE, 
                                       y int not null constraint c1 unique);
0 rows inserted/updated/deleted
ij> 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> alter table t2 drop constraint c1;
0 rows inserted/updated/deleted
ij> drop table t1;
ERROR X0Y25: Operation 'DROP CONSTRAINT' cannot be performed on object 'xxxxGENERATED-IDxxxx' because CONSTRAINT 'xxxxGENERATED-IDxxxx' is dependent on that object.
ij> drop table t2;
0 rows inserted/updated/deleted
ij> -- SQL0634N The delete rule of FOREIGN KEY "<name>" must not be CASCADE (reason-code = "<reason-code>").  
-- Explanation: The CASCADE delete rule specified in the FOREIGN KEY clause of the CREATE TABLE 
-- or ALTER TABLE statement is not valid for one of the following reason codes: 
-- (01) A self-referencing constraint exists with a delete rule of SET NULL, NO ACTION or RESTRICT. 
-- (02) 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. 
-- (03) The relationship would cause another table to be delete-connected to the same table through
--  multiple paths with different delete rules or with delete rule equal to SET NULL. 
-- case sql0634 - 01
create table t1( a int not null primary key, b int , c int );
ERROR X0Y32: Table/View 'T1' already exists in Schema 'APP'.
ij> create table t2(x int, y int not null unique);
0 rows inserted/updated/deleted
ij> alter table t1 add constraint c1 foreign key (b) 
                             references t1(a) on delete set null;
ERROR 42X14: 'B' is not a column in table or VTI 'T1'.
ij> alter table t1 add constraint c2 foreign key (c) 
                              references t2(y) on delete cascade;
ERROR 42X14: 'C' is not a column in table or VTI 'T1'.
ij> drop table t1;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> -- t2 should fail
create table t1(a int not null primary key) ;
0 rows inserted/updated/deleted
ij> create table t2(x int not null unique, y int references t2(x) ON DELETE SET NULL, 
       		                       z int references t1(a) ON DELETE CASCADE);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key cannot be CASCADE. (A self-referencing constraint exists with a delete rule of SET NULL, NO ACTION or RESTRICT.) '. 
ij> create table t2(x int not null unique, y int references t2(x) ON DELETE NO ACTION, 
       		                       z int references t1(a) ON DELETE CASCADE);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key cannot be CASCADE. (A self-referencing constraint exists with a delete rule of SET NULL, NO ACTION or RESTRICT.) '. 
ij> create table t2(x int not null unique, y int references t2(x) ON DELETE RESTRICT, 
       		                       z int references t1(a) ON DELETE CASCADE);
ERROR 42915: Foreign  Key 'xxxxGENERATED-IDxxxx' is invalid because 'The delete rule of foreign key cannot be CASCADE. (A self-referencing constraint exists with a delete rule of SET NULL, NO ACTION or RESTRICT.) '. 
ij> --START  SHOULD PASS CASES
create table t2(x int not null unique, y int references t2(x) ON DELETE CASCADE, 
       		                       z int references t1(a) ON DELETE SET NULL);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int not null unique, y int references t2(x) ON DELETE CASCADE, 
       		                       z int references t1(a) ON DELETE NO ACTION);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int not null unique, y int references t2(x) ON DELETE CASCADE, 
       		                       z int references t1(a) ON DELETE RESTRICT);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int not null unique, y int references t2(x) ON DELETE CASCADE, 
       		                       z int references t1(a) ON DELETE CASCADE);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int not null unique, y int references t2(x) ON DELETE RESTRICT, 
       		                       z int references t1(a) ON DELETE SET NULL);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int not null unique, y int references t2(x) ON DELETE RESTRICT, 
       		                       z int references t1(a) ON DELETE NO ACTION);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int not null unique, y int references t2(x) ON DELETE RESTRICT, 
       		                       z int references t1(a) ON DELETE RESTRICT);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int not null unique, y int references t2(x) ON DELETE NO ACTION, 
       		                       z int references t1(a) ON DELETE SET NULL);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int not null unique, y int references t2(x) ON DELETE NO ACTION, 
       		                       z int references t1(a) ON DELETE RESTRICT);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> create table t2(x int not null unique, y int references t2(x) ON DELETE NO ACTION, 
       		                       z int references t1(a) ON DELETE NO ACTION);
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> --END PASS CASES
drop table t1;
0 rows inserted/updated/deleted
ij> -- case sql0634 - 02 (c1 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 t2 add constraint c2 foreign key (y)
                              references t1(b) on delete set null;
0 rows inserted/updated/deleted
ij> alter table t1 add constraint c1 foreign key (b)
                             references t2(x) on delete cascade;
ERROR 42915: Foreign  Key 'C1' 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 t2 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> -- case sql0634 - 03 
create table t1( a int not null primary key, b int);