Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT. Some database systems have deferred checks, and NO ACTION is a deferred check. 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. This is the default behaviour in the event that a referential action is not explicitly specified.įor an ON DELETE or ON UPDATE that is not specified, the default action is always RESTRICT`. RESTRICT means that any attempt to delete and/or update the parent will fail throwing an error.This is the equivalent of ANDing the outcomes of first two statements. ON UPDATE CASCADE ON DELETE CASCADE means that if you UPDATE OR DELETE the parent, the change is cascaded to the child. If you're changing PRIMARY KEYs with any regularity (or even at all!), there is something wrong with your design. ON UPDATE CASCADE means that if the parent primary key is changed, the child value will also change to reflect that. You should keep track of all data that's ever been in a database, although this can be done using TRIGGERs. ON DELETE CASCADE means that if the parent record is deleted, any child records are also deleted. In the SQL 2003 standard there are 5 different referential actions: The definitive guide for MySQL is, of course, the documentation, to be found here. ON UPDATE CASCADE ON DELETE CASCADE CREATE TABLE child (Īre there any errors in the queries? What do these queries (1,2 & 3) mean? Are they same?Ī very good thread on this subject is to be found here and also here. My Question is: What is the difference between the following sql queries. Is there any significant difference between ON UPDATE CASCADE and ON DELETE CASCADE I'm trying to add foreign key references to my child table based on the parent table. I have two tables in MySQL database- parent, child.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |