refactions2.sql

derby database source code.good for you.

alter table t3 add constraint c3 foreign key (m) 
                              references t2(y) on delete set null;

alter table t2 drop constraint c1;
alter table t3 drop constraint c2;
alter table t3 drop constraint c3;


-- c3 fails - sql0632 - 3
alter table t2 add constraint c1 foreign key (x) 
                             references t1(a) on delete cascade;
alter table t3 add constraint c2 foreign key (l) 
                              references t1(a) on delete set null;
alter table t3 add constraint c3 foreign key (m) 
                              references t2(y) on delete set null;

alter table t2 drop constraint c1;
alter table t3 drop constraint c2;
alter table t3 drop constraint c3;

drop table t1;
drop table t2;
drop table t3;

--cyclic case with two tables.
create table t1(a int not null primary key, b int not null unique);
create table t2(x int not null primary key, y int);

--passes
alter table t1 add constraint c1 foreign key (b)
                             references t2(x) on delete cascade;
alter table t2 add constraint c2 foreign key (y)
                              references t1(b) on delete cascade;

alter table t1 drop constraint c1;
alter table t2 drop constraint c2;


alter table t1 add constraint c1 foreign key (b)
                             references t2(x) on delete NO ACTION;
alter table t2 add constraint c2 foreign key (y)
                              references t1(b) on delete cascade;

alter table t1 drop constraint c1;
alter table t2 drop constraint c2;

--c2 fails - sql0632N - reason code 2
alter table t1 add constraint c1 foreign key (b)
                             references t2(x) on delete cascade;
alter table t2 add constraint c2 foreign key (y)
                              references t1(b) on delete set null;

alter table t1 drop constraint c1;
alter table t2 drop constraint c2;

--c1 fails - sql0634N - reason code 2
alter table t2 add constraint c2 foreign key (y)
                              references t1(b) on delete set null;
alter table t1 add constraint c1 foreign key (b)
                             references t2(x) on delete cascade;

alter table t1 drop constraint c1;
alter table t2 drop constraint c2;

-- 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;
alter table t2 add constraint c2 foreign key (y)
                              references t1(b) on delete set null;

alter table t1 drop constraint c1;
alter table t2 drop constraint c2;


drop table t2;
drop table t1;

-- should pass
create table t1(a int not null unique, b int not null unique);
create table t3(l int unique not null  , y int);
create table t2(x int references t1(a) ON DELETE CASCADE ,
                y int references t3(l) ON DELETE RESTRICT);

drop table t2;
drop table t3;
drop table t1;


--creating t2 should fail
create table t1(a int not null unique, b int not null unique);
create table t3(l int unique not null  ,
               y int references t1(b) ON DELETE CASCADE);
create table t2(x int references t1(a) ON DELETE CASCADE ,
                y int references t3(l) ON DELETE RESTRICT);

drop table t2;
drop table t3;
drop table t1;



-- cyclic references
-- t1 refs  t3 refs t2 refs t1
create table t1( a int not null primary key, b int);
create table t2(x int, y int not null unique);
create table t3(l int, m int not null unique , k int );

insert into t1  values (1  , 1) ;
insert into t2 values ( 1 , 1) ;
insert into t3 values (1 , 1, 1) ;

--delete connected cycle 
alter table t1 add constraint c1 foreign key (b)
                              references t3(m) on delete cascade;
alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete cascade;
alter table t3 add constraint c3 foreign key (m)
                              references t2(y) on delete cascade;

alter table t1 drop constraint c1;
alter table t2 drop constraint c2;
alter table t3 drop constraint c3;

--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;
alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete cascade;
alter table t3 add constraint c3 foreign key (k)
                              references t2(y) on delete set null;


alter table t1 drop constraint c1;
alter table t2 drop constraint c2;
alter table t3 drop constraint c3;

--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;
alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete set null;
alter table t3 add constraint c3 foreign key (k)
                              references t2(y) on delete cascade;


alter table t1 drop constraint c1;
alter table t2 drop constraint c2;
alter table t3 drop constraint c3;

--c3 should fail - SQL0634N - 2
--DELETE CONNECTED CYCLE
alter table t1 add constraint c1 foreign key (b)
                              references t3(m) on delete set null;
alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete cascade;
alter table t3 add constraint c3 foreign key (k)
                              references t2(y) on delete cascade;


alter table t1 drop constraint c1;
alter table t2 drop constraint c2;
alter table t3 drop constraint c3;


-- passes
alter table t1 add constraint c1 foreign key (b)
                              references t3(m) on delete set null;
alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete set null;
alter table t3 add constraint c3 foreign key (k)
                              references t2(y) on delete cascade;

alter table t1 drop constraint c1;
alter table t2 drop constraint c2;
alter table t3 drop constraint c3;

--passes
alter table t1 add constraint c1 foreign key (b)
                              references t3(m) on delete cascade;
alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete set null;
alter table t3 add constraint c3 foreign key (k)
                              references t2(y) on delete set null;

alter table t1 drop constraint c1;
alter table t2 drop constraint c2;
alter table t3 drop constraint c3;

--passes
alter table t1 add constraint c1 foreign key (b)
                              references t3(m) on delete set null;
alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete cascade;
alter table t3 add constraint c3 foreign key (k)
                              references t2(y) on delete set null;

alter table t1 drop constraint c1;
alter table t2 drop constraint c2;
alter table t3 drop constraint c3;

drop table t1 ;
drop table t2 ;
drop table t3 ;


-- 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);

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);

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);

create table tself(a int not null primary key , 
               b int references tself(a) ,  
               c int references tself(a) ON DELETE CASCADE);

create table tparent( a int not null  primary key);
--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);
drop table tself;
--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);
drop table tself;
--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);
drop table tself;
--should pass
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 SET NULL);

drop table tself;
drop table tparent;


--two consectuvie set null  CYCLE

create table t1( a int not null primary key, b int);
create table t2(x int, y int not null unique);
create table t3(l int, m int not null unique , k int );
create table t4(s int, t int not null unique , y int );

--all should pass
--two consectuvie set null  CYCLE , but not a delete connected cylcle
alter table t1 add constraint c1 foreign key (b)
                              references t3(m) on delete CASCADE;
alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete SET NULL;
alter table t4 add constraint c3 foreign key (s)
                              references t2(y) on delete SET NULL;
alter table t3 add constraint c4 foreign key (k)
                              references t4(t) on delete cascade;

alter table t1 drop constraint c1;
alter table t2 drop constraint c2;
alter table t4 drop constraint c3;
alter table t3 drop constraint c4;

--two continuos set nulls , but not a cycle
alter table t3 add constraint c1 foreign key (l)
                              references t1(a) on delete CASCADE;
alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete SET NULL;
alter table t4 add constraint c3 foreign key (s)
                              references t2(y) on delete SET NULL;
alter table t4 add constraint c4 foreign key (y)
                              references t3(m) on delete cascade;

alter table t3 drop constraint c1;
alter table t2 drop constraint c2;
alter table t4 drop constraint c3;
alter table t4 drop constraint c4;

--c4 fails error case NULL followed by a cascade in the path
alter table t3 add constraint c1 foreign key (l)
                              references t1(a) on delete CASCADE;
alter table t2 add constraint c2 foreign key (x)
                             references t1(a) on delete CASCADE;
alter table t4 add constraint c3 foreign key (s)
                              references t2(y) on delete SET NULL;
alter table t4 add constraint c4 foreign key (y)
                              references t3(m) on delete cascade;

alter table t3 drop constraint c1;
alter table t2 drop constraint c2;
alter table t4 drop constraint c3;

drop table t4 ;
drop table t3 ;
drop table t2 ;
drop table t1 ;

-- t2 should fail for these 4 cases below
create table t1( a int not null primary key, b int not null  unique);
create table t2(x int references t1(a) ON DELETE RESTRICT, 
                y int not null unique, z int references t1(b) ON DELETE CASCADE);

drop table t1;
create table  t1(a int not null unique , b int not null unique);
create table  t2(x int references t1(a) ON DELETE SET NULL ,
y int references t1(b) ON DELETE CASCADE);

drop table t1;
create table  t1(a int not null unique , b int not null unique);
create table  t2(x int references t1(a) ON DELETE SET NULL ,
y int references t1(b) ON DELETE SET NULL);

drop table t1;
create table  t1(a int not null unique , b int not null unique);
create table  t2(x int references t1(a) ON DELETE SET NULL ,
y int references t1(b));

drop table t1;