+-
数据表
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
补充,一般在正常的开发中,一般不建议在数据库中设定外键
性能问题:插入数据时需要对两张表进行检查 并发问题: 在高并发大流量事务场景,使用外键更容易造成死锁。 扩展性:在水平拆分和分库的情况下,外键是无法生效的