openlang

Adding cascading deletion to an existing constraint

SQL changing an existing constraint

Here is a constraint I have

CONSTRAINT CURRENTPLAN_FK FOREIGN KEY(CurrentPlanCode) REFERENCES PLAN(CurrentPlanCode),

I'd like to include

ON DELETE CASCADE

in that constraint.

How can i change the existing CURRENTPLAN_FK constraint to include

ON DELETE CASCADE

in CURRENTPLAN_FK

Assume CURRENTPLAN_FK belongs to the CURRENTPLAN table

The conversation continues 2

Follow the reasoning
  1. Permalink

    No. Changing the constraint isn't possible, the option available is to remove it and create it again

    ALTER TABLE [TARGETTABLE] DROP CONSTRAINT [KEYNAME]

    A foreign key constraint

    Alter Table FirstTable Add Constraint [KEYNAME]
    Foreign Key (Field) References SecondTable (Field) On Update Cascade On Delete Cascade

    A primary key constraint

    Alter Table Target add constraint [Main Key]
    Primary key(Field1,Field2,.....)
  2. Permalink

    Constraints cannot ever be changed, although you can remove them and create replacements.

    Take a look here

    ALTER TABLE target_table
    DROP CONSTRAINT CURRENTPLAN_FK;

    then create the replacement using ON DELETE CASCADE this way

    ALTER TABLE target_table ADD CONSTRAINT CURRENTPLAN_FK
    FOREIGN KEY(CurrentPlanCode) REFERENCES PLAN(CurrentPlanCode)
    ON DELETE CASCADE;

    hope that help

Other sides of the same question

  1. Missing field through a derived table in SQL

    I'm executing the following SQL statement: It produces the following error: #1054 - Unknown column 'shipping.post_id' in 'on…

    Replies 2Latest activity sql
  2. Testing D arrays for a matching entry

    with associative arrays this is possible how is this done with ordinary arrays? I'd like a validation expression, for example

    Replies 2Latest activity d
  3. Removing an optional column safely with PostgreSQL 9+

    I want to remove a column belonging to a table. What's the way to determine whether that column is present? I read the manual…

    Replies 2Latest activity sql