如何更改外键引用操作？ （行为） [英] How to change the foreign key referential action? (behavior)

问题描述

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

SQL命令将是什么将其更改为 ON DELETE RESTRICT ？ （不能删除父母如果有孩子）

解决方案

_{老问题，但添加答案，以便可以得到帮助 b
$ b b
$ b}

假设一个 table1 有一个列名为 fk_table2_id 的外键，其中约束 name fk_name 和 table2 被引用的表格 t2 （ ）。

  table1 [fk_table2_id]  - > table2 [t2] 
  

第一步，DROP旧CONSTRAINT： ）

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

_{通知约束被删除，列不被删除} 第二步，添加新的CONSTRAINT：

 
  code> ALTER TABLE`table1` 
 ADD CONSTRAINT`fk_name` 
 FOREIGN KEY（`fk_table2_id`）参考`table2`（`t2`）ON DELETE CASCADE; 
  

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

示例：

我有一个 UserDetails 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）
  
 第二步： / strong> 
  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 
`User`（`User_id`）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) 


What  would the SQL command be to change this to ON DELETE RESTRICT? (can't delete parent if it has children)
解决方案
Old question but adding answer so that one can get help

Its two step process:     

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]
First step, DROP old  CONSTRAINT: (reference) 
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 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`)
:
:
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
:


                        
这篇关于如何更改外键引用操作？ （行为）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！