How to Change a Foreign Key\'s ON DELETE Action from CASCADE to RESTRICT?

Modifying Foreign Key Referential Action

In database design, foreign keys are crucial for maintaining data integrity by establishing relationships between tables. When defining a foreign key, you specify the referential action that governs what happens to child records when a referenced parent record is modified or deleted.

One common referential action is ON DELETE CASCADE, which automatically deletes child records when their parent record is deleted. To change this behavior to ON DELETE RESTRICT, preventing the deletion of parent records with associated child records, you can use the following steps:

Step 1: Drop Existing Foreign Key Constraint

Execute the following SQL command to remove the existing foreign key constraint:

ALTER TABLE `table_name` DROP FOREIGN KEY `constraint_name`;

where table_name is the table containing the foreign key column and constraint_name is the name of the foreign key constraint.

Step 2: Add New Foreign Key Constraint with ON DELETE RESTRICT

Now, create a new foreign key constraint with the desired ON DELETE RESTRICT behavior:

ALTER TABLE `table_name` ADD CONSTRAINT `new_constraint_name` FOREIGN KEY (`foreign_key_column`) 
REFERENCES `referenced_table` (`referenced_column`) 
ON DELETE RESTRICT;

where foreign_key_column is the column in table_name referencing referenced_table, referenced_table is the table containing the referenced column, referenced_column is the column in referenced_table referenced by the foreign key, and new_constraint_name is the name of the new foreign key constraint.

Example:

Consider a table UserDetails with a foreign key User_id referencing another table Users. To change the referential action from ON DELETE CASCADE to ON DELETE RESTRICT, follow these steps:

1. Drop existing foreign key constraint:

   ALTER TABLE `UserDetails` DROP FOREIGN KEY `FK_User_id`;

   Copy after login

2. Add new foreign key constraint with ON DELETE RESTRICT:

   ALTER TABLE `UserDetails` ADD CONSTRAINT `FK_User_id` 
   FOREIGN KEY (`User_id`) 
   REFERENCES `Users` (`User_id`) 
   ON DELETE RESTRICT;

   Copy after login

By performing these steps, you can modify the referential action of a foreign key to ensure that the desired data integrity rules are enforced in your database.

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