即使两个父行都存在，也无法插入到连接表中，外键约束仍然失败

Question

我正在尝试在连接表中插入一行。连接表的设置如下所示：

CREATE TABLE `attachments_candidates` (
  `candidate_id` int(11) NOT NULL,
  `attachment_id` int(11) NOT NULL,
  PRIMARY KEY (`candidate_id`,`attachment_id`),
  UNIQUE KEY `UNIQ_2FCBAF6C464E68B` (`attachment_id`),
  KEY `IDX_2FCBAF6C91BD8781` (`candidate_id`),
  CONSTRAINT `FK_2FCBAF6C464E68B` FOREIGN KEY (`attachment_id`) REFERENCES `attachment` (`id`),
  CONSTRAINT `FK_2FCBAF6C91BD8781` FOREIGN KEY (`candidate_id`) REFERENCES `candidate` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

失败的插入代码如下所示：

INSERT INTO attachments_candidates (candidate_id, attachment_id) VALUES (70300, 10012);

显示的错误是：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db-7`.`attachments_candidates`, CONSTRAINT `FK_2FCBAF6C91BD8781` FOREIGN KEY (`candidate_id`) REFERENCES `candidate` (`id`) ON DELETE CASCADE)

为了确保父行存在(并且attachment_id是唯一的)，我使用以下命令进行了检查：

MariaDB [db-7]> select count(*) from candidate where id = 70300; select count(*) from attachment where id = 10012; select count(*) from attachments_candidates where attachment_id = 10012;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

感谢您的帮助。

编辑:以下是两个父表的表定义(省略了一些字段)：

| candidate | CREATE TABLE `candidate` (
  `id` int(11) NOT NULL,
  `resume_id` int(11) DEFAULT NULL,
  `assigned_to_id` int(11) DEFAULT NULL,
  `address_id` int(11) DEFAULT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_C8B28E44D262AF09` (`resume_id`),
  UNIQUE KEY `UNIQ_C8B28E44F5B7AF75` (`address_id`),
  KEY `IDX_C8B28E44F4BD7827` (`assigned_to_id`),
  CONSTRAINT `FK_C8B28E44BF396750` FOREIGN KEY (`id`) REFERENCES `module_entity` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_C8B28E44D262AF09` FOREIGN KEY (`resume_id`) REFERENCES `attachment` (`id`) ON DELETE CASCADE,
  CONSTRAINT `FK_C8B28E44F4BD7827` FOREIGN KEY (`assigned_to_id`) REFERENCES `users` (`id`),
  CONSTRAINT `FK_C8B28E44F5B7AF75` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

| attachment | CREATE TABLE `attachment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_by_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `mime_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `size` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `extension` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_795FD9BBB03A8386` (`created_by_id`),
  CONSTRAINT `FK_795FD9BBB03A8386` FOREIGN KEY (`created_by_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10017 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

MariaDB [db-7]> show triggers;
Empty set (0.05 sec)

Answer 1

你向我们展示的东西绝对没有错；

drop table if exists candidate,attachment,attachments_candidates;

create table candidate(id int primary key);
create table attachment(id int primary key);


CREATE TABLE `attachments_candidates` (
  `candidate_id` int(11) NOT NULL,
  `attachment_id` int(11) NOT NULL,
  PRIMARY KEY (`candidate_id`,`attachment_id`),
  UNIQUE KEY `UNIQ_2FCBAF6C464E68B` (`attachment_id`),
  KEY `IDX_2FCBAF6C91BD8781` (`candidate_id`),
  CONSTRAINT `FK_2FCBAF6C464E68B` FOREIGN KEY (`attachment_id`) REFERENCES `attachment` (`id`),
  CONSTRAINT `FK_2FCBAF6C91BD8781` FOREIGN KEY (`candidate_id`) REFERENCES `candidate` (`id`) ON DELETE CASCADE
) ;

insert into candidate values(70300);
insert into attachment values(10012);

INSERT INTO attachments_candidates (candidate_id, attachment_id) VALUES (70300, 10012);


select * from attachmentS_candidates;


+--------------+---------------+
| candidate_id | attachment_id |
+--------------+---------------+
|        70300 |         10012 |
+--------------+---------------+
1 row in set (0.00 sec)

请添加所有表定义并检查触发器。

Answer 2

PRIMARY KEY (`candidate_id`,`attachment_id`),
UNIQUE KEY `UNIQ_2FCBAF6C464E68B` (`attachment_id`),

听起来是“错的”。attachments_candidates看起来像一个多对多的映射表，但它只是多对多映射表，但这可以用一种更简单的方式来处理--只需在candidate表中包含attachment_id。(并对其进行索引。)

一旦你弄清楚了这个问题，你的问题可能就会消失。