CODEWITHSUNDEEP
sqloracle-database
user1777711
user1777711

Reputation: 1754

How to Alter Constraint

SQL How to Alter Constraint

Below is 1 of my constraint

CONSTRAINT ACTIVEPROG_FKEY1 FOREIGN KEY(ActiveProgCode) REFERENCES PROGRAM(ActiveProgCode),

I want to add in 

ON DELETE CASCADE

to the constraint above.

How do i alter that existing constraint ACTIVEPROG_FKEY1 and add 

ON DELETE CASCADE

to constraint ACTIVEPROG_FKEY1

Consider ACTIVEPROG_FKEY1 is at Table ACTIVEPROG

Upvotes: 131

Views: 301316

Answers (2)

andy
andy

Reputation: 6079

No. We cannot alter the constraint, only thing we can do is drop and recreate it

ALTER TABLE [TABLENAME] DROP CONSTRAINT [CONSTRAINTNAME]

Foreign Key Constraint

Alter Table Table1 Add Constraint [CONSTRAINTNAME] Foreign Key (Column) References Table2 (Column) On Update Cascade On Delete Cascade

Primary Key constraint

Alter Table Table add constraint [Primary Key] Primary key(Column1,Column2,.....)

Upvotes: 22

user1819920
user1819920

Reputation: 2248

You can not alter constraints ever but you can drop them and then recreate.

Have look on this

ALTER TABLE your_table DROP CONSTRAINT ACTIVEPROG_FKEY1;

and then recreate it with ON DELETE CASCADE like this

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

hope this help

Upvotes: 196

Related Questions