How Do I Add ON DELETE CASCADE to an Existing SQL Foreign Key Constraint?

Modifying Constraints in SQL

Altering constraints in SQL is a common task for database management. One of the most frequent modifications is adding the ON DELETE CASCADE clause to an existing foreign key constraint. This clause allows you to cascade delete operations from the parent table to the child table, ensuring data integrity.

To alter an existing constraint, such as ACTIVEPROG_FKEY1, you can't directly modify it. Instead, you must drop the constraint and recreate it with the desired modifications.

Steps to Alter a Constraint:

1. Drop the existing constraint using the following syntax:

ALTER TABLE YOUR_TABLE DROP CONSTRAINT ACTIVEPROG_FKEY1;

2. Recreate the constraint with the ON DELETE CASCADE clause:

ALTER TABLE YOUR_TABLE
ADD CONSTRAINT ACTIVEPROG_FKEY1 FOREIGN KEY(ActiveProgCode)
REFERENCES PROGRAM(ActiveProgCode)
ON DELETE CASCADE;

This process will effectively update the ACTIVEPROG_FKEY1 constraint with the ON DELETE CASCADE behavior, allowing you to cascade delete operations from the PROGRAM table to the ACTIVEPROG table.

The above is the detailed content of How Do I Add ON DELETE CASCADE to an Existing SQL Foreign Key Constraint?. For more information, please follow other related articles on the PHP Chinese website!