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
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 (ie: ENABLED/DISABLED ...).
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;
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;
上一篇: 无法通过GridView更新数据