mysql - 表不能包含具有auto_increment的2字段主键

我正在尝试创建一个表，该表表示配方中的指令：

+---------------------+
| 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

我要做的是正确的/可能的吗？

最佳答案

我的建议是，首先用auto_increment创建genericid列，以便在表中有一个主键。然后为recipeId和stepNumber一起创建一个唯一的键，这样就不会有这两个字段的任何重复组合。
要为一个配方添加多个步骤，您需要确保没有将recipeId、stepNumber或instruction设置为自动递增。唯一设置为“自动递增”的列将保持id。
因此，这两个表的表模式如下所示（忽略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 afterSELECT和1 inWHERE条件均指id=1表中recipies的行
IFNULL(MAX(stepNumber),0)+1将选择该配方的最高步数（如果它不存在，它将选择“0”）+ 1
如果你想看到它工作的话，这里有一个SQL fiddle。
[编辑]
我不需要对主键使用组合键，但显然，如果表中还没有主键，那么InnoDB上的后续工作就可以完成了。

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