ON DELETE RESTRICT: it is prohibited to delete a row of the referenced table if that row has any matching rows in the referencing table.ON DELETE SET DEFAULT: if a row of the referenced table is deleted, then all referencing columns in all matching rows of the referencing table to be set to the column’s default value.ON DELETE SET NULL: if a row of the referenced table is deleted, then all referencing columns in all matching rows of the referencing table to be set to null.ON DELETE CASCADE: if a row of the referenced table is deleted, then all matching rows in the referencing table are deleted.These are called, directly from the SQL:2011 spec There are five options for ON DELETE and ON UPDATE that can apply to the FOREIGN KEY. The foreign key constraint, the operation will fail. For example, if anĪction specifies SET DEFAULT but the default value would not satisfy Not excuse you from observing any constraints. Respectively, when the referenced row is deleted. Referencing row(s) to be set to nulls or their default values, These cause the referencing column(s) in the There are two other options: SET NULLĪnd SET DEFAULT. Referenced row is deleted, row(s) referencing it should beĪutomatically deleted as well. Transaction, whereas RESTRICT does not.) CASCADE specifies that when a NO ACTION allows the check to be deferred until later in the (The essential difference between these two choices is that If any referencing rows still exist when the constraint is checked, anĮrror is raised this is the default behavior if you do not specifyĪnything. RESTRICT prevents deletion of a referenced row. Restricting and cascading deletes are the two most common options. Cascading deletes is one thing you can do. To maintain data integrity, you have to do something to the "child" table. When you delete a row from the "parent" table-from "that_table"-you remove a valid value from the possible values for the "child" table. One way to interpret a foreign key declaration is, "All valid values for this column come from 'that_column' in 'that_table'." When you delete a row in the "child" table, nobody cares. it could mean delete the Parent record when the Child record isĭeleted, or it could mean delete the Child record when the Parent is So it goes with the foreign key declaration. ON DELETE CASCADE is an optional clause in a foreign key declaration. Records are owned by Parent records, which table needs the ON DELETE For example, if I have two tables - Parent and Child - where Child
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |