Behavior of FOREIGN KEY Constraints on DELETE and UPDATE
Foreign key constraints define how MySQL should react when a referenced (parent table) row is deleted or updated. These actions ensure referential integrity is maintained.
CASCADE → Automatically deletes or updates the child rows when the parent row changes.
SET NULL → Sets the foreign key value in the child row to NULL.
RESTRICT → Prevents the operation if child rows exist (default behavior in many cases).
NO ACTION → Same as RESTRICT in MySQL; the delete/update is blocked.
SET DEFAULT → Sets child rows to a default value (not supported by MySQL).
ON DELETE CASCADE → Deleting a customer automatically deletes their orders.
ON UPDATE CASCADE → Updating the customer's ID updates matching order.customer_id values.
ON DELETE SET NULL → Deletes parent row but child.foreign_key becomes NULL.
RESTRICT / NO ACTION → Operation is blocked if dependent rows exist.
These actions give fine-grained control over how relational data behaves when parent records change, ensuring data integrity according to the database design.