mysql - ERROR 1452：无法添加或更新子行：DELETE 操作上的外键约束失败？

Question

我正在尝试DELETE从 db (mySQL Workbench) 中记录并收到约束错误。

Executing:
DELETE FROM `users` WHERE (`ID` = '27');

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1452: 1452: Cannot add or update a child row: a foreign key constraint fails (`people`, CONSTRAINT `people_ibfk_36` FOREIGN KEY (`creatorID`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE)
SQL Statement:
DELETE FROM `users` WHERE (`ID` = '27')

错误的原因可能是什么？数据库不应该只是级联删除被users删除记录引用的任何内容（在本例中为表中的条目people）？这可能是由一些进一步的引用引起的（例如，将在删除时设置 null 并且不允许 null ），但错误指向引用链中最近的表？

编辑：表定义

Users桌子

CREATE TABLE `users` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `firstLogin` tinyint(1) NOT NULL DEFAULT 1,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `surname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `accountTypeID` int(11) DEFAULT NULL
  PRIMARY KEY (`ID`),
  KEY `accountTypeID` (`accountTypeID`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`accountTypeID`) REFERENCES `account_types` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

People桌子

CREATE TABLE `people` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `appOwner` tinyint(1) DEFAULT 0,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `surname` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `favStatusID` int(11) DEFAULT NULL,
  `userID` int(11) DEFAULT NULL,
  `creatorID` int(11) DEFAULT NULL  
  PRIMARY KEY (`ID`),
  UNIQUE KEY `people_creator_i_d_user_i_d` (`creatorID`,`userID`),
  KEY `favStatusID` (`favStatusID`),
  KEY `userID` (`userID`),
  CONSTRAINT `people_ibfk_34` FOREIGN KEY (`favStatusID`) REFERENCES `fav_statuses` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `people_ibfk_35` FOREIGN KEY (`userID`) REFERENCES `users` (`ID`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `people_ibfk_36` FOREIGN KEY (`creatorID`) REFERENCES `users` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=200 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

标签： mysqlmysql-workbench