refactions.sql

derby database source code.good for you.

--no cascade delete , just default check
create table t1(a int not null primary key);
create table t2(b int references t1(a));
insert into t1 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
delete from t1;
drop table t2;

--simple cascade delete
create table t2(b int references t1(a) ON DELETE CASCADE);
insert into t2 values (1) , (2) , (3) , (4);
delete from t1 where a =2 ;
select * from t2;
delete from t1 ;
select * from t2;

--multiple rows in the dependent table for a single row in the parent
insert into t1 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
delete from t1 where a = 3 ;
select * from t1;
delete from t1;
select * from t2;

drop table t2;
drop table t1;


--chain of cascade delete 
--every table has one depedent table referencing it
create table t1 (a int not null primary key ) ;
create table t2 (b int not null primary key  references t1(a) ON DELETE CASCADE);
create table t3 (c int not null primary key  references t2(b) ON DELETE CASCADE) ;
create table t4 (d int not null primary key  references t3(c) ON DELETE CASCADE) ;
create table t5 (e int not null primary key  references t4(d) ON DELETE CASCADE) ;

insert into t1 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t2  values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t3 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t4 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t5 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
delete from t1 where a = 5;
select * from t1;
select * from t2;
select * from t3;
select * from t4;
select * from t5;
delete from t1 ;
select * from t1;
select * from t2;
select * from t3;
select * from t4;
select * from t5;


--check the prepared statement cascade delete
insert into t1 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t2  values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t3 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t4 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t5 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
autocommit off;
prepare sdelete as 'delete from t1 where a = ?';
execute sdelete using 'values (2)';
select * from t1;
select * from t2;
select * from t3;
select * from t4;
select * from t5;
prepare sdelete1 as 'delete from t2 where b = ?';
execute sdelete1 using 'values (3)';

--Make sure the ps recompile on a DDL action
drop table t5 ;
execute sdelete using 'values (5)';
execute sdelete1 using 'values (6)';
select * from t1;
select * from t2;
select * from t3;
select * from t4;

drop table t4;
drop table t3 ;
execute sdelete using 'values (7)';
execute sdelete1 using 'values (8)';
select * from t1;
select * from t2;
remove sdelete;
remove sdelete1;
autocommit on;
delete from t1 ;
select * from t1;
select * from t2;
drop table t2 ;
drop table t1;


--two foreign keys and less number of columns on the dependent table.
create table t1( a int not null primary key , b int , c int not null unique) ;
create table t2( x int references t1(c) ON DELETE CASCADE ) ;
create table t3( y int references t1(a) ON DELETE CASCADE ) ;

insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;
insert into t2 values (3) , (6), (9) ;
insert into t3 values (1) , (4) , (7) ;
delete from t1 ;
select * from t1;
select * from t2;
select * from t3;
drop table t3;
drop table t2;
drop table t1;

--triggers on the  dependen tables 

create table t1( a int not null primary key , b int , c int not null unique) ;
create table t2( x int references t1(c) ON DELETE CASCADE ) ;
create table t3( y int references t1(a) ON DELETE CASCADE) ;
create table t4(z int , op char(2));

--create triggers such a way that the all deleted row
--in t2 are inserted into t4

create trigger trig_delete after DELETE on t2
referencing old as deletedrow
for each row mode db2sql
insert into t4 values(deletedrow.x , 'bd');

insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;
insert into t2 values (3) , (6), (9) ;
insert into t3 values (1) , (4) , (7) ;
delete from t1 ;
select * from t4;
select * from t1;
select * from t2;
select * from t3;

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


--test for multiple fkeys on the same table referrring to
--different columns on the parent table.

create table  t1(a int not null unique , b int not null unique);
create table  t2(x int references t1(a) ON DELETE CASCADE ,
y int references t1(b) ON DELETE CASCADE);
insert into t1 values(1 , 4) , (2,3) , (3, 2) , (4, 1);
insert into t2 values(1 , 4) , (2,3) , (3, 2) , (4, 1);
delete from t1;
select * from t1;
select * from t2;

drop table t2;
drop table t1;


--check for the unique nulls case
--check for sefl referencing





---ON DELETE SET NULL TEST CASES


--simple cascade delete set to null
create table t1(a int not null primary key);
create table t2(b int references t1(a) ON DELETE SET NULL);
insert into t1 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
delete from t1 where a =2 ;
select * from t2;
delete from t1 ;
select * from t2;

--multiple rows in the dependent table for a single row in the parent
insert into t1 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
insert into t2 values (1) , (2) , (3) , (4);
delete from t1 where a = 3 ;
select * from t1;
delete from t1;
select * from t2;

drop table t2;
drop table t1;


--chain of cascade delete 
--every table has one depedent table referencing it
create table t1 (a int not null primary key ) ;
create table t2 (b int not null primary key  references t1(a) ON DELETE CASCADE);
create table t3 (c int not null primary key  references t2(b) ON DELETE CASCADE) ;
create table t4 (d int not null primary key  references t3(c) ON DELETE CASCADE) ;
create table t5 (e int references t4(d) ON DELETE SET NULL) ;

insert into t1 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t2  values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t3 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t4 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t5 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
delete from t1 where a = 5;
select * from t1;
select * from t2;
select * from t3;
select * from t4;
select * from t5;
delete from t1 ;
select * from t1;
select * from t2;
select * from t3;
select * from t4;
select * from t5;


--check the prepared statement cascade delete
insert into t1 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t2  values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t3 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t4 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t5 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
autocommit off;
prepare sdelete as 'delete from t1 where a = ?';
execute sdelete using 'values (2)';
select * from t1;
select * from t2;
select * from t3;
select * from t4;
select * from t5;
prepare sdelete1 as 'delete from t2 where b = ?';
execute sdelete1 using 'values (3)';

--Make sure the ps recompile on a DDL action
drop table t5 ;
execute sdelete using 'values (5)';
execute sdelete1 using 'values (6)';
select * from t1;
select * from t2;
select * from t3;
select * from t4;

drop table t4;
drop table t3 ;
execute sdelete using 'values (7)';
execute sdelete1 using 'values (8)';
select * from t1;
select * from t2;
remove sdelete;
remove sdelete1;
autocommit on;
delete from t1 ;
select * from t1;
select * from t2;
drop table t2 ;
drop table t1;


--two foreign keys and less number of columns on the dependent table.
create table t1( a int not null primary key , b int , c int not null unique) ;
create table t2( x int references t1(c) ON DELETE CASCADE ) ;
create table t3( y int references t1(a) ON DELETE SET NULL ) ;

insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;
insert into t2 values (3) , (6), (9) ;
insert into t3 values (1) , (4) , (7) ;
delete from t1 ;
select * from t1;
select * from t2;
select * from t3;
drop table t3;
drop table t2;
drop table t1;

--triggers on the  dependen tables 

create table t1( a int not null primary key , b int , c int not null unique) ;
create table t2( x int references t1(c) ON DELETE SET NULL ) ;
create table t3( y int references t1(a) ON DELETE SET NULL) ;
create table t4(z int , op char(2));

--create triggers such a way that the all deleted row
--in t2 are inserted into t4

create trigger trig_update after UPDATE on t2
referencing old as updatedrow
for each row mode db2sql
insert into t4 values(updatedrow.x , 'bu');

insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;
insert into t2 values (3) , (6), (9) ;
insert into t3 values (1) , (4) , (7) ;
delete from t1 ;
select * from t4;
select * from t1;
select * from t2;
select * from t3;

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


--test for multiple fkeys on the same table referrring to
--different columns on the parent table.

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);
insert into t1 values(1 , 4) , (2,3) , (3, 2) , (4, 1);
insert into t2 values(1 , 4) , (2,3) , (3, 2) , (4, 1);
delete from t1;
select * from t1;
select * from t2;

drop table t2;
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);
insert into t1 values(1 , 4) , (2,3) , (3, 2) , (4, 1);
insert into t2 values(1 , 3) , (2,3) , (3, 4) , (4, 1);
delete from t1 where a =1 ;

drop table t2;
drop table t1;
--following is ACTAULL CASCADE DELETE CASE
create table  t1(a int not null unique , b int not null unique);
create table  t2(x int references t1(a) ON DELETE CASCADE ,
y int references t1(b) ON DELETE CASCADE);
insert into t1 values(1 , 4) , (2,3) , (3, 2) , (4, 1);
insert into t2 values(1 , 3) , (2,3) , (3, 4) , (4, 1);
delete from t1 where a =1 ;
--Above delete should delete two rows.
drop table t2;
drop table t1;

create table t1 (a int not null primary key ) ;
create table t2 (b int not null primary key  references t1(a) ON DELETE NO ACTION);
insert into t1 values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
insert into t2  values (1) , (2) , (3) , (4) , (5) , (6) , (7) , (8) ;
delete from t1;
select * from t2;
drop table t2;
drop table t1;

--test for DELETE RESTRICT
--first check with an after trigger and NO ACTION
create table t1(a int not null unique, b int not null unique);
create table t2(x int references t1(a) ON DELETE NO ACTION , y int);
create trigger trig_delete after DELETE on t1
referencing old as deletedrow
for each row mode db2sql
delete from t2 where x = deletedrow.a;

insert into t1 values(1 , 2);
insert into t1 values(2 , 3);
insert into t2 values(1, 2);
insert into t2 values(2, 3);

-- should fail
-- parent row can not be deleted because of a dependent relationship from another table
delete from t1 where a =1;

drop table t2;

--do the same case as above with RESTRICT
--we should get error, because RESTRICT rules are checked before firing triggers
create table t2(x int references t1(a) ON DELETE RESTRICT , y int);
insert into t2 values(1, 2);
insert into t2 values(2, 3);

--following delete should throw constraint violations error
delete from t1 where a =1;
drop table t2;
drop table t1;

--test for ON UPDATE RESTRICT
--first check with a trigger and NO ACTION
autocommit off ;
create table t1(a int not null unique, b int not null unique);
create table t2(x int references t1(a) ON UPDATE NO ACTION , y int);
create trigger trig_update after UPDATE on t1
referencing old as old for each  row mode db2sql
update t2 set x = 2 where x = old.a;

insert into t1 values(1 , 2);
insert into t1 values(2 , 3);
insert into t2 values(1, 2);
insert into t2 values(2, 3);
commit;
-- this update should fail
-- parent row can not be deleted because of a dependent relationship from another table
update t1 set a = 7 where a =1;
-- should pass because no foreign key constraints are violated
update t1 set b = 7 where a =1;
select * from t1 ;
select * from t2 ;
rollback;
drop table t2;
commit;
--do the same case as above with RESTRICT
--we should get error, because RESTRICT is check before firing triggers
create table t2(x int references t1(a) ON UPDATE RESTRICT , y int);
insert into t2 values(1, 2);
insert into t2 values(2, 3);
commit;
--following update should throw an error
update t1 set a = 7 where a =1;
select * from t1 ;
select * from t2;
autocommit on;
drop table t2;
drop table t1;


--After ROW triggers on the  dependen tables
create table t1( a int not null primary key , b int , c int not null unique) ;
create table t2( x int references t1(c) ON DELETE CASCADE ) ;
create table t3( y int references t1(a) ON DELETE CASCADE) ;
create table t4(z int , op char(2));

--create triggers such a way that the all deleted row
--in t2 are inserted into t4

create trigger trig_delete after DELETE on t2
referencing old as deletedrow
for each row mode db2sql
insert into t4 values(deletedrow.x , 'ad');

insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;
insert into t2 values (3) , (6), (9) ;
insert into t3 values (1) , (4) , (7) ;
delete from t1 ;
select * from t4;
select * from t1;
select * from t2;
select * from t3;

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


--After Statement triggers on the  dependen tables
create table t1( a int not null primary key , b int , c int not null unique) ;
create table t2( x int references t1(c) ON DELETE CASCADE ) ;
create table t3( y int references t1(a) ON DELETE CASCADE) ;
create table t4(z int , op char(2));

--create triggers such a way that the all deleted row
--in t2 are inserted into t4

create trigger trig_delete after DELETE on t2
REFERENCING OLD_Table AS deletedrows
for each statement mode db2sql 
insert into t4 select x, 'ad' from deletedrows;

insert into t1 values (1, 2, 3), (4,5,6) , (7,8,9) ;
insert into t2 values (3) , (6), (9) ;
insert into t3 values (1) , (4) , (7) ;
delete from t1 ;
select * from t4;
select * from t1;
select * from t2;
select * from t3;

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


--After triggers on a self referencing table