CODEWITHSUNDEEP
sqloracle-database
Ula Krukar
Ula Krukar

Reputation: 12999

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

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

Upvotes: 230

Views: 406677

Answers (13)

Neha
Neha

Reputation: 11

first you have to remove constraint then add constraint with cascade like below code

alter table Course
drop FK_Course_CourseTest

ALTER TABLE Course
ADD CONSTRAINT FK_Course_CourseTest
FOREIGN KEY (Courseid)
REFERENCES CourseTest(CourseId)
on delete cascade;

Upvotes: 0

Ryabchenko Alexander
Ryabchenko Alexander

Reputation: 12330

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;

Upvotes: 1

David Silva-Barrera
David Silva-Barrera

Reputation: 1136

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.

Upvotes: 71

Ryabchenko Alexander
Ryabchenko Alexander

Reputation: 12330

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;

Upvotes: 4

Phúc Tĩnh
Phúc Tĩnh

Reputation: 1

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!

Upvotes: -1

Hassan Ali Shahzad
Hassan Ali Shahzad

Reputation: 2724

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;

Upvotes: 0

bhavani
bhavani

Reputation: 311

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;

Upvotes: 31

RedPelle
RedPelle

Reputation: 284

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

Upvotes: 4

Serge Kishiko
Serge Kishiko

Reputation: 486

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

enter image description here

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.

enter image description here

NUMBER 3:

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

enter image description here

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

Upvotes: 11

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

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...).

Upvotes: 247

pradeep
pradeep

Reputation: 1801

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;

Upvotes: 179

James111
James111

Reputation: 15903

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:

enter image description here

Upvotes: 7

shindigo
shindigo

Reputation: 1295

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;

Upvotes: 13

Related Questions