Problem
SQL How to Alter Constraint
One of my constraints is shown here.
CONSTRAINT ACTIVEPROG_FKEY1 FOREIGN KEY(ActiveProgCode) REFERENCES PROGRAM(ActiveProgCode),
I’d like to add something.
ON DELETE CASCADE
to the preceding constraint
How do I add a constraint to the current ACTIVEPROG FKEY1 constraint?
ON DELETE CASCADE
to constraint ACTIVEPROG_FKEY1
Consider the fact that ACTIVEPROG FKEY1 is stored in Table ACTIVEPROG.
Asked by user1777711
Solution #1
Constraints cannot be changed at any time, but they can be dropped and recreated.
Look over this.
ALTER TABLE your_table DROP CONSTRAINT ACTIVEPROG_FKEY1;
Then, like this, recreate it with ON DELETE CASCADE.
ALTER TABLE your_table
add CONSTRAINT ACTIVEPROG_FKEY1 FOREIGN KEY(ActiveProgCode) REFERENCES PROGRAM(ActiveProgCode)
ON DELETE CASCADE;
hope this help
Answered by user1819920
Solution #2
No. We can’t change the constraint; the only option is to drop and recreate it.
ALTER TABLE [TABLENAME] DROP CONSTRAINT [CONSTRAINTNAME]
Alter Table Table1 Add Constraint [CONSTRAINTNAME] Foreign Key (Column) References Table2 (Column) On Update Cascade On Delete Cascade
Alter Table Table add constraint [Primary Key] Primary key(Column1,Column2,.....)
Answered by andy
Post is based on https://stackoverflow.com/questions/13244889/how-to-alter-constraint