How to add 'ON DELETE CASCADE' in ALTER TABLE statement

Question

I have a foreign key constraint in my table, I want to add ON DELETE CASCADE to it.

I have tried this:

alter table child_table_name
  modify constraint fk_name
  foreign key (child_column_name)
  references parent_table_name (parent_column_name) on delete cascade;

Doesn't work.

EDIT:
Foreign key already exists, there are data in foreign key column.

The error message I get after executing the statement:

ORA-02275: such a referential constraint already exists in the table

Answer 1

You can not add ON DELETE CASCADE to an already existing constraint. You will have to drop and re-create the constraint. The documentation shows that the MODIFY CONSTRAINT clause can only modify the state of a constraint (i-e: ENABLED/DISABLED...).

Answer 2

First drop your foreign key and try your above command, put add constraint instead of modify constraint. Now this is the command:

ALTER TABLE child_table_name 
  ADD CONSTRAINT fk_name 
  FOREIGN KEY (child_column_name) 
  REFERENCES parent_table_name(parent_column_name) 
  ON DELETE CASCADE;

Answer 3

As explained before:

ALTER TABLE TABLENAME
drop CONSTRAINT FK_CONSTRAINTNAME;

ALTER TABLE TABLENAME
ADD CONSTRAINT FK_CONSTRAINTNAME
    FOREIGN KEY (FId)
    REFERENCES OTHERTABLE
        (Id)
    ON DELETE CASCADE ON UPDATE NO ACTION;

As you can see those have to be separated commands, first dropping then adding.

Answer 4

Answer for MYSQL USERS:

ALTER TABLE ChildTableName 
DROP FOREIGN KEY `fk_table`;
ALTER TABLE ChildTableName 
ADD CONSTRAINT `fk_t1_t2_tt`
  FOREIGN KEY (`parentTable`)
  REFERENCES parentTable (`columnName`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

Answer 5

This PL*SQL will write to DBMS_OUTPUT a script that will drop each constraint that does not have delete cascade and recreate it with delete cascade.

NOTE: running the output of this script is AT YOUR OWN RISK. Best to read over the resulting script and edit it before executing it.

DECLARE
      CURSOR consCols (theCons VARCHAR2, theOwner VARCHAR2) IS
        select * from user_cons_columns
            where constraint_name = theCons and owner = theOwner
            order by position;
      firstCol BOOLEAN := TRUE;
    begin
        -- For each constraint
        FOR cons IN (select * from user_constraints
            where delete_rule = 'NO ACTION'
            and constraint_name not like '%MODIFIED_BY_FK'  -- these constraints we do not want delete cascade
            and constraint_name not like '%CREATED_BY_FK'
            order by table_name)
        LOOP
            -- Drop the constraint
            DBMS_OUTPUT.PUT_LINE('ALTER TABLE ' || cons.OWNER || '.' || cons.TABLE_NAME || ' DROP CONSTRAINT ' || cons.CONSTRAINT_NAME || ';');
            -- Re-create the constraint
            DBMS_OUTPUT.PUT('ALTER TABLE ' || cons.OWNER || '.' || cons.TABLE_NAME || ' ADD CONSTRAINT ' || cons.CONSTRAINT_NAME 
                                        || ' FOREIGN KEY (');
            firstCol := TRUE;
            -- For each referencing column
            FOR consCol IN consCols(cons.CONSTRAINT_NAME, cons.OWNER)
            LOOP
                IF(firstCol) THEN
                    firstCol := FALSE;
                ELSE
                    DBMS_OUTPUT.PUT(',');
                END IF;
                DBMS_OUTPUT.PUT(consCol.COLUMN_NAME);
            END LOOP;                                    

            DBMS_OUTPUT.PUT(') REFERENCES ');

            firstCol := TRUE;
            -- For each referenced column
            FOR consCol IN consCols(cons.R_CONSTRAINT_NAME, cons.R_OWNER)
            LOOP
                IF(firstCol) THEN
                    DBMS_OUTPUT.PUT(consCol.OWNER);
                    DBMS_OUTPUT.PUT('.');
                    DBMS_OUTPUT.PUT(consCol.TABLE_NAME);        -- This seems a bit of a kluge.
                    DBMS_OUTPUT.PUT(' (');
                    firstCol := FALSE;
                ELSE
                    DBMS_OUTPUT.PUT(',');
                END IF;
                DBMS_OUTPUT.PUT(consCol.COLUMN_NAME);
            END LOOP;                                    

            DBMS_OUTPUT.PUT_LINE(')  ON DELETE CASCADE  ENABLE VALIDATE;');
        END LOOP;
    end;

Answer 6

Here is an handy solution! I'm using SQL Server 2008 R2.

As you want to modify the FK constraint by adding ON DELETE/UPDATE CASCADE, follow these steps:

NUMBER 1:

Right click on the constraint and click to Modify

NUMBER 2:

Choose your constraint on the left side (if there are more than one). Then on the right side, collapse "INSERT And UPDATE Specification" point and specify the actions on Delete Rule or Update Rule row to suit your need. After that, close the dialog box.

NUMBER 3:

The final step is to save theses modifications (of course!)

PS: It's saved me from a bunch of work as I want to modify a primary key referenced in another table.

Answer 7

For anyone using MySQL:

If you head into your PHPMYADMIN webpage and navigate to the table that has the foreign key you want to update, all you have to do is click the Relational view located in the Structure tab and change the On delete select menu option to Cascade.

Image shown below:

Answer 8

If you want to change a foreign key without dropping it you can do:

ALTER TABLE child_table_name  WITH CHECK ADD FOREIGN KEY(child_column_name)
REFERENCES parent_table_name (parent_column_name) ON DELETE CASCADE

Answer 9

for postgresql

BEGIN TRANSACTION ;
ALTER TABLE bank_accounts
    DROP CONSTRAINT bank_accounts_company_id_fkey;

ALTER TABLE bank_accounts
    ADD CONSTRAINT bank_accounts_company_id_fkey FOREIGN KEY (company_id)
        REFERENCES companies (id)
        ON DELETE CASCADE;
END;

Answer 10

ALTER TABLE `tbl_celebrity_rows` ADD CONSTRAINT `tbl_celebrity_rows_ibfk_1` FOREIGN KEY (`celebrity_id`) 
REFERENCES `tbl_celebrities`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;

Answer 11

Also good idea is to use transaction

begin transaction;
    alter table  child_table_name  drop constraint fk_name;
    alter table child_table_name add constraint fk_name
            foreign key (child_column_name)  
            references parent_table_name (parent_column_name)  on delete cascade;
end;

Answer 12

MySQL workbench img Right click at the table you want to alter and click alter table, then click Foreign Keys. You can see Foreign Keys Options on the right side and just select cascade and click apply!