refactions2.sql

derby database source code.good for you.

       		                       z int references t1(a) ON DELETE RESTRICT);
drop table t2;
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);
drop table t2;
--END PASS CASES
drop table t1;


-- case sql0634 - 02 (c1 fails)
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);
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 t2 drop constraint c2;
drop table t1;
drop table t2;

-- case sql0634 - 03 
create table t1( a int not null primary key, b int);
create table t2(x int, y int not null unique, z int);
create table t3(l int, m int not null unique , k int );
create table t4(c1 int not null unique , c2 int);


-- error scenario 1: adding constraint c4 will make t2 get two paths from t1 with SET NULLS
alter table t2 add constraint c1 foreign key (x)
                              references t1(a) on delete set null;
alter table t2 add constraint c2 foreign key (z)
                              references t4(c1) on delete set null;
alter table t3 add constraint c3 foreign key (l)
                              references t1(a) on delete cascade;
alter table t4 add constraint c4 foreign key (c1)
                              references t3(m) on delete cascade;


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

-- error scenario 2: adding constraint c4 will make t2 get two paths from t1 with a SET NULL and
--- a CASCADE.
alter table t2 add constraint c1 foreign key (x)
                              references t1(a) on delete CASCADE;
alter table t2 add constraint c2 foreign key (z)
                              references t4(c1) on delete set null;
alter table t3 add constraint c3 foreign key (l)
                              references t1(a) on delete cascade;
alter table t4 add constraint c4 foreign key (c1)
                              references t3(m) on delete cascade;

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

-- error scenario 3: adding constraint c4 will make t2 get two paths from t1 with a NO ACTION 
--- and a CASCADE.
alter table t2 add constraint c1 foreign key (x)
                              references t1(a) on delete NO ACTION;
alter table t2 add constraint c2 foreign key (z)
                              references t4(c1) on delete set null;
alter table t3 add constraint c3 foreign key (l)
                              references t1(a) on delete cascade;
alter table t4 add constraint c4 foreign key (c1)
                              references t3(m) on delete cascade;

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

-- error scenario 4: adding constraint c4 will make t2 get two paths from t1 with a CASCADE
--- and a RESTRICT.
alter table t2 add constraint c1 foreign key (x)
                              references t1(a) on delete CASCADE;
alter table t2 add constraint c2 foreign key (z)
                              references t4(c1) on delete RESTRICT;
alter table t3 add constraint c3 foreign key (l)
                              references t1(a) on delete cascade;
alter table t4 add constraint c4 foreign key (c1)
                              references t3(m) on delete cascade;

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

--FOLLOWING SHOULD PASS

alter table t2 add constraint c1 foreign key (x)
                              references t1(a) on delete set null;
alter table t2 add constraint c2 foreign key (z)
                              references t4(c1) on delete set null;
alter table t3 add constraint c3 foreign key (l)
                              references t1(a) on delete set null;
alter table t4 add constraint c4 foreign key (c1)
                              references t3(m) on delete cascade;

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

alter table t2 add constraint c1 foreign key (x)
                              references t1(a) on delete CASCADE;
alter table t2 add constraint c2 foreign key (z)
                              references t4(c1) on delete set null;
alter table t3 add constraint c3 foreign key (l)
                              references t1(a) on delete set null;
alter table t4 add constraint c4 foreign key (c1)
                              references t3(m) on delete cascade;

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

alter table t2 add constraint c1 foreign key (x)
                              references t1(a) on delete CASCADE;
alter table t2 add constraint c2 foreign key (z)
                              references t4(c1) on delete CASCADE;
alter table t3 add constraint c3 foreign key (l)
                              references t1(a) on delete set null;
alter table t4 add constraint c4 foreign key (c1)
                              references t3(m) on delete cascade;

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

alter table t2 add constraint c1 foreign key (x)
                              references t1(a) on delete CASCADE;
alter table t2 add constraint c2 foreign key (z)
                              references t4(c1) on delete CASCADE;
alter table t3 add constraint c3 foreign key (l)
                              references t1(a) on delete CASCADE;
alter table t4 add constraint c4 foreign key (c1)
                              references t3(m) on delete cascade;

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

alter table t2 add constraint c1 foreign key (x)
                              references t1(a) on delete SET NULL;
alter table t2 add constraint c2 foreign key (z)
                              references t4(c1) on delete SET NULL;
alter table t3 add constraint c3 foreign key (l)
                              references t1(a) on delete SET NULL;
alter table t4 add constraint c4 foreign key (c1)
                              references t3(m) on delete RESTRICT;

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

alter table t2 add constraint c1 foreign key (x)
                              references t1(a) on delete SET NULL;
alter table t2 add constraint c2 foreign key (z)
                              references t4(c1) on delete SET NULL;
alter table t3 add constraint c3 foreign key (l)
                              references t1(a) on delete CASCADE;
alter table t4 add constraint c4 foreign key (c1)
                              references t3(m) on delete RESTRICT;

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

alter table t2 add constraint c1 foreign key (x)
                              references t1(a) on delete SET NULL;
alter table t2 add constraint c2 foreign key (z)
                              references t4(c1) on delete CASCADE;
alter table t3 add constraint c3 foreign key (l)
                              references t1(a) on delete CASCADE;
alter table t4 add constraint c4 foreign key (c1)
                              references t3(m) on delete RESTRICT;

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


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








--- END OF ACTUAL ERROR CASES

--- MISC CASES 
--Following should give error because of delete-rule restrictions
create table t1( a int not null primary key);
create table t2(x int references t1(a) ON DELETE CASCADE, 
                                       y int not null unique);
create table t3(l int references t1(a) ON DELETE CASCADE , 
                         m int references t2(y) ON DELETE SET NULL);

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

--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=42915


create table t1( a int not null primary key);
create table t2(x int references t1(a) ON DELETE SET NULL, 
                                       y int not null unique);
create table t3(l int references t1(a) ON DELETE CASCADE , 
                         m int references t2(y) ON DELETE SET NULL);

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

--Following should pass.
create table t1( a int not null primary key);
create table t4(s int not null unique);
create table t2(x int references t4(s) ON DELETE CASCADE, y int not null unique);
create table t3(l int references t1(a) ON DELETE CASCADE , 
                         m int references t2(y) ON DELETE SET NULL);


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


--Following should give error because of delete-rule restrictions
create table t1( a int not null primary key);
create table t2(x int, y int not null unique);
create table t3(l int, m int );

-- all should pass
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 cascade;
alter table t3 add constraint c3 foreign key (m) 
                              references t2(y) on delete cascade;

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


-- c3 fails: sql0633N - 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 cascade;
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; sql0632N - 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 cascade;

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


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

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

-- succeds
alter table t2 add constraint c1 foreign key (x) 
                             references t1(a) on delete set null;
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;

-- succeds
alter table t2 add constraint c1 foreign key (x) 
                             references t1(a) on delete set null;
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 cascade;

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

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