如何在ALTER TABLE语句中添加'ON DELETE CASCADE'
我在表中有一个外键约束，我想添加ON DELETE CASCADE到它。
我试过这个：
alter table child_table_name modify constraint fk_name foreign key (child_column_name) references parent_table_name (parent_column_name) on delete cascade;
不起作用。
编辑：
外键已存在，外键列中有数据。
执行语句后得到的错误消息：
ORA-02275: such a referential constraint already exists in the table
您不能将ON DELETE CASCADE添加到已存在的约束中。 您将不得不drop并重新create约束。 该文档显示MODIFY CONSTRAINT子句只能修改约束的状态（即： ENABLED/DISABLED ...）。
首先drop你的外键，并尝试你的上面的命令，把add constraint而不是modify constraint 。 现在这是命令：
ALTER TABLE child_table_name
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column_name)
REFERENCES parent_table_name(parent_column_name)
ON DELETE CASCADE;
此PL * SQL将向DBMS_OUTPUT写入一个脚本，该脚本将删除没有删除级联的每个约束，并使用删除级联重新创建它。
注意：运行此脚本的输出需自行承担风险。 最好仔细阅读结果脚本并在执行之前进行编辑。
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;
上一篇: How to add 'ON DELETE CASCADE' in ALTER TABLE statement