May 7, 2007 at 7:56 am
It is possible to delete a record from a table and at same time delete all of its references in other tables, in a single stored procedure? f so would there be any performance penalty? Is it called Cascading delete? Does cascading delete means deleting PK and FK in a single delete command? Can some one explain it to me?
thanks,
kushpaw
May 7, 2007 at 9:36 am
It's deleting the parent record and having the child records deleted too. No perf penalty I'm aware of, but it's automatic, which means that it happens and you can lose lots of data quickly. At least without it, then you have to make the decision to delete the child records first before the parent.
May 7, 2007 at 1:43 pm
Since it is automatic, you must have to set something in table during design time. Can you suggest some links to for this topic? Is this feature avaible only in Sql 2005?
thanks,
kushpaw
May 7, 2007 at 2:15 pm
"Cascading Referential Integrity Constraints" in Books Online should be a starting point.
May 8, 2007 at 6:59 am
To answer your second question ... Cascading deletes are not new in sql 20005. They are available in sql 2000 (maybe even earlier).
Norene Malaney
May 8, 2007 at 7:22 am
In 7.0 it was only through triggers the built in one started in 2000 but without SET DEFAULT and SET NULL, the later were implemented in 2005. Cascade DELETE, Cascade UPDATE, Cascade SET DEFAULT and Cascade SET NULL is very simple if a references b b must exist.
Primary Key a becomes Foreign Key b so you can cascade the delete of a to delete b or a can set b to null or default and cascade the update of a to update b. These are the ANSI SQL DRI(declarative referential integrity) rules, Microsoft docs could be different based on implementation. Hope this helps.
Kind regards,
Gift Peddie
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply