表不能包含具有auto_increment的2字段主键 [英] Table can't have 2-field primary key with auto_increment

问题描述

我正在尝试创建一个代表配方中的指令的表:

+---------------------+
| 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创建通用的id列，以在表中具有主键.然后为recipeId和stepNumber一起创建唯一键，这样您就不会在这2个字段中有任何重复的组合.

要为单个配方添加多个步骤，您需要确保将recipeId，stepNumber或instruction中的任何一个都不设置为自动递增.设置为auto_increment的唯一列仍为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

SELECT之后的
• 1和WHERE条件中的1都引用recipies表中具有id=1的行
• IFNULL(MAX(stepNumber),0)+1将为该配方选择最高的步骤号(如果不存在，则将选择"0")+1

如果您希望看到它有用，请使用 SQL提琴.

我从不需要为主键使用组合键，但是显然只要您在表中没有主键，就可以在InnoDB上进行后续工作.

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

I'm trying to create a table that represents an instruction in a recipe:

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

The idea is to have a primary key of (recipeId, stepNumber) where the recipeId comes from the recipe table and the stepNumber auto-increments.

When I tried to create this table, I got the following error:

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

Is what I'm trying to do correct/possible?

解决方案

My suggestion is, create the generic id column with auto_increment first, to have a primary key in the table. Then create a unique key for both recipeId and stepNumber together so you won't have any duplicate combination of these 2 fields.

To be able to add multiple steps for a single recipe you will need to make sure none of recipeId, stepNumber or instruction is set to auto-increment. The only column set to auto_increment remains id.

So the table schema for these 2 tables would look like (ignore the category column)

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;

Let's add a record in the recipies table first

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

Then let's add a row

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 after SELECT and 1 in the WHERE condition both refer to the row with id=1 in the recipies table
• IFNULL(MAX(stepNumber),0)+1 will select the highest step number for that recipe (if it doesn't exist it will select "0") +1

Here's a SQL fiddle if you want to see it working.

[EDIT]
I have never needed using a combo for the primary key but apparently following works on InnoDB provided you don't already have a primary key in the table.

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

这篇关于表不能包含具有auto_increment的2字段主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！