I have two tables in MySQL database- parent
, child
. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE
and ON DELETE CASCADE
My Parent Table
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
My Question is: What is the difference between the following sql queries.
-
ON DELETE CASCADE
CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB;
-
ON UPDATE CASCADE
CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ) ENGINE=INNODB;
-
ON UPDATE CASCADE ON DELETE CASCADE
CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=INNODB;
Are there any errors in the queries? What do these queries (1,2 & 3) mean?? Are they same???
Best Answer
A very good thread on this subject is to be found here and also here. The definitive guide for MySQL is, of course, the documentation, to be found here.
In the SQL 2003 standard there are 5 different referential actions:
To answer the question:
CASCADE
ON DELETE CASCADE
means that if the parent record is deleted, any child records are also deleted. This is not a good idea in my opinion. You should keep track of all data that's ever been in a database, although this can be done usingTRIGGER
s. (However, see caveat in comments below).ON UPDATE CASCADE
means that if the parent primary key is changed, the child value will also change to reflect that. Again in my opinion, not a great idea. If you're changingPRIMARY KEY
s with any regularity (or even at all!), there is something wrong with your design. Again, see comments.ON UPDATE CASCADE ON DELETE CASCADE
means that if youUPDATE
ORDELETE
the parent, the change is cascaded to the child. This is the equivalent ofAND
ing the outcomes of first two statements.RESTRICT
RESTRICT
means that any attempt to delete and/or update the parent will fail throwing an error. This is the default behaviour in the event that a referential action is not explicitly specified.NO ACTION
NO ACTION
: From the manual. A keyword from standard SQL. In MySQL, equivalent toRESTRICT
. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, andNO ACTION
is a deferred check. In MySQL, foreign key constraints are checked immediately, soNO ACTION
is the same asRESTRICT
.SET NULL
SET NULL
- again from the manual. Delete or update the row from the parent table, and set the foreign key column or columns in the child table toNULL
. This is not the best of ideas IMHO, primarily because there is no way of "time-travelling" - i.e. looking back into the child tables and associating records withNULL
s with the relevant parent record - eitherCASCADE
or useTRIGGER
s to populate logging tables to track changes (but, see comments).SET DEFAULT
SET DEFAULT
. Yet another (potentially very useful) part of the SQL standard that MySQL hasn't bothered implementing! Allows the developer to specify a value to which to set the foreign key column(s) on an UPDATE or a DELETE. InnoDB and NDB will reject table definitions with aSET DEFAULT
clause.As mentioned above, you should spend some time looking at the documentation, here.