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?
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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.