Q

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


This was first published in May 2003

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close