2

Aside from the aforementioned 'oops' where you delete a record you didn't intend to and now a lot of associated records are gone as well, you probably simply can't cascade delete everything. A thing to keep in mind when using cascades is that it can cause conflicts if there's multiple cascade paths. SQL Server and other DBMS will block the creation of the ...


2

Are there any disadvantages, gotchas, or things to bear in mind when adding ON UPDATE CASCADE ON DELETE CASCADE to every single foreign key in the database? One reason not to : Oops!! If you accidentally delete the wrong record, then you wipe out everything that references on that record. If that record happens to be the one at the root of your entire ...


1

In the Stack Overflow question, What are the Pros and Cons of Cascading delete and updates? The accepted answer by SQLMenace states: Pros: When you delete a row from the Parent table all the foreign key rows are deleted This is usually faster than implementing this with triggers Orphaned rows are unlikely Cons Orphans are possible If by mistake you ...


1

You could use a trigger on the log table that rolls back when the object_id being inserted is not found in sys.objects - since triggers are the typical mechanism we use when a foreign key doesn't make sense or isn't possible (like maintaining integrity across database or instance boundaries). But do you really want to lose all of the data because one column ...


1

The answer is, as usual, it depends, in this case, primarily on whether your values are subject to change. Although there are lots of competing principles, the primary principle at play is that in a relational database you want to avoid updating primary (and therefore foreign) key values at all cost. Others might argue that some of the other factors you've ...


Only top voted, non community-wiki answers of a minimum length are eligible