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

Changing Foreign Key Referential Action

Foreign key constraints enforce data integrity by establishing relationships between tables. One common scenario is managing the behavior when a referenced record is deleted. This article addresses how to change the referential action, specifically from ON DELETE CASCADE to ON DELETE RESTRICT.

ON DELETE CASCADE vs. ON DELETE RESTRICT

ON DELETE CASCADE: When a parent record is deleted, all matching child records are automatically deleted. This behavior can lead to unintended data loss.

ON DELETE RESTRICT: Prevents the deletion of a parent record if it has any child records. This ensures that data integrity is maintained.

SQL Command to Change Referential Action

The following SQL command modifies the referential action for a foreign key column:

ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<column_name>) REFERENCES <referenced_table_name>
ON DELETE <referential_action>

In your case, the table_name is "table1", the constraint_name is "fk_table2_id", the column_name is "fk_table2_id", the referenced_table_name is "table2", and the referential_action is "RESTRICT".

Example

Suppose you have two tables, "table1" and "table2":

CREATE TABLE table1 (
  id INT NOT NULL,
  fk_table2_id INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (fk_table2_id) REFERENCES table2(id) ON DELETE CASCADE
);

CREATE TABLE table2 (
  id INT NOT NULL,
  PRIMARY KEY (id)
);

To change the foreign key constraint in "table1" to ON DELETE RESTRICT, execute the following command:

ALTER TABLE table1
ADD CONSTRAINT fk_table2_id
FOREIGN KEY (fk_table2_id) REFERENCES table2(id) ON DELETE RESTRICT;

This will prevent the deletion of any records from "table2" if there are still child records referencing it in "table1".

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