+-
Mysql外键有值但数据插入仍然失败

数据表

Department
id name createTime updateTime 1 R & D .. .. 2 Administration .. .. 3 Human Resource .. .. 4 Supply Chain .. .. 5 Purchasing .. ..
CREATE TABLE `department` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `createTime` datetime NOT NULL,
  `updateTime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `department_id_uindex` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT 
CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Staff
id name deptId level createTime updateTime 1 Rod Johnson 3 1 .. .. 2 Chris Beams 1 1 .. .. 3 Mark Twain 2 1 .. .. 4 Alexandre Owen 4 1 .. .. 5 Fiona Chris 5 1 .. ..
CREATE TABLE `staff` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `deptId` int NOT NULL,
  `level` int NOT NULL DEFAULT '1',
  `createTime` datetime NOT NULL,
  `updateTime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `staff_id_uindex` (`id`),
  CONSTRAINT `staff_department_id_fk` FOREIGN KEY (`id`) REFERENCES `department` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT 
CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci`

staff.deptId 作为外键引用 department.id
staff表中插入前 4 条时是没问题的,准备插入第 5 条时,出现外键约束失败的报错:
[23000][1452] Cannot add or update a child row: a foreign key constraint fails (iushu.staff, CONSTRAINT staff_department_id_fk FOREIGN KEY (id) REFERENCES department (id

这到底是哪里出问题了?
虽然现在大多系统都不会在数据库设计中使用外键,但最近在倒腾Mysql时突然发现这问题,想搞明白看看怎么这么神奇

FOREIGN KEY (`id`) REFERENCES `department` (`id`) ON UPDATE CASCADE

这句错了吧,应该是

FOREIGN KEY (`deptId`) REFERENCES `department` (`id`) ON UPDATE CASCADE

补充,一般在正常的开发中,一般不建议在数据库中设定外键

性能问题:插入数据时需要对两张表进行检查 并发问题: 在高并发大流量事务场景,使用外键更容易造成死锁。 扩展性:在水平拆分和分库的情况下,外键是无法生效的