Re: ON DELETE CASCADE

[landon kelsey]

the following SQL once left the tables empty under Oracle

not true under mysql or MS SQL Server 2005

alter table pets_like_boys
drop constraint pets_like;

alter table girls_like_pets
drop constraint girls_like;

alter table boys_like_girls
drop constraint boys_like;

drop table pets_like_boys;
drop table boys_like_girls;
drop table girls_like_pets;

create table pets_like_boys (pets_name varchar(32), boys_name varchar(32),primary key (pets_name));
create table boys_like_girls (boys_name varchar(32), girls_name varchar(32),primary key (boys_name));
create table girls_like_pets (girls_name varchar(32), pets_name varchar(32),primary key (girls_name));

insert into pets_like_boys values('fluffy', 'sam');
insert into pets_like_boys values('rover', 'bob');
insert into pets_like_boys values('skippy', 'joe');

insert into boys_like_girls values('bob','jill');
insert into boys_like_girls values('sam','betty');
insert into boys_like_girls values('joe','sue');
insert into boys_like_girls values('george','jill');

insert into girls_like_pets values('sue','fluffy');
insert into girls_like_pets values('jill','skippy');
insert into girls_like_pets values('betty','rover');

alter table pets_like_boys
add constraint pets_like
foreign key (boys_name )
references boys_like_girls
on delete cascade;

alter table boys_like_girls
add constraint boys_like
foreign key (girls_name )
references girls_like_pets
on delete cascade;

alter table girls_like_pets
add constraint girls_like
foreign key (pets_name )
references pets_like_boys
on delete cascade;


select * from pets_like_boys;
select * from boys_like_girls;
select * from girls_like_pets;
delete from boys_like_girls where boys_name = 'bob';
select * from pets_like_boys;
select * from boys_like_girls;
select * from girls_like_pets;



select table_name from user_tables;