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;

It doesn't work.

EDIT:

Foreign key already exists, there are data in the foreign key columns.

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 should drop and re-create the constraint. 

Have a look at this documentation  shows that the MODIFY CONSTRAINT clause can only modify the state of a constraint  (i-e: ENABLED/DISABLED...).

Answer 2

Usually you'll have to drop the existing constraint, and then again add a new constraint by using the ALTER TABLE statement. In this topic we'll guide you how you may do it:

Firstly, you need to delete any current foreign key constraint. In this step, determine the name of the foreign key constraint you would like to change. You might have found this information when opening up your database management program, or by using the data dictionary. With the name of your constraint in hand you're ready to drop the existing one:

ALTER TABLE child_table
DROP CONSTRAINT constraint_name;

Then add ON DELETE CASCADE during the addition of the new foreign key constraint. Finally, you can add again the foreign key constraint by specifying ON DELETE CASCADE now:

ALTER TABLE child_table
ADD CONSTRAINT constraint_name
FOREIGN KEY (foreign_key_column)
REFERENCES parent_table (primary_key_column)
ON DELETE CASCADE;