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

Just a few guesses based on the information you gave:

• Does the foreign key constraint exis?
• Any typos in table and/or column names?

Please give the full table declarations and foreign key definitions, the general statement you gave looks okay ...

Answer 2

The SQL statement looks ok. Is there any existing data in the foreign key field that violates the constraint and is stopping it from being applied?

Answer 3

Hi Ula,

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