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?

matteeyah
  • 217
  • 1
  • 3
  • 8

1 Answers1

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).

TommCatt
  • 2,159
  • 12
  • 10