What happens if a parent row which is referenced by child row is being deleted in case of foreign key constraint?
Câu trả lời What happens if a parent row which is referenced by child row is being deleted in case of foreign key constraint?
When a parent row that is referenced by a child row is being deleted in the context of a foreign key constraint in MySQL, the behavior depends on the specific foreign key constraint rules defined for the ON DELETE action in the table schema. MySQL supports several options for handling deletions of referenced rows:
CASCADE: If the ON DELETE CASCADE rule is specified, deleting a parent row will automatically delete the matching rows in the child table. This ensures that all dependent child rows are removed along with the parent row, maintaining referential integrity without leaving orphaned rows in the child table[4].
SET NULL: With the ON DELETE SET NULL rule, when a parent row is deleted, the values in the foreign key column(s) of the referencing child rows are set to NULL. This option is useful when you want to preserve the child rows but indicate that the relationship with the parent row no longer exists[4].
NO ACTION or RESTRICT: If the foreign key constraint is defined with ON DELETE NO ACTION or ON DELETE RESTRICT, MySQL will prevent the deletion of a parent row if there are any child rows that reference it. An error will be raised if an attempt is made to delete a parent row that has dependent child rows, thereby preventing the operation to maintain data integrity[2][4].
