SQL 如何在 ALTER TABLE 语句中添加“ON DELETE CASCADE”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1571581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to add 'ON DELETE CASCADE' in ALTER TABLE statement
提问by Ula Krukar
I have a foreign key constraint in my table, I want to add ON DELETE CASCADE to it.
我的表中有一个外键约束，我想向它添加 ON DELETE CASCADE 。
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
回答by Vincent Malgrat
You can not add ON DELETE CASCADEto an already existing constraint. You will have to dropand re-createthe constraint. The documentationshows that the MODIFY CONSTRAINTclause can only modify the state of a constraint(i-e: ENABLED/DISABLED...).
您不能添加ON DELETE CASCADE到已经存在的约束。你将不得不drop重新create约束。该文件显示，该MODIFY CONSTRAINT子句只能修改一个约束的状态（即：ENABLED/DISABLED...）。
回答by pradeep
First dropyour foreign key and try your above command, put add constraintinstead of modify constraint.
Now this is the command:
首先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;
回答by shindigo
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;
回答by David Silva-Barrera
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.
如您所见，这些命令必须是分开的命令，先删除然后添加。
回答by bhavani
Answer for MYSQL USERS:
MYSQL 用户的回答：
ALTER TABLE ChildTableName
DROP FOREIGN KEY `fk_table`;
ALTER TABLE ChildTableName
ADD CONSTRAINT `fk_t1_t2_tt`
FOREIGN KEY (`parentTable`)
REFERENCES parentTable (`columnName`)
ON DELETE CASCADE
ON UPDATE CASCADE;
回答by Serge Kishiko
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:
如果您想通过添加 ON DELETE/UPDATE CASCADE 来修改 FK 约束，请执行以下步骤：
NUMBER 1:
1号：
Right click on the constraint and click to Modify
右键单击约束并单击以修改
NUMBER 2:
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:
数字 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：它使我免于一堆工作，因为我想修改另一个表中引用的主键。
回答by James111
For anyone using MySQL:
对于任何使用 MySQL 的人：
If you head into your PHPMYADMINwebpage and navigate to the table that has the foreign key you want to update, all you have to do is click the Relational viewlocated in the Structuretab and change the On deleteselect menu option to Cascade.
如果你的头到你的PHPMYADMIN网页，并导航到具有要更新外键，所有你所要做的就是点击表Relational view地处Structure选项卡并更改On delete选择菜单选项Cascade。
Image shown below:
如下图所示：
回答by RedPelle
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
回答by Hassan Ali Shahzad
ALTER TABLE `tbl_celebrity_rows` ADD CONSTRAINT `tbl_celebrity_rows_ibfk_1` FOREIGN KEY (`celebrity_id`)
REFERENCES `tbl_celebrities`(`id`) ON DELETE CASCADE ON UPDATE RESTRICT;
