MySQL - 无法添加外键

Question

我有一个 MySQL InnoDB 表格，如下所示：

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` enum('MANUAL','FACEBOOK') NOT NULL DEFAULT 'MANUAL',
  `role` enum('COOK','HOST','ALL') NOT NULL DEFAULT 'ALL',
  `about_me` varchar(1000) DEFAULT NULL,
  `food_preferences` varchar(1000) DEFAULT NULL,
  `cooking_experience` varchar(1000) DEFAULT NULL,
  `with_friend` bit(1) DEFAULT b'0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

接下来我尝试添加一个表格并插入了这样一条语句（创建表格时没有添加外键，因为有问题）：

CREATE TABLE `messages` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `from` bigint(20) NOT NULL,
  `to` bigint(20) NOT NULL,
  `content` varchar(10000) NOT NULL,
  `timestamp_sent` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `timestamp_read` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

创建好数据表后，我需要最终添加外键到 'from' 和 'to' 字段，引用 'users'.'id' 字段：

ALTER TABLE `messages` 
  ADD CONSTRAINT `messages_users_fk`
  FOREIGN KEY (`from` , `to` )
  REFERENCES `users` (`id` , `id` )
  ON DELETE SET NULL
  ON UPDATE CASCADE
, ADD INDEX `messages_users_fk_idx` (`from` ASC, `to` ASC)

我收到的错误是：

错误：1822错误：无法添加外键约束。在引用表“users”中缺少约束“messages_users_fk”的索引。

但是“users”表在“id”上有一个主要索引...

还尝试了一个更小的步骤，只为“from”字段添加外键：

ALTER TABLE `messages` 
  ADD CONSTRAINT `messages_users_fk`
  FOREIGN KEY (`from` )
  REFERENCES `users` (`id` )
  ON DELETE SET NULL
  ON UPDATE CASCADE
, ADD INDEX `messages_users_fk_idx` (`from` ASC) ;

错误略有不同：

错误1825：在“消息”表上添加外键约束时失败。FOREIGN KEY约束“cook4food / messages_users_fk”中的选项不正确。

字段类型与其他StackOverflow线程中问题的原因相同（bigint（20）NOT NULL）。我的表没有分区（MySQL手册将其作为InnoDB中具有外键约束的限制）。“messages”表当前不存储任何行，因此存储的数据无法以任何方式成为问题。我被卡住了，请帮忙。

- tomek.g1989

---

很高兴看到实际的CREATE TABLE语句，可以与它们一起玩耍 :) - Álvaro González

Answer 1

如果您拥有适当的权限，您可以发出此查询：

SHOW ENGINE innodb STATUS

...其中将告诉您（以及其他一些信息）错误的确切细节：

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130311 13:30:06 Error in foreign key constraint of table test/#sql-71c_6:

  FOREIGN KEY (`from` , `to` )
  REFERENCES `users` (`id` , `id` )
  ON DELETE SET NULL
  ON UPDATE CASCADE
, ADD INDEX `messages_users_fk_idx` (`from` ASC, `to` ASC):
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.

在父记录被删除时，您无法使子记录为空，因为这些列是NOT NULL：

  `from` bigint(20) NOT NULL,
  `to` bigint(20) NOT NULL,

编辑： 此外，我看不出单个复合键的目的。我认为您需要两个单独的键。

- Álvaro González

---

StackOverflow社群一如既往地快速和有益。现在我可以看到我的错误了，谢谢！ - tomek.g1989

有什么想法，如果SHOW ENGINE innodb STATUS没有显示最新的FOREIGN KEY ERROR？ - antonagestam

谢谢你！+1 Alavaro。你让我免于度过第二个夜晚！再次感谢。 - Salathiel Genèse

谢谢，@Álvaro González。我很快就找到了我的错误。 - Green

Answer 2

REFERENCESusers(id,id)

您正在尝试创建一个无效的外键（id，id）。涉及到复合索引（涉及多个列的索引）的列不能是相同的重复列。至少我从未见过这种情况。

请创建两个单独的外键，一个用于FROM，另一个用于TO，每个外键都指向用户。

- Tim

---

是的，我在思考这两种方法之间的区别。分离的外键是我真正需要的。谢谢。 - tomek.g1989

Answer 3

正如我们所看到的，

当子列from和to不为null时，您无法在子记录中定义SET NULL。

因此，如果您尝试使用以下2个语句创建外键，则应该在messages表中拥有外键。

1>

ALTER TABLE `messages`  ADD CONSTRAINT `messages_users_from_fk`   FOREIGN KEY (`from` )   REFERENCES `users` (`id` )  ON DELETE CASCADE ON UPDATE CASCADE, ADD INDEX `messages_users_fk_idx` (`from` ASC) ;

和

2>

ALTER TABLE `messages`  ADD CONSTRAINT `messages_users_to_fk`   FOREIGN KEY (`to` )   REFERENCES `users` (`id` )  ON DELETE CASCADE ON UPDATE CASCADE, ADD INDEX `messages_users_fk_to_idx` (`to` ASC) ;

希望这些语句能对您有所帮助。

- Piyas De