mysql - 表不能有带 auto_increment 的 2 字段主键

Question

我正在尝试创建一个表来表示食谱中的说明:

+---------------------+
| recipeId   (PK, FK) |
| stepNumber (PK)     |
|---------------------|
| instruction         |
+---------------------+

想法是有一个主键 (recipeId, stepNumber) 其中 recipeId 来自 recipe 表和 stepNumber 自动递增。

当我尝试创建这个表时，出现了以下错误:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key 

我正在尝试做的事情是否正确/可能？

Answer 1

我的建议是，创建通用的 id首先是 auto_increment 的列，以便在表中具有主键。然后为 recipeId 创建一个唯一的键和 stepNumber放在一起，这样您就不会有这两个字段的任何重复组合。

为了能够为单个食谱添加多个步骤，您需要确保没有 recipeId , stepNumber或 instruction设置为自动递增。唯一设置为 auto_increment 的列仍然是 id .

所以这 2 个表的表架构看起来像(忽略 category 列)

CREATE TABLE `recipies` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `category` enum('Salad','Dessert','Meat','Pastry') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `instructions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `recipeId` int(11) unsigned NOT NULL,
  `stepNumber` int(11) NOT NULL DEFAULT '1',
  `instruction` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `recipeId` (`recipeId`,`stepNumber`),
  CONSTRAINT `instructions_ibfk_1` FOREIGN KEY (`recipeId`) REFERENCES `recipies` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

让我们在 recipies 中添加一条记录表格优先

INSERT INTO `recipies` (`name`,`category`)
VALUES ('Pumpkin Pie','Pastry');

然后让我们添加一行

INSERT INTO `instructions` (`recipeId`,`instruction`,`stepNumber`)
SELECT
    1,
    'You will need plenty of pumpkins!',
    IFNULL(MAX(`stepNumber`),0)+1
FROM `instructions`
WHERE `recipeId`=1

• 1 在 SELECT 之后和 1 在 WHERE条件均指代 id=1 的行在recipies表
• IFNULL(MAX(stepNumber),0)+1将为该食谱选择最高的步骤编号(如果不存在，它将选择“0”)+1

这是一个 SQL fiddle如果你想看到它的工作。

[编辑]
我从来不需要对主键使用组合，但如果表中还没有主键，显然可以在 InnoDB 上进行后续工作。

ALTER TABLE `instructions`
ADD PRIMARY KEY(`recipeId`,`stepNumber`)