Windows Systems Administrator Jupitermedia US-CT-Darien Post A Job | Post A Resume

The SQL Guru Answers your Questions...

---

Today's question comes from Vladimir D.:

I have tables linked by referential integrity constraint or should I say foreign key. Is there any way SQL to automaticaly delete rows in "child" table when coresponding row in "parent" table is deleted. I know about the trigger but as I saw triggers are not working in situation where foreign kay constraint is present. What is your suggestion to have tables without referential integrity and do cascading delete and update by trigger or keep foreign keys and do manualy deletino of all "child" rows in all tables referencing "parrent" table. I hope you understood my question because my English is far away from perfect. Thank you for your help Best regards Vladimir Djacic

Vladimir,

Unfortunately, SQL Server 7.0 and prior versions do not support cascading deletes or updates. As you've seen, the referential integrity constraints interfere with triggers. (Because constraints are checked before triggers.)

Which is the best way? Both methods have pros and cons. The biggest pro to writing triggers is that you get the all the referential integrity options. The biggest con for triggers is the amount of code you need to write... and trust me, writing basically the same trigger 50 times is not a lot of fun. Trigger code is also a hassle to maintain when you change tables in the database.

Constraints are easy to set up, but are limited to restrictive RI, as you've seen. They can also add some management overhead if you need to drop or truncate tables in your database (you have to drop the constraints first).

I prefer to use RI constraints. I actually don't use cascading updates or deletes that much in my database designs for a couple of reasons:

1) If you need to use cascading updates, it means that you're using an "intelligent" key. That is, the key values have meaning. (as opposed to "surrogate" keys that have no inherent meaning, like an autonumber or IDENTITY column.) Using intelligent vs. surrogate keys is a bit of a religious debate that I won't get into here, and I happen to like surrogate keys - thus, no need to cascade updates.
2) Cascading deletes scare me. Usually, someone has put a lot of time & effort populating the database, and cascading deletes make it too easy to wipe out a lot of data with a DELETE statement.

If I have to implement cascading deletes, I'll usually wrap them into a stored procedure that the application has to call to perform the delete.

Sean

P.S. I've heard rumors that SQL Server 7.5 will finally implement cascading DRI constraints. Of course, I can neither confirm nor deny those rumors :)

---

Read Other SQL Guru Questions

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article