How to Change a Foreign Key Referential Action from ON DELETE CASCADE to ON DELETE RESTRICT?

Altering Foreign Key Referential Action

Foreign key constraints enforce referential integrity by ensuring that rows in a child table cannot be deleted if they are referenced by rows in a parent table. By default, foreign key constraints are set to ON DELETE CASCADE, which automatically deletes child rows when their parent rows are deleted.

If you wish to change the referential action to ON DELETE RESTRICT, which prevents the deletion of parent rows if they have associated child rows, you can do so using the following SQL commands:

Step 1: Drop the Existing Constraint

ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

This command removes the existing foreign key constraint, but leaves the foreign key column in place.

Step 2: Add the New Constraint with ON DELETE RESTRICT

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (foreign_key_column) REFERENCES parent_table (parent_key) ON DELETE RESTRICT;

This command creates a new foreign key constraint with ON DELETE RESTRICT, effectively preventing parent rows from being deleted if they have related child rows.

Note: Before making these changes, ensure that the table data is consistent with the desired referential action.

The above is the detailed content of How to Change a Foreign Key Referential Action from ON DELETE CASCADE to ON DELETE RESTRICT?. For more information, please follow other related articles on the PHP Chinese website!