MySQL Forums
Forum List  »  Oracle

cascade foreign key constraint
Posted by: landon kelsey
Date: September 09, 2007 03:32PM

Once in Oracle, the following would leave the tables empty. This is not happening in mysql or even MS SQL Server 2005.

I do not have Oracle available currently!

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;

Options: ReplyQuote

Written By
cascade foreign key constraint
September 09, 2007 03:32PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.