Problem solve Get help with specific problems with your technologies, process and projects.

ON DELETE SET NULL for lookup tables

Should there be cascade-delete set to "on" from a lookup table to a table with one of its keys? Say you had a state table with "state id". For a table that foreign keys into the state table, should you cascade delete records of this table when one of the states is deleted?

Probably not.

Your example of a State Code lookup table is a good one. It's highly unlikely that a state will ever be deleted, but if it is, do you really want to just blow away all your customer records that were from that state? Wouldn't you want to keep those records, and do something else about their state codes? If so, ON DELETE CASCADE is not appropriate for the customer state code foreign key.

You might consider using ON DELETE SET NULL, so that customer records that were in the state that was deleted are retained, with their state code set to null (meaning "don't know which state this customer is in"). Unfortunately not all databases support ON DELETE SET NULL syntax, so you might want to implement that with a trigger, or perhaps leave it as ON DELETE RESTRICT and require that customer records for that state be updated before the state code is allowed be deleted from the lookup table.

For More Information

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.