MySql Foreign keys: ON DELETE NO ACTION behavour - how to leave info in referenced field?

Question

I have two tables, one 'users' and one 'orders', where the order table has a foreign key reference to the user id table. (I'm using email address as user id in this case.)

What I want to accomplish is this:

1. If the user table id is updated (= email address is changed), this will reflect in the referenced order table user_id field. (This works fine using ON UPDATE CASCADE directive - no problem here!)

2. If the user is deleted from the user table, the order will remain, KEEPING the referenced user_id.

I'm having problem with the second goal:

If I use ON DELETE CASCADE on the referenced user id field, the order row is of course deleted.

If I use ON DELETE NO ACTION, I get an error when I try to remove the user. (#1451 - Cannot delete or update a parent row: a foreign key constraint fails)

If I use ON DELETE SET NULL, I can delete the user row, but the referenced info is set to null so I can't track down who made that order...

Is there a way to allow removal of the user from the user table while keeping the referenced user_id information in the orders table?

Answer 1

Two options:

• Don't actually delete the user, just mark them as deleted by setting a deleted field.

or:

• Remove the foreign key constraint.

I would recommend the first option. Taking the second option can lead to data inconsistencies. Besides, just having the value of the key without the corresponding data in the other table will not help you much anyway.