I'm designing my database schema using MySQL Workbench, which is pretty cool because you can do diagrams and it converts them 😛
Anyways, I've decided to use InnoDB because of it's Foreign Key support. One thing I noticed though is that it allows you to set On Update and on Delete options for foreign keys. Can someone explain where "Restrict", "Cascade" and set null could be used in a simple example?
For example, say I have a user
table which includes a userID
. And say I have a message table message
which is a many-to-many which has 2 foreign keys (which reference the same primary key, userID
in the user
table). Is setting the On Update and On Delete options any useful in this case? If so, which one do I choose? If this isn't a good example, could you please come up with a good example to illustrate how these could be useful?
Thanks
Best Answer
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).
With MySQL you do not have advanced constraints like you would have in postgreSQL but at least the foreign key constraints are quite advanced.
We'll take an example, a company table with a user table containing people from theses company
Let's look at the ON UPDATE clause:
And now on the ON DELETE side:
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) andON 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.Edit
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.
==> See below the last edit, things are moving on this domain
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.
12/2017-Updating this Edit about MySQL:
as stated by @IstiaqueAhmed in the comments, the situation has changed on this subject. So follow the link and check the real up-to-date situation (which may change again in the future).