Relational database foreign key constraints usage in practice

Question

So you have a relational database and you define some foreign keys for reference integrity. But now, what's the better way to leverage them? Assume we are going to delete a row in a table with foreign keys to other tables.

1. Use them as a garbage collector - Set all constraints to cascade. So in your application, you first check if the target row has any "children" and present the user the option to abort the delete operation if it is undesirable to have the dependent rows deleted as well.

2. Use them as actual constraints - In your application, just attempt to delete the target row. If the operation failed, then check if it has children and present the options to the user; If the user wants to proceed with the operation, manually delete the depending rows first.

The second option makes deleting circular references rather arduous - You have to set the foreign keys to null before you can even delete anything.

Answer 1

There are 2 typical foreign key scenarios:

• Association: link 2 entities that can exist on their own
• Composition: link a child entity to its parent entity (the child entity does it exist without a parent, for instance: order and order item)

I'd cascade only in the case of composition and treat each association case individually.