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:
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!)
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?