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.
This was first published in May 2003