如何在ALTER TABLE语句中添加“ON DELETE CASCADE”

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

7 个解决方案

#1

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 (i-e: ENABLED/DISABLED...).

不能将删除级联添加到已存在的约束中。您将不得不删除并重新创建约束。文档显示修改约束子句只能修改约束的状态(i-e: ENABLED/DISABLED…)。

#2

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;

#3

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.

这个PL*SQL将向DBMS_OUTPUT写入一个脚本，该脚本将删除没有删除级联的每个约束，并使用删除级联重新创建它。

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;

#4

As explained before:

正如之前解释:

ALTER TABLE TABLEName
drop CONSTRAINT FK_CONSTRAINTNAME;

ALTER TABLE TABLENAME
ADD CONSTRAINT FK_CONSTRAINTNAME
    FOREIGN KEY (FId)
    REFERENCES OTHERTABLE
        (Id)
    ON DELETE CASCADE ON UPDATE NO ACTION;

As you can see those have to be separated commands, first dropping then adding.

正如您所看到的，这些必须是分开的命令，首先是删除，然后添加。

#5

If you want to change a foreign key without dropping it you can do:

如果你想换一把外键而不掉下来，你可以这样做:

ALTER TABLE child_table_name  WITH CHECK ADD FOREIGN KEY(child_column_name)
REFERENCES parent_table_name (parent_column_name) ON DELETE CASCADE

#6

For anyone using MySQL:

If you head into your PHPMYADMIN webpage and navigate to the table that has the foreign key you want to update, all you have to do is click the Relational view located in the Structure tab and change the On delete select menu option to Cascade.

如果您进入PHPMYADMIN页面并导航到具有要更新的外键的表，那么只需单击结构选项卡中的关系视图，并更改On delete select菜单选项以实现级联。

Image shown below:

图所示:

#7

Here is an handy solution! I'm using SQL Server 2008 R2.

这里有一个简便的解决方案!我使用的是SQL Server 2008 R2。

As you want to modify the FK constraint by adding ON DELETE/UPDATE CASCADE, follow these steps:

如果您想通过添加删除/更新级联来修改FK约束，请执行以下步骤:

NUMBER 1:

Right click on the constraint and click to Modify

右键单击约束并单击修改

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.

选择左侧的约束(如果有多于一个)。然后在右边，折叠“插入和更新规范”点，并指定删除规则或更新规则行上的操作，以满足您的需要。之后，关闭对话框。

NUMBER 3:

The final step is to save theses modifications (of course!)

最后一步是保存这些修改(当然!)

PS: It's saved me from a bunch of work as I want to modify a primary key referenced in another table.

PS:因为我想修改另一个表中引用的主键，所以这让我省去了很多工作。