Sunday, July 27, 2008

Foreign key cascade on update dangerous?

In relational databases there's a feature called foreign key cascading. Typically, there's an ID associated with each row, or entry, in a database table. Tables may refer to other tables using a foreign key, which typically refers to the IDs of the rows being linked. There are options to "cascade" on two kinds of operations: delete and update. When a table refers to another table, the table pointing to the other table can be configured to take changes in the ID of the original table and integrate them automatically. This is what happens when ON UPDATE CASCADE is set on a foreign key. When ON DELETE CASCADE is set on a foreign key, deleting rows in the original table will also delete rows that are pointing to that row.

So, it's not hard to imagine situations where cascading on delete would be dangerous.

Now a while ago, I was talking to a database specialist about cascading updates on foreign keys. He mentioned that they're dangerous, so I took his word for it. But I've been thinking about it and I still don't see any way they can be dangerous; the only changes being propagated outward will be changes in the values stored in foreign keys; no rows will be deleted.

I've asked around to get second opinions and examples of situations where this would be bad; for now, I'm just going to avoid cascading updates for future projects.

blog comments powered by Disqus