In the SQL standard there are 5 different referential actions:
- 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 using triggers.
- 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 changing PRIMARY KEYs with any regularity (or even at all), there is something wrong with your design.
- ON UPDATE CASCADE ON DELETE CASCADE means that if you UPDATE OR DELETE the parent, the change is cascaded to the child. This is the equivalent of ANDing the first two statements.
- RESTRICT
- Means that any attempt to delete and/or update the parent will fail throwing an error. This is the default behavior in the event that a referential action is not explicitly specified.
- NO ACTION
- A keyword from standard SQL. In MySQL, equivalent to RESTRICT. 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, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.
- SET NULL
- Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL.
- SET DEFAULT
- 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 a SET DEFAULT clause.
Please refer documentation for corresponding database you are using,
Article Tags:
DatabasesArticle Categories:
MySQL