MySQL 如何更改外键引用动作？（行为）

How to change the foreign key referential action? (behavior)

提问by Moak

I have set up a table that contains a column with a foreign key, set to ON DELETE CASCADE(delete child when parent is deleted)

我已经建立了一个表，其中包含一个带有外键的列，设置为ON DELETE CASCADE（删除父项时删除子项）

What would the SQL command be to change this to ON DELETE RESTRICT? (can't delete parent if it has children)

将这个更改为的 SQL 命令是ON DELETE RESTRICT什么？（如果有孩子，则无法删除父母）

回答by Grijesh Chauhan

_{Old question but adding answer so that one can get help}

_{老问题，但添加答案以便获得帮助}

Its two step process:

它的两步过程：

Suppose, a table1has a foreign keywith column name fk_table2_id, with constraintname fk_nameand table2is referred table with key t2(something like below in my diagram).

假设， atable1有一个外键，列名fk_table2_id，约束名fk_name，table2并被引用表与键t2（类似于我的图表中的下图）。

   table1 [ fk_table2_id ] --> table2 [t2]

First step, DROP old CONSTRAINT: (reference)

第一步，DROP old CONSTRAINT：（参考）

ALTER TABLE `table1` 
DROP FOREIGN KEY `fk_name`;  

_{notice constraint is deleted, column is not deleted}

_{通知约束被删除，列没有被删除}

Second step, ADD new CONSTRAINT:

第二步，添加新的约束：

ALTER TABLE `table1`  
ADD CONSTRAINT `fk_name` 
    FOREIGN KEY (`fk_table2_id`) REFERENCES `table2` (`t2`) ON DELETE CASCADE;  

_{adding constraint, column is already there}

_{添加约束，列已经存在}

Example:

例子：

I have a UserDetailstable refers to Userstable:

我有一个UserDetails表指的是Users表：

mysql> SHOW CREATE TABLE UserDetails;
:
:
 `User_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`Detail_id`),
  KEY `FK_User_id` (`User_id`),
  CONSTRAINT `FK_User_id` FOREIGN KEY (`User_id`) REFERENCES `Users` (`User_id`)
:
:

First step:

第一步：

mysql> ALTER TABLE `UserDetails` DROP FOREIGN KEY `FK_User_id`;
Query OK, 1 row affected (0.07 sec)  

Second step:

第二步：

mysql> ALTER TABLE `UserDetails` ADD CONSTRAINT `FK_User_id` 
    -> FOREIGN KEY (`User_id`) REFERENCES `Users` (`User_id`) ON DELETE CASCADE;
Query OK, 1 row affected (0.02 sec)  

result:

结果：

mysql> SHOW CREATE TABLE UserDetails;
:
:
`User_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`Detail_id`),
  KEY `FK_User_id` (`User_id`),
  CONSTRAINT `FK_User_id` FOREIGN KEY (`User_id`) REFERENCES 
                                       `Users` (`User_id`) ON DELETE CASCADE
:

回答by Romuald Brunet

You can do this in one query ifyou're willing to change its name:

如果您愿意更改其名称，则可以在一个查询中执行此操作：

ALTER TABLE table_name
  DROP FOREIGN KEY `fk_name`,
  ADD CONSTRAINT `fk_name2` FOREIGN KEY (`remote_id`)
    REFERENCES `other_table` (`id`)
    ON DELETE CASCADE;

This is useful to minimize downtime if you have a large table.

如果您有一张大桌子，这有助于最大限度地减少停机时间。

回答by pascal

ALTER TABLE DROP FOREIGN KEY fk_name;
ALTER TABLE ADD FOREIGN KEY fk_name(fk_cols)
            REFERENCES tbl_name(pk_names) ON DELETE RESTRICT;

回答by Vasiliy

Remember that MySQL keeps a simple index on a column after deleting foreign key. So, if you need to change 'references' column you should do it in 3 steps

请记住，在删除外键后，MySQL 会在列上保留一个简单的索引。因此，如果您需要更改“引用”列，则应分 3 个步骤进行

• drop original FK
• drop an index (names as previous fk, using drop indexclause)
• create new FK

• 放弃原来的FK
• 删除索引（名称为以前的 fk，使用drop index子句）
• 创建新的 FK

回答by stamster

You can simply use one query to rule them all: ALTER TABLE products DROP FOREIGN KEY oldConstraintName, ADD FOREIGN KEY (product_id, category_id) REFERENCES externalTableName (foreign_key_name, another_one_makes_composite_key) ON DELETE CASCADE ON UPDATE CASCADE

您可以简单地使用一个查询来统治所有这些： ALTER TABLE products DROP FOREIGN KEY oldConstraintName, ADD FOREIGN KEY (product_id, category_id) REFERENCES externalTableName (foreign_key_name, another_one_makes_composite_key) ON DELETE CASCADE ON UPDATE CASCADE

回答by DavidSM

I had a bunch of FKs to alter, so I wrote something to make the statements for me. Figured I'd share:

我有一堆 FK 需要修改，所以我写了一些东西来为我做陈述。想我会分享：

SELECT

CONCAT('ALTER TABLE `' ,rc.TABLE_NAME,
    '` DROP FOREIGN KEY `' ,rc.CONSTRAINT_NAME,'`;')
, CONCAT('ALTER TABLE `' ,rc.TABLE_NAME,
    '` ADD CONSTRAINT `' ,rc.CONSTRAINT_NAME ,'` FOREIGN KEY (`',kcu.COLUMN_NAME,
    '`) REFERENCES `',kcu.REFERENCED_TABLE_NAME,'` (`',kcu.REFERENCED_COLUMN_NAME,'`) ON DELETE CASCADE;')

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
LEFT OUTER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON kcu.TABLE_SCHEMA = rc.CONSTRAINT_SCHEMA
    AND kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
WHERE DELETE_RULE = 'NO ACTION'
AND rc.CONSTRAINT_SCHEMA = 'foo'