We help IT Professionals succeed at work.

on delete cascade -sql 2005

shmz
shmz asked
on
Medium Priority
444 Views
Last Modified: 2012-05-12
Hi,

what are the negative effect of having on delete cascade set on foreign key of a child table?


thanks
Comment
Watch Question

Kalpesh ChhatralaSoftware Consultant
CERTIFIED EXPERT

Commented:
If you remove record from Child table effect also parent table. related parent table record also delete.
Kalpesh ChhatralaSoftware Consultant
CERTIFIED EXPERT

Commented:
Please Ignore first post

If you remove record from Parent table effect also Child table. related child table record also deleted.
Armand GSenior Developer
CERTIFIED EXPERT
Commented:
So the question was:

what are the negative effect of having on delete cascade set on foreign key of a child table?

Child table with records matching from parent table being automatically deleted if Parent record is deleted is a "positive effect" and not a "negative effect". This is because it already does the deleting job for you on the children tables thus reducing your programming job to perform the delete manually.

The "negative" effect that I can see for this case is when you have a similar field that should be a foreign key and you failed to declare it as foreign key, it will cause data inconsistencies later on inside some parts of your database/ tables.

Clearly, if you've carefully designed your table and database structures, "positive effects" weigh more than "negative effects".

Commented:
One of the biggest negatives that can happen is if you cascade from parent to child across many tables.  If by accident somebody runs a delete statement on a high level parent table and forgets a where clause you could wipe out tables worth of data.  

Author

Commented:
thanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.