MySQL(Percona)错误1452:无法无故添加或更新子行

Question

我有两个数据库表"用户"；以及"；任务"；。任务表包含createdBy_user_id和updatedBy_user_id列的两个外键，这两个列都引用users.id。如果我尝试在tasks中插入一个条目(在确保外键引用的用户存在之后(，如下所示：

INSERT INTO tasks (createdBy_user_id,updatedBy_user_id,noOfYear,createdAt,updatedAt,status,customer_id)
VALUES (1,1,1,NOW(),NOW(),"open",1)

查询失败，出现错误1452:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`tasks`, CONSTRAINT `user_id_fk_constr` FOREIGN KEY (`createdBy_user_id`) REFERENCES `users` (`id`))

我不知道为什么会发生这种情况，因为如果我删除约束，一切都会很好。相同的错误不会发生在"；updatedBy_ user_id"；专栏，让这件事如此令人困惑。

表格具有以下DDL：

用户表：

CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`active` tinyint(1) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`email_confirmed_at` datetime DEFAULT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(255) NOT NULL,
`first_name` varchar(50) DEFAULT NULL,
`last_name` varchar(50) DEFAULT NULL,
`job` varchar(64) DEFAULT NULL,
`position` varchar(64) DEFAULT NULL,
`specialKnowledge` text,
`tasks` text,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

任务表：

CREATE TABLE `tasks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`noOfYear` int(11) NOT NULL,
`year` int(11) GENERATED ALWAYS AS (right(year(`createdAt`),2)) VIRTUAL NOT NULL,
`createdBy_user_id` int(11) NOT NULL,
`updatedBy_user_id` int(11) NOT NULL,
`status` enum('open','closed') NOT NULL,
`customer_id` int(11) NOT NULL,
`projectDescription` text,
PRIMARY KEY (`id`),
UNIQUE KEY `tasks_year_unique_constr` (`year`,`noOfYear`),
KEY `user_id_fk_constr` (`createdBy_user_id`),
KEY `customer_id_fk_constr` (`customer_id`),
KEY `user_up_id_fk_constr` (`updatedBy_user_id`),
CONSTRAINT `customer_id_fk_constr` FOREIGN KEY (`customer_id`) REFERENCES `Customer` (`id`),
CONSTRAINT `user_id_fk_constr` FOREIGN KEY (`createdBy_user_id`) REFERENCES `users` (`id`),
CONSTRAINT `user_up_id_fk_constr` FOREIGN KEY (`updatedBy_user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 

正如您所看到的，数据类型匹配，并且两个表都使用InnoDB Engine。用户表包含一个条目：

select id,username from users;

+----+----------+
| id | username |
+----+----------+
|  1 | admin    |
+----+----------+
1 row in set (0.00 sec)

因此，插入失败的原因并不明显。你知道我的桌子怎么了吗？看起来像是软件错误。

Answer 1

这是一个错误。MySQL 5.7在生成列和外键方面遇到了一些问题，我认为这是Bug#79772的未修复变体，当存在虚拟索引时，不允许使用外键。

在您的特定情况下，可能取决于您的确切版本，以下任何修改似乎都可以防止该错误的发生：

• 不使用虚拟列，而是将其设为stored
• 不要为虚拟列后面的列创建外键，例如将列顺序更改为year, status, createdBy_user_id, updatedBy_user_id
• 不要在虚拟列上使用unique索引，正常索引应该可以(至少在修复了链接错误的版本中(。你想要一个唯一的约束，所以这不是一个选项，但事实上，这解决了你的问题，强调了"；bug"；问题的性质

第二个要点似乎是潜在的错误：我假设某个迭代器没有正确计算虚拟列，所以应该检查createdBy_user_id的外键似乎混淆了列，并实际根据users表检查year的值(在本例中为"20"(。因此，如果您有一个id为"的用户；20〃；在您的users表中，外键实际上会接受这一点，无论您试图插入createdBy_user_id的什么值，请参阅MySQL 5.7.29 fiddle。

除非您有特定的理由使用虚拟列，否则使用stored可能是明智的做法