Mysql 报错：Cannot add or update a child row: a foreign key constraint fails

分手后的思念是犯贱 2023-01-13 06:12 98阅读 0赞

**错误信息：**

insert into student (id,name,age,tid) VALUES ( 1,'张三',18,1)
    > 1452 - Cannot add or update a child row: a foreign key constraint fails (`db123`.`student`, CONSTRAINT `tid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`))
    > 时间: 0.12s

表结构：

`student`表

CREATE TABLE `student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `tid` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `tid` (`tid`),
      CONSTRAINT `tid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70]

`teacher`表

CREATE TABLE `teacher` (
      `id` int(11) NOT NULL,
      `name` varchar(255) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70 1]

`student`表中的`tid`是`teacher`表中`id`的外键

再次看下**错误信息**：

翻译下来：**不能添加或修改子行，外键约束失败**

**报错原因：**

说白了就是：主表`teacher`中没有`id`为`1`的数据，所以子表`student`中就不能添加或修改`tid`为`1`的数据。

**解决方案：**

主表`teacher`中添加`id`为`1`的数据，即可。

insert into teacher (id,name,age) VALUES(1,'李老师',30)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70 2]

再次执行`insert into student (id,name,age,tid) VALUES ( 1,'张三',18,1)`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70 3]  
问题解决。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70]: /images/20221022/9b0e6b96be324acd8ed89fbf1c3ea72a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70 1]: /images/20221022/12df5c67b37542db9591446ab0dd0e9f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70 2]: /images/20221022/13aa9483f56e4f5eb5259997fe8fb8de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mjc0MDUzMA_size_16_color_FFFFFF_t_70 3]: /images/20221022/144b9439b61e49e8b6696e697f0f1d8e.png