外键约束失败 - 可能误解了我的关系

Question

我在处理一些 MySQL 关系时遇到了一些问题。我认为我的结构中遗漏了一些明显的东西。这是我的 SQL：

DROP TABLE IF EXISTS `parentlist_comments`;
CREATE TABLE `parentlist_comments` (
  `id` char(36) NOT NULL,
  `parentlist_id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `comment` char(50) NOT NULL,
  `accepted` tinyint(1) NOT NULL DEFAULT '0',
  `submitted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_parentlist_comments_parentlist` (`parentlist_id`),
  KEY `fk_parentlist_comment_user` (`user_id`),
  CONSTRAINT `fk_parentlist_comments_parentlist` FOREIGN KEY (`parentlist_id`) REFERENCES `parentlists` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_parentlist_comment_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `parentlist_submissions`;
CREATE TABLE `parentlist_submissions` (
  `id` char(36) NOT NULL,
  `parentlist_id` char(36) NOT NULL,
  `type_id` char(36) NOT NULL,
  `name` char(25) NOT NULL,
  `user_id` char(36) NOT NULL,
  `accepted` tinyint(1) NOT NULL DEFAULT '0',
  `submitted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `votes` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_parentlist_submissions_user` (`user_id`),
  KEY `fk_parentlist_submissions_list` (`parentlist_id`),
  KEY `fk_parentlist_submissions_type` (`type_id`),
  CONSTRAINT `fk_parentlist_submissions_list` FOREIGN KEY (`parentlist_id`) REFERENCES `parentlists` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_parentlist_submissions_type` FOREIGN KEY (`type_id`) REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_parentlist_submissions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `parentlists`;
CREATE TABLE `parentlists` (
  `id` char(36) NOT NULL,
  `name` char(20) NOT NULL,
  `description` char(50) NOT NULL,
  `user_id` char(36) NOT NULL,
  `max_comments` int(3) NOT NULL DEFAULT '0',
  `max_submissions` int(3) NOT NULL DEFAULT '10',
  `max_votes` int(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_list_user` (`user_id`),
  CONSTRAINT `fk_list_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

DROP TABLE IF EXISTS `submissions`;
CREATE TABLE `submissions` (
  `id` char(36) NOT NULL,
  `type_id` char(36) NOT NULL,
  `name` char(30) NOT NULL,
  `description` char(50) NOT NULL,
  `embed` char(200) DEFAULT NULL,
  `user_id` char(36) NOT NULL,
  `accepted` tinyint(1) NOT NULL DEFAULT '0',
  `submitted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `votes` int(5) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_submission_user` (`user_id`),
  KEY `fk_submission_type` (`type_id`),
  CONSTRAINT `fk_submission_type` FOREIGN KEY (`type_id`) REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_submission_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `types`;
CREATE TABLE `types` (
  `id` char(36) NOT NULL,
  `name` char(20) NOT NULL,
  `description` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` char(36) NOT NULL,
  `name` char(20) NOT NULL,
  `password` char(20) NOT NULL,
  `email` char(50) NOT NULL,
  PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我在parentlist_submissions 中创建了一个名为submission_id 的列。我试图在parentlist_submissions.submission_id 和submissions.id 之间创建外键关系，当我尝试执行此操作时，出现错误：外键约束失败。无论出于何种原因，我的查询浏览器都不会让我复制它。

非常感谢这里的任何帮助！

Answer 1

该错误通常是由于表中已填充了违反约束的数据而引起的。 （请注意，如果您刚刚添加了列，则空值可能是一个问题。）

我猜测，因为我没有看到您在创建submission_index列或创建外键约束的位置发布了语句。

Answer 2

您似乎缺少“parentlist_submissions.submission_id”列。