Cannot add or update a child row, MySQL外键错误解析

一时失言乱红尘 2022-06-06 06:20 156阅读 0赞

在MySQL 3.23.44版本后，InnoDB引擎类型的表支持了外键约束。  
外键的使用条件：  
1.两个表必须是 InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，但至少目前不支持）；  
2.外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立；   
3.外键关系的两个表的列必须是数据类型相似，也就是可 以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作；

外键的定义语法：  
\[CONSTRAINT symbol\] FOREIGN KEY \[id\] (index\_col\_name, ...)  
    REFERENCES tbl\_name (index\_col\_name, ...)  
    \[ON DELETE \{RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT\}\]  
    \[ON UPDATE \{RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT\}\]  
该语法 可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。  
ON DELETE、ON UPDATE表示事件触发限制，可设参数：  
RESTRICT（限 制外表中的外键改动）  
CASCADE（跟随外键改动）c  
SET NULL（设空值）  
SET DEFAULT（设默认值）  
NO ACTION（无动作，默认的）

搞个例子，简单演示一下使用，做dage和xiaodi两个表，大哥表是主键，小弟表是外键：  
建表：

1  ![None.gif][]  CREATE     TABLE   \`dage\` (  
  2  ![None.gif][]   \`id\`   **int**  (  **11**  )   NOT     NULL   auto\_increment,  
  3  ![None.gif][]   \`name\`   **varchar**  (  **32**  )   default     ''  ,  
  4  ![None.gif][]     PRIMARY     KEY    (\`id\`)  
  5  ![None.gif][] ) ENGINE  =  InnoDB   DEFAULT   CHARSET  =  latin1；  
  6  ![None.gif][]   
  7  ![None.gif][]  CREATE     TABLE   \`xiaodi\` (  
  8  ![None.gif][]   \`id\`   **int**  (  **11**  )   NOT     NULL   auto\_increment,  
  9  ![None.gif][]   \`dage\_id\`   **int**  (  **11**  )   default     NULL  ,  
 10  ![None.gif][]   \`name\`   **varchar**  (  **32**  )   default     ''  ,  
 11  ![None.gif][]     PRIMARY     KEY    (\`id\`),  
 12  ![None.gif][]     KEY   \`dage\_id\` (\`dage\_id\`),  
 13  ![None.gif][]     CONSTRAINT   \`xiaodi\_ibfk\_1\`   FOREIGN     KEY   (\`dage\_id\`)   REFERENCES   \`dage\` (\`id\`)  
 14  ![None.gif][] ) ENGINE  =  InnoDB   DEFAULT   CHARSET  =  latin1；

插入个大哥：

1  ![None.gif][]  mysql  >     insert     into   dage(name)   values  (  '  铜锣湾  '  );  
 2  ![None.gif][] Query OK,   **1**   row affected (  **0.01**   sec)  
 3  ![None.gif][] mysql  >     select     \*     from   dage;  
 4  ![None.gif][]  \+  \--  \--+--------+    
 5  ![None.gif][]  |   id   |   name     |    
 6  ![None.gif][]  \+  \--  \--+--------+    
 7  ![None.gif][]  |      **1**     |   铜锣湾   |    
 8  ![None.gif][]  \+  \--  \--+--------+    
 9  ![None.gif][]  **1**   row   in     set   (  **0.00**   sec)

插入个小弟：

1  ![None.gif][]  mysql  >     insert     into   xiaodi(dage\_id,name)   values  (  **1**  ,  '  铜锣湾\_小弟A  '  );  
 2  ![None.gif][] Query OK,   **1**   row affected (  **0.02**   sec)  
 3  ![None.gif][]   
 4  ![None.gif][] mysql  >     select     \*     from   xiaodi;  
 5  ![None.gif][]  \+  \--  \--+---------+--------------+    
 6  ![None.gif][]  |   id   |   dage\_id   |   name           |    
 7  ![None.gif][]  \+  \--  \--+---------+--------------+    
 8  ![None.gif][]  |      **1**     |           **1**     |   铜锣湾\_小弟A   |    
 9  ![None.gif][]  \+  \--  \--+---------+--------------+

把大哥删除：

1  ![None.gif][]  mysql  >     delete     from   dage   where   id  =  **1**  ;  
 2  ![None.gif][] ERROR   **1451**   (  **23000**  ): Cannot   delete     or     update   a parent row: a   foreign     key     constraint   fails (\`bstar  /  xiaodi\`,  CONSTRAINT   \`xiaodi\_ibfk\_1\`   FOREIGN     KEY   (\`dage\_id\`)   REFERENCES   \`dage\` (\`id\`))

提示：不行呀，有约束的，大哥下面还有小弟，可不能扔下我们不管呀！

插入一个新的小弟：

1  ![None.gif][]  mysql  >     insert     into   xiaodi(dage\_id,name)   values  (  **2**  ,  '  旺角\_小弟A  '  );                
 2  ![None.gif][] ERROR   **1452**   (  **23000**  ): Cannot   add     or     update   a child row: a   foreign     key     constraint   fails (\`bstar  /  xiaodi\`,   CONSTRAINT  \`xiaodi\_ibfk\_1\`   FOREIGN     KEY   (\`dage\_id\`)   REFERENCES   \`dage\` (\`id\`))  
 3  ![None.gif][]

提示：小子，想造反呀！你还没大哥呢！

把外键约束增加事件触发限制：

1  ![None.gif][]  mysql  >   show   create     table   xiaodi;  
  2  ![None.gif][] ![dot.gif][] ![dot.gif][]   
  3  ![None.gif][]     CONSTRAINT   \`xiaodi\_ibfk\_1\`   FOREIGN     KEY   (\`dage\_id\`)   REFERENCES   \`dage\` (\`id\`)  
  4  ![None.gif][] ![dot.gif][] ![dot.gif][]   
  5  ![None.gif][] mysql  >     alter     table   xiaodi   drop     foreign     key   xiaodi\_ibfk\_1;   
  6  ![None.gif][] Query OK,   **1**   row affected (  **0.04**   sec)  
  7  ![None.gif][] Records:   **1**    Duplicates:   **0**    Warnings:   
  8  ![None.gif][] mysql  >     alter     table   xiaodi   add     foreign     key  (dage\_id)   references   dage(id)   on     delete     cascade     on     update     cascade  ;  
  9  ![None.gif][] Query OK,   **1**   row affected (  **0.04**   sec)  
 10  ![None.gif][] Records:   **1**    Duplicates:   **0**    Warnings:   **0**

再次试着把大哥删了：

1  ![None.gif][]  mysql  >     delete     from   dage   where   id  =  **1**  ;  
 2  ![None.gif][] Query OK,   **1**   row affected (  **0.01**   sec)  
 3  ![None.gif][]   
 4  ![None.gif][] mysql  >     select     \*     from   dage;  
 5  ![None.gif][] Empty   set   (  **0.01**   sec)  
 6  ![None.gif][]   
 7  ![None.gif][] mysql  >     select     \*     from   xiaodi;  
 8  ![None.gif][] Empty   set   (  **0.00**   sec)

得，这回对应的小弟也没了，没办法，谁让你跟我on delete cascade了呢！

例子说明的应该蛮清楚了吧，其他功能对应手册自己实践吧！:-)

转自： http://www.cppblog.com/wolf/articles/69089.html

[None.gif]: /images/20220606/9397f7a566264f4a8f88b59270407886.png
[dot.gif]: /images/20220606/633568e0b1cc4259bf9d7463724650f7.png