如何更改外键引用动作?(行为)

问题描述

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

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 RESTRICT 的 SQL 命令是什么?(如果有孩子，则无法删除父母)

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

推荐答案

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

它的两步过程:

假设，一个table1有一个外键，列名fk_table2_id，约束名fk_name 和 table2 是带有键 t2 的引用表(在我的图表中类似于下面的内容).

Suppose, a table1 has a foreign key with column name fk_table2_id, with constraint name fk_name and table2 is referred table with key t2 (something like below in my diagram).

   table1 [ fk_table2_id ] --> table2 [t2]

第一步，DROP old CONSTRAINT:(参考)

First step, DROP old CONSTRAINT: (reference)

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

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

第二步，添加新的CONSTRAINT:

Second step, ADD new CONSTRAINT:

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

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

示例:

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

I have a UserDetails table refers to Users table:

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`)
:
:

第一步:

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

第二步:

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)  

结果:

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
:

这篇关于如何更改外键引用动作?(行为)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！