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

2021-11-20 00:00:00 foreign-keys foreign-key-relationship mysql

我已经建立了一个表，其中包含一个带有外键的列，设置为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
:

相关文章