real example I wrote showing the dangers of cascade

[landonmkelsey]

alter table pets_like_boys drop constraint pets_like;
alter table boys_like_girls drop constraint boys_like;
alter table girls_like_pets drop constraint girls_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(boys_name) on delete cascade;
alter table boys_like_girls add constraint boys_like foreign key (girls_name) references girls_like_pets(girls_name) on delete cascade;
alter table girls_like_pets add constraint girls_like foreign key (pets_name) references pets_like_boys(pets_name) 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;

[debasisdas]

Why you have posted this code here ?
Do you have a question ?

[landonmkelsey]

again it is a working learning machine to illustrate the dangers of cascade.

[debasisdas]

If you know something and want to show your knowledge then please do not post those here as questions. Better post those as an article ,that will really help the new members.

[nav33n]

If you know something and want to show your knowledge then please do not post those here as questions. Better post those as an article ,that will really help the new members.

In my opinion, It doesn't matter if it is a forum thread or an article to share knowledge. Forum thread is read by many people, including new members, experienced members and guest users. So, it's fine.

@landonmkelsey,

If you had also explained what is the danger it would have been great.

Cheers!

[debasisdas]

Definitely there is no potential danger, but as time pass this thread will go down the memory line. Once it goes pas t the 2nd page i don't think anyone will ever open it unless someone runs a search with specific key words. So my idea was if it is an article (as the number is less) that would be easily accessible to others. As you say there is no problem with this approach i have nothing more to say.

Regards,
Deabsis

[nav33n]

Definitely there is no potential danger, but as time pass this thread will go down the memory line. Once it goes pas t the 2nd page i don't think anyone will ever open it unless someone runs a search with specific key words.

Agree with that. But I guess, we don't have an option to publish articles here. Anyways, Cheers man :)

[landonmkelsey]

There is definitely a danger as would be explicitly illustrated if the script is run.

That would explicitly show the danger of csacade and lead to discussion.

We all might learn something.

I have versions of this script that work (and fail) on MySQL and SQL Server.

This script was inspired by page 311 "SQL" by Groff and Weinberg where they

describe the same situation with "runaway deletes". I wrote the script to provide a

real example showing what can occur.

So you want a question: "what are the dangers of cascade"

[nav33n]

Hmm.. Interesting.. What I would have liked to know was the result.. Unfortunately, I don't have oracle installed anywhere [office/home], so I can't check these queries. What happens if we run the query ? Thanks.

[landonmkelsey]

actually amazing and definitely unforgetable.!

The script shows the tables full until the delete command.

and the result is empty tables. Sounds scary doesn't it.

Can you imagine one little delete command corrupting tables at work?

You'd be fired!:'(

The script is set up to display the phenomenon.

BTW
Oracle for windows and linux is available free.
SQLServer Express 2008 is available free.
MySQL comes with Linux and is free for Windows...easy to admin.

Once MySQL had no left outer join.(version 4 was it)

[nav33n]

:) Yep, I know they are available for free. I just don't feel the need to install oracle.

And yes, I tried your queries in mysql and I see what you are talking about. Thanks for the info :)

[landonmkelsey]

Hmm.. Interesting.. What I would have liked to know was the result.. Unfortunately, I don't have oracle installed anywhere [office/home], so I can't check these queries. What happens if we run the query ? Thanks.

is excellent! easy reading!
fun to read and learn. Page 311 suggests the danger so I built a script that would display the before and after state of the tables.

I built a script to build the tables used in examples in that book.

Many a head has rolled when people made potentially corrupting constraints.

[nav33n]

Cool.. Cheers :)

[landonmkelsey]

what is a "PM" post moderator???

thanks for your friendly-not angry replies

[landonmkelsey]

:) Yep, I know they are available for free. I just don't feel the need to install oracle.

And yes, I tried your queries in mysql and I see what you are talking about. Thanks for the info :)

100% agree...Oracle is a bit of a mess...windows or Linux

[nav33n]

what is a "PM" post moderator???

thanks for your friendly-not angry replies

PM means Personal message, not post moderator. :)

[landonmkelsey]

100% agree...Oracle is a bit of a mess...windows or Linux

Should you ever decide to try!
The Oracle instructions are incomplete and even wrong in places! http://www.oracle-base.com/articles/11g/OracleDB11gR1InstallationOnFedora11.php http://www.oracle-base.com/articles/11g/OracleDB11gR1InstallationOnFedora11.php

[debasisdas]

If one understands what is ON DELETE CASCADE and how/ where to use that and where not ,that piece of code would definitely not look surprising. There is no magic or tricks in that, that is pure logic. ON DELETE CASCADE is designed for that only.