php - 在我的表中创建 message_id 时出现 MySQL 错误

Question

我有一个表消息。我尝试使用 message_id 外键创建表 contests 。

CREATE TABLE IF NOT EXISTS `message` (
  `chat_id` bigint COMMENT 'Unique chat identifier',
  `id` bigint UNSIGNED COMMENT 'Unique message identifier',

  ....

  PRIMARY KEY (`chat_id`, `id`),

  ...

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

还有我的 table 比赛

CREATE TABLE IF NOT EXISTS `contests` (
  `id` INT(11) AUTO_INCREMENT COMMENT 'ID записи',
  ...

  `message_id` bigint UNSIGNED COMMENT 'ID сообщения с кнопкой',
  `chat_id` bigint COMMENT 'Так надо',

  ...

  PRIMARY KEY (`id`),

  FOREIGN KEY (`message_id`) REFERENCES `message` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`chat_id`) REFERENCES `message` (`chat_id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

Error: Create table 'admin_bot/contests' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.

Answer 1

您需要使用表的主键在表的外键中引用它。您的 SQL 需要如下所示:

CREATE TABLE IF NOT EXISTS `contests` (
  `id` INT(11) AUTO_INCREMENT COMMENT 'ID записи',
  ...

  `message_id` bigint UNSIGNED COMMENT 'ID сообщения с кнопкой',
  `chat_id` bigint COMMENT 'Так надо',

  ...

  PRIMARY KEY (`id`),

   FOREIGN KEY (`chat_id`, `message_id`) REFERENCES `message` (`chat_id`, `id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;