Ask the Expert

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?

    Requires Free Membership to View

    Login

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


This was first published in May 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top