Setting up table relations what do "Cascade", "Set Null" and "Restrict" do?
I want to start using table relations in a new project.
After some googling I got 2 tables set up as InnoDB:
The keys I want to link are
->users->userid (primary) ->sessions->userid (index)
The only thing that I don't understand in this process is what the different settings for "On update" and "On delete" do
The options here are:
- -- (nothing?)
- Cascade (???)
- Set Null (sets everything to null?)
- No action (well duh...)
- Restrict (???)
I basically want the data in sessions to be deleted when a user is completely deleted This since the sessions will only be deleted when the expiration is detected by my session manager...
So if anyone can tell me what these options do it would be much appreciated.
Answer
CASCADE will propagate the change when the parent changes. (If you delete a row, rows in constrained tables that reference that row will also be deleted, etc.)
SET NULL sets the column value to NULL when a parent row goes away.
RESTRICT causes the attempted DELETE of a parent row to fail.
EDIT: You didn't ask about them, but the SQL standard defines two other actions: SET DEFAULT and NO ACTION. In MySQL, NO ACTION is equivalent to RESTRICT. (In some DBMSs, NO ACTION is a deferred check, but in MySQL all checks are immediate.) The MySQL parser accepts SET DEFAULT, but both the InnoDB and NDB engines reject those statements, so SET DEFAULT can't actually be used for either an ON UPDATE or ON DELETE constraint.
Also, note that cascading foreign key actions do not activate triggers in MySQL.