How to Add ON DELETE CASCADE to an Existing SQL Constraint?

Altering Constraints in SQL

You have a constraint named ACTIVEPROG_FKEY1 that needs to be updated to include ON DELETE CASCADE behavior. However, constraints cannot be directly altered in SQL. Here's how to address this situation:

Step 1: Drop the Existing Constraint

Use the following query to drop the ACTIVEPROG_FKEY1 constraint:

ALTER TABLE your_table DROP CONSTRAINT ACTIVEPROG_FKEY1;

Step 2: Recreate the Constraint with ON DELETE CASCADE

After dropping the original constraint, recreate it with the desired behavior using the following query:

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

This will add the constraint with the updated behavior you need. Keep in mind that you must ensure that the table ACTIVEPROG exists and the ActiveProgCode field is defined in both tables (your_table and PROGRAM) for the constraint to be valid.

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