Rectangle 27 1

mysql Foreign key constraints: When to use ON UPDATE and ON DELETE?


CREATE TABLE COMPANY (
     company_id INT NOT NULL,
     company_name VARCHAR(50),
     PRIMARY KEY (company_id)
) ENGINE=INNODB;

CREATE TABLE USER (
     user_id INT, 
     user_name VARCHAR(50), 
     company_id INT,
     INDEX company_id_idx (company_id),
     FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;
ON DELETE CASCADE : dangerous
  • ON DELETE CASCADE : dangerous : if you delete a company row in table COMPANY the engine will delete as well the related USERs. This is dangerous but can be used to make automatic cleanups on secondary tables (so it can be something you want, but quite certainly not for a COMPANY<->USER example)
  • ON DELETE RESTRICT : the default : if you try to delete a company_id Id in table COMPANY the engine will reject the operation if one USER at least links on this company, can save your life.
  • ON DELETE SET NULL : handful : if you delete a COMPANY row the related USERs will automatically have the relationship to NULL. If Null is your value for users with no company this can be a good behavior, for example maybe you need to keep the users in your application, as authors of some content, but removing the company is not a problem for you.
  • ON UPDATE CASCADE : the best one usually : if you update a company_id in a row of table COMPANY the engine will update it accordingly on all USER rows referencing this COMPANY (but no triggers activated on USER table, warning). The engine will track the changes for you, it's good.
  • ON UPDATE RESTRICT : the default : if you try to update a company_id in table COMPANY the engine will reject the operation if one USER at least links on this company.
  • ON UPDATE SET NULL : if you update a company_id in a row of table COMPANY the engine will set related USERs company_id to NULL (should be available in USER company_id field). I cannot see any interesting thing to do with that on an update, but I may be wrong.

And I do not think this will get fixed one day. Foreign key constraints are managed by the InnoDb storage and Triggers are managed by the MySQL SQL engine. Both are separated. Innodb is the only storage with constraint management, maybe they'll add triggers directly in the storage engine one day, maybe not.

But I have my own opinion on which element you should choose between the poor trigger implementation and the very useful foreign keys constraints support. And once you'll get used to database consistency you'll love PostgreSQL.

Do not hesitate to put constraints on the database. You'll be sure to have a consistent database, and that's one of the good reasons to use a database. Especially if you have several applications requesting it (or just one application but with a direct mode and a batch mode using different sources).

It's been a long time since I wrote that. Now I think I should add one important warning. MySQL has one big documented limitation with cascades. Cascades are not firing triggers. So if you were over confident enough in that engine to use triggers you should avoid cascades constraints.

MySQL triggers activate only for changes made to tables by SQL statements. They do not activate for changes in views, nor by changes to tables made by APIs that do not transmit SQL statements to the MySQL Server

Perfect!!!, this explanation really good. even a stupid person like me can understand :D

Triggers are not activated by foreign key actions.

We'll take an example, a company table with a user table containing people from theses company

With MySQL you do not have advanced constraints like you would have in postgreSQL but at least the foreign key constraints are quite advanced.

You will need to be careful of cascading, it can lock up your system if lots of records need to be changed. Cascde delete should espcially be looked at carefully before using, often you really do want the delete to not occur if there are child records. I wouldn't want a customer delete to wipe out the financial data for the oreders he had previously. Sometimes it is better to make sure cacading is not on and provide a way to amrk records as inactive.

usually my default is: ON DELETE RESTRICT ON UPDATE CASCADE. with some ON DELETE CASCADE for track tables (logs--not all logs--, things like that) and ON DELETE SET NULL when the master table is a 'simple attribute' for the table containing the foreign key, like a JOB table for the USER table.

Note
Rectangle 27 1

mysql Foreign key constraints: When to use ON UPDATE and ON DELETE?


Addition to @MarkR answer - one thing to note would be that many PHP frameworks with ORMs would not recognize or use advanced DB setup (foreign keys, cascading delete, unique constraints), and this may result in unexpected behaviour.

For example if you delete a record using ORM, and your DELETE CASCADE will delete records in related tables, ORM's attempt to delete these related records (often automatic) will result in error.

That would bea reason to not use that particular ORM. Any tool that is that poor at database support is not trustworthy. Foreign keys and cascading deletes or updates are db basics not advanced concepts and no realtional database should ever be designed without foreign key constraints!

Note