Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails

问题描述

今天在向mysql数据库中一张表插入记录时，报错了，完整的错误信息描述是这样：

0	8	15:22:07	INSERT INTO `cuinn_menu` VALUES ('3', '2021-06-14 15:57:24', '2021-08-08 19:37:44', ' mdi-menu', '菜单管理', '1', '/menu/list', '10', '\0', '')	Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`db_boot_base`.`cuinn_menu`, CONSTRAINT `FKitpl2u3dbuubknu7p41mbiv05` FOREIGN KEY (`parent_id`) REFERENCES `cuinn_menu` (`id`))	0.015 sec

我的表是一张菜单表结构是这样的：

---

原因分析：

这个报错信息的意思是我在向 cuinn_menu 表中插入数据时，违反了 FKitpl2u3dbuubknu7p41mbiv05 这个外键约束。由于由于我这张表的外键是parent_id也是这张表本身的主键id.看到这条插入的语句，可以看到id是3，parent_id是10，但是在这张表中并没有id为10的记录，因此，我们无法将这条记录插入到表中

---

解决方案：

通过以上描述得知，我在创建一个子菜单之前它必定先有一个父菜单，我的所有插入sql如下：

INSERT INTO `cuinn_menu` VALUES ('3', '2021-06-14 15:57:24', '2021-08-08 19:37:44', ' mdi-menu', '菜单管理', '1', '/menu/list', '10', '\0', '');
INSERT INTO `cuinn_menu` VALUES ('4', '2021-06-14 15:58:40', '2021-08-09 21:01:47', ' mdi-plus', '新增', '2', '/menu/add', '3', '\0', '');
INSERT INTO `cuinn_menu` VALUES ('8', '2021-06-17 21:10:41', '2021-08-09 20:40:05', ' mdi-eyedropper-variant', '编辑', '3', 'edit(\'/menu/edit\')', '3', '', '');
INSERT INTO `cuinn_menu` VALUES ('9', '2021-06-17 21:11:13', '2021-08-09 20:59:04', ' mdi-window-close', '删除', '4', 'del(\'/menu/delete\')', '3', '', '');
INSERT INTO `cuinn_menu` VALUES ('10', '2021-06-17 21:34:18', '2021-06-17 21:34:18', ' mdi-settings', '系统设置', '0', '', null, '\0', '');
INSERT INTO `cuinn_menu` VALUES ('11', '2021-06-17 21:37:04', '2021-06-17 21:37:04', ' mdi-account-settings-variant', '角色管理', '5', '/role/list', '10', '\0', '');
INSERT INTO `cuinn_menu` VALUES ('13', '2021-06-17 21:40:58', '2021-08-09 21:02:02', ' mdi-account-plus', '新增', '6', '/role/add', '11', '\0', '');
INSERT INTO `cuinn_menu` VALUES ('15', '2021-08-08 19:38:34', '2021-08-08 19:38:34', ' mdi-account', '用户管理', '1', '/user/list', '10', '\0', '');
INSERT INTO `cuinn_menu` VALUES ('16', '2021-08-08 19:39:30', '2021-08-09 21:02:12', ' mdi-account-plus', '新增', '2', '/user/add', '15', '\0', '');
INSERT INTO `cuinn_menu` VALUES ('17', '2021-08-08 19:40:44', '2021-08-09 21:30:45', ' mdi-account-edit', '编辑', '3', 'edit(\'/user/edit\')', '15', '', '');
INSERT INTO `cuinn_menu` VALUES ('18', '2021-08-08 19:41:08', '2021-08-09 20:59:27', ' mdi-account-remove', '删除', '4', 'del(\'/user/delete\')', '15', '', '');
INSERT INTO `cuinn_menu` VALUES ('20', '2021-08-09 21:21:47', '2021-08-09 21:21:47', ' mdi-account-edit', '编辑', '7', 'edit(\'/role/edit\')', '11', '', '');
INSERT INTO `cuinn_menu` VALUES ('21', '2021-08-09 21:23:40', '2021-08-09 21:23:40', ' mdi-account-remove', '删除', '8', 'del(\'/role/delete\')', '11', '', '');
INSERT INTO `cuinn_menu` VALUES ('22', '2021-08-10 21:31:05', '2021-08-10 21:33:21', ' mdi-arrow-up-bold-circle', '上传图片', '0', '/upload/upload_photo', '15', '\0', '\0');
INSERT INTO `cuinn_menu` VALUES ('23', '2021-08-12 19:51:26', '2021-08-12 19:53:33', ' mdi-tag', '日志管理', '0', '/system/operator_log_list', '10', '\0', '');
INSERT INTO `cuinn_menu` VALUES ('24', '2021-08-12 19:54:31', '2021-08-12 19:54:31', ' mdi-tag-remove', '删除', '0', 'del(\'/system/delete_operator_log\')', '23', '', '');
INSERT INTO `cuinn_menu` VALUES ('25', '2021-08-12 19:56:44', '2021-08-12 20:32:45', ' mdi-delete-circle', '清空日志', '0', 'delAll(\'/system/delete_all_operator_log\')', '23', '', '');
INSERT INTO `cuinn_menu` VALUES ('26', '2021-08-12 21:10:59', '2021-08-12 21:10:59', ' mdi-database', '数据备份', '0', '/database_bak/list', '10', '\0', '');
INSERT INTO `cuinn_menu` VALUES ('27', '2021-08-12 21:12:53', '2021-08-13 21:27:30', ' mdi-database-plus', '备份', '0', 'add(\'/database_bak/add\')', '26', '', '');
INSERT INTO `cuinn_menu` VALUES ('28', '2021-08-12 21:14:21', '2021-08-12 21:14:21', ' mdi-database-minus', '删除', '0', 'del(\'/database_bak/delete\')', '26', '', '');
INSERT INTO `cuinn_menu` VALUES ('29', '2021-08-14 10:56:57', '2021-08-14 10:56:57', ' mdi-database-minus', '还原', '0', 'restore(\'/database_bak/restore\')', '26', '', '');

因此我在执行这些记录之前需要将他们排序，让父菜单记录先执行，也就是parent_id为null的记录先执行，那么就没问题了，这个问题就得到了解决。