CODEWITHSUNDEEP
mysqlsql
Eugene S
Eugene S

Reputation: 3122

Table can't have 2-field primary key with auto_increment

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?

Upvotes: 1

Views: 2084

Answers (3)

inhan
inhan

Reputation: 7470

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`)

Upvotes: 2

user1945782
user1945782

Reputation:

I do have to ask - why would you want to? If you think about it, your recipe Id (FK) is going to be unique, then your step number is always going to start from 1 (or zero if you're zero based).

-- edit --

steps table:
recipe_id step_id step_detail
--------- ------- ---------------------------
        1       1 blah
        1       2 blah
        1       3 blah
        2       1 blah
        2       2 blah
        2       3 blah
        2       4 blah
        2       5 blah
--------- ------- ---------------------------

If you included an auto-increment here then the step numbers would just keep on going up instead of resetting to 1 for the next recipe.

-- end edit --

Kind regards, Westie.

Upvotes: 2

user1914530
user1914530

Reputation:

I don't think you can do this with InnoDB if thats what you are using. Apparently you can with MyISAM.

http://dev.mysql.com/doc/refman/5.6/en/example-auto-increment.html

Upvotes: 1

Related Questions