0

In my database I have a table with 2 columns referencing the same column in a separate table.

I know this creates a race condition, but I have a check in place to make sure they don't point to the same row. Is there any smart way to achieve a cascading update behavior? Or am I forced to fall back to using triggers?

Maybe if I model the database differently. The model in question is an inventory transfer between stores. It notes the receiving store id and the sending store id, which creates a race condition (in the eyes of the RDBMS, I made sure that doesn't actually happen with a check).

Any input on this?

5
  • Is there any smart way to achieve a cascading update behavior? The foreign keys should be defined as CASCADE UPDATE. Is this what you are looking for ?
    – Kin Shah
    Commented Jul 9, 2015 at 14:57
  • Yeah, but when you set two foreign keys in a single table that point to the same column in another table and you try to define them as CASCADE UPDATE SQL Server throws an error claiming that it can't be done, because it results in a race condition.
    – matteeyah
    Commented Jul 9, 2015 at 15:11
  • Which exact value(s) are you protecting with cascade? In other words, which column or column(s) will be updated, and which other column or column(s) need to be updated automatically? Which of these columns is actually liked to change (and requires direct, ad hoc update accessibility)? I find that sometimes people want all of their FKs to include cascade definitions when they're not always necessary. Commented Jul 9, 2015 at 15:23
  • 1
    I wonder why the store IDs need to be updated in the first place. Commented Jul 9, 2015 at 15:33
  • 1
    I suspect only in the event where a mistake was made when originally recording. Wrap a procedure around that special case (prevent direct ad hoc updates to those columns) and toss the cascade. You probably want additional auditing of that scenario anyway, which cascade won't buy you. Commented Jul 9, 2015 at 15:41

1 Answer 1

1

When SQL was first developed, the idea of cascading updates and deletes made sense. In practice, they are nothing but trouble. Deleting one row of one table or updating one field of one row of one table could lock up the entire database for hours because of the cascading locks. Or generate a race condition.

Plus, cascading actions are, by definition, side effects. You really, really want to minimize the amount of side effects. Nothing good comes from them.

So it is best to not allow the deletion of a row that is referred to by any foreign key(s). Always accept the default "On Cascade" option (No Action). Allow the DBMS to throw an exception and let the app respond correctly (delete/update the referencing rows then the referenced row -- after checking with the user if this is really what they want to do, of course).

1
  • Also ON CASCADE DELETE can be particularly dangerous. Many people use DELETE then INSERT when refreshing data to avoid writing the equivalent (usually longer) IF <EXISTS> UPDATE ELSE INSERT or learning MERGE syntax or what-ever their DB provides for UPSERT type operations (or when actively avoiding such syntax for cross-DB compatibility). The initial delete will kill all the child rows (and grandchildren and so forth as far as the cascade cascades) but the re-insert won't put them back. Commented Nov 20, 2017 at 14:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.