refactions2.out

derby database source code.good for you.

ij> alter table t2 drop constraint c2;
ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.C2' on table 'APP.T2'. 
ij> --c1 fails - sql0634N - reason code 2
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 t1 drop constraint c1;
ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.C1' on table 'APP.T1'. 
ij> alter table t2 drop constraint c2;
0 rows inserted/updated/deleted
ij> -- c1 fails : column b can not contain null values
alter table t1 add constraint c1 foreign key (b)
                             references t2(x) on delete NO ACTION;
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 drop constraint c1;
0 rows inserted/updated/deleted
ij> alter table t2 drop constraint c2;
0 rows inserted/updated/deleted
ij> drop table t2;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> -- should pass
create table t1(a int not null unique, b int not null unique);
0 rows inserted/updated/deleted
ij> create table t3(l int unique not null  , y int);
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE CASCADE ,
                y int references t3(l) ON DELETE RESTRICT);
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 t1;
0 rows inserted/updated/deleted
ij> --creating t2 should fail
create table t1(a int not null unique, b int not null unique);
0 rows inserted/updated/deleted
ij> create table t3(l int unique not null  ,
               y int references t1(b) ON DELETE CASCADE);
0 rows inserted/updated/deleted
ij> create table t2(x int references t1(a) ON DELETE CASCADE ,
                y int references t3(l) ON DELETE RESTRICT);
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 t2;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T2' because it does not exist.
ij> drop table t3;
0 rows inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> -- cyclic references
-- t1 refs  t3 refs t2 refs t1
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> insert into t1  values (1  , 1) ;
1 row inserted/updated/deleted
ij> insert into t2 values ( 1 , 1) ;
1 row inserted/updated/deleted
ij> insert into t3 values (1 , 1, 1) ;
1 row inserted/updated/deleted
ij> --delete connected cycle 
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 c3 foreign key (m)
                              references t2(y) on delete cascade;
0 rows inserted/updated/deleted
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 t3 drop constraint c3;
0 rows inserted/updated/deleted
ij> --c3 should fail SQL0632N - 2
--delete connected cycle all refactions inside the cycle should be same
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 c3 foreign key (k)
                              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 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 t3 drop constraint c3;
ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.C3' on table 'APP.T3'. 
ij> --c3 should fail SQL0634N - 2 -- PROBLEMATIC CASE
-- DELETE CONNECTED CYCLE
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 set null;
0 rows inserted/updated/deleted
ij> alter table t3 add constraint c3 foreign key (k)
                              references t2(y) on delete cascade;
ERROR 42915: Foreign  Key 'C3' 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/deleted
ij> alter table t2 drop constraint c2;
0 rows inserted/updated/deleted
ij> alter table t3 drop constraint c3;
ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.C3' on table 'APP.T3'. 
ij> --c3 should fail - SQL0634N - 2
--DELETE CONNECTED CYCLE
alter table t1 add constraint c1 foreign key (b)
                              references t3(m) on delete set null;
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 c3 foreign key (k)
                              references t2(y) on delete cascade;
ERROR 42915: Foreign  Key 'C3' 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/deleted
ij> alter table t2 drop constraint c2;
0 rows inserted/updated/deleted
ij> alter table t3 drop constraint c3;
ERROR 42X86: ALTER TABLE failed. There is no constraint 'APP.C3' on table 'APP.T3'. 
ij> -- passes
alter table t1 add constraint c1 foreign key (b)
                              references t3(m) on delete set null;
0 rows inserted/updated/deleted
ij> alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete set null;
0 rows inserted/updated/deleted
ij> alter table t3 add constraint c3 foreign key (k)
                              references t2(y) on delete cascade;
0 rows inserted/updated/deleted
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 t3 drop constraint c3;
0 rows inserted/updated/deleted
ij> --passes
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 set null;
0 rows inserted/updated/deleted
ij> alter table t3 add constraint c3 foreign key (k)
                              references t2(y) on delete set null;
0 rows inserted/updated/deleted
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 t3 drop constraint c3;
0 rows inserted/updated/deleted
ij> --passes
alter table t1 add constraint c1 foreign key (b)
                              references t3(m) on delete set null;
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 c3 foreign key (k)
                              references t2(y) on delete set null;
0 rows inserted/updated/deleted
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 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> -- self referencing errors
create table tself(a int not null primary key , 
               b int references tself(a) ON DELETE SET NULL,  
               c int references tself(a) 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 references tself(a) ON DELETE CASCADE,  
               c int references tself(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 references tself(a) ON DELETE SET NULL,  
               c int references tself(a) 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 references tself(a) ,  
               c int references tself(a) 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 tparent( a int not null  primary key);
0 rows inserted/updated/deleted
ij> --THIS ONE SHOULD PASS , but currently we are throwing ERRROR
create table tself(a int not null primary key , 
               b int references tparent(a) ON DELETE SET NULL ,  
               c int references tself(a) ON DELETE CASCADE);
0 rows inserted/updated/deleted
ij> drop table tself;
0 rows inserted/updated/deleted
ij> --should pass
create table tself(a int not null primary key , 
               b int references tparent(a) ON DELETE CASCADE ,  
               c int references tself(a) ON DELETE CASCADE);
0 rows inserted/updated/deleted
ij> drop table tself;
0 rows inserted/updated/deleted
ij> --should throw error
create table tself(a int not null primary key , 
               b int references tparent(a) ON DELETE CASCADE ,  
               c int references tself(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 the table is dependent in a relationship with a delete rule of CASCADE.)'. 
ij> drop table tself;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TSELF' b