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

user_110677 0
Viewed 304425

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
oracle sql
11 Answers

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;
user_98622840

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.

user_87190070

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;
user_65157550 
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;
user_7707040

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!

user_160386260
Related