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

Question

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?

mysqli is not a database but just a driver. it's irrelevant to whatever table structure. mysql-i, note the "i" at the end. mysqli is not mysql — Your Common Sense, Jan 19 '13 at 21:14
@YourCommonSense Ah! You're referring to the tag on the question, my mistake! — Eugene S, Jan 19 '13 at 21:29

inhan · Accepted Answer · 2013-01-20T03:19:48.690

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

This looks pretty good, but I don't see the purpose of the extra auto-incrementing `id` field in the `instructions` table. The combination of the `recipeId` and `stepNumber` is already unique. — Eugene S, Jan 20 '13 at 02:50
@unluddite I actually agree with you, but I remember sometime ago I read somewhere that the lack of a primary key has some oddities in certain circumstances. I'm not exactly sure why, performance, perhaps. Anyway that's why I added it, otherwise it has no affect on the data itself. — inhan, Jan 20 '13 at 02:54
That may be true, but what I meant was the the PK key is `(recipeId, stepNumber)`. Thanks for your answer and the example of using `MAX`. — Eugene S, Jan 20 '13 at 02:55
Oh okay. No problem. Btw the multi-column primary key appears to be named as "composite primary key". I came across a couple of examples [here](http://stackoverflow.com/questions/1460465/composite-primary-key-performance-drawback-in-mysql) and [here](http://stackoverflow.com/questions/2642778/multi-column-primary-key-in-mysql-5). — inhan, Jan 20 '13 at 02:57
@unluddite It seems multiple columns can be set to primary key. You don't have to worry about the `id` anymore. — inhan, Jan 20 '13 at 03:06
Without a primary key referencing becomes a nightmare. Using this method you will still need to look at things like displaying the step number from 1 in your app as well as the problem of re-ordering (if the steps should change). Perhaps adding a sort order field may be a way of tracing this. — Paul, Jan 20 '13 at 10:05

Paul · Answer 2 · 2013-01-20T01:06:37.413

2

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.

edited Jan 20 '13 at 01:06

answered Jan 19 '13 at 23:48

Paul

4,160
3
30
56

The idea is that a recipe has multiple steps (instructions) so the recipe id + the step number produce a unique record. – Eugene S Jan 20 '13 at 00:57
I'm pretty new to this so I'm open to better solutions. I was trying to avoid explicitly setting the `stepNumber` and just let the auto-increment to take care of that; also adding a new step to a recipe won't involve having to get the current highest step number. Is `max` a built-in MySql function that can achieve this? – Eugene S Jan 20 '13 at 01:10
Yes - you can find it [here](http://www.tizag.com/mysqlTutorial/mysqlmax.php). I suppose my post is more about the context of what you are doing than the easiest solution. – Paul Jan 20 '13 at 01:19
@Westie: You have a misconception. You confuse `AUTO_INCREMENT` with `UNIQUE`. I agree that in most cases an auto_increment column is also the primary key (so unique). But you can actually have auto_increment columns that are not defined as unique. What you think is impossible, is very much what happens in MyISAM engine if you have a Primary Key of `(a,b)` and you set column `b` as auto_increment. See my answer here: [creating primary key based on date](http://stackoverflow.com/questions/5455436/creating-primary-key-based-on-date/5455513#5455513) – ypercubeᵀᴹ Jan 20 '13 at 18:37
In InnoDB, you can also have a auto_increment column that is not unique but it doesn't work as nicely as in MyISAM. One has to set the values manually (if left automatically, they still get unique values). So, an `(a,b)` primary key with `b` auto_increment is possible to be set up in InnoDB but it doesn't work as expected. – ypercubeᵀᴹ Jan 20 '13 at 18:40
@ypercube: No - my thoughts are clear on this. `AUTO_INCREMENT` technically is unique in itself. My point is that, when you come to code your app, you're going to need to look at the value of the steps involved. I.e. when you display your recipe on the screen you don't want the steps for recipe id 30 displaying 451, 452, 453 - you're going to want them to start (visually for the user) from 1. – Paul Jan 20 '13 at 19:36
It seems that a comment I posted on one of the other posts has been removed - this also discussed, what I suppose is, the ultimate end of why the steps are there. When you follow a recipe in a book the steps in the recipe always start at 1. My thoughts are that, one way pr another, the steps will have to start at 1 at some point. – Paul Jan 20 '13 at 20:17

score 1 · Answer 3 · answered Jan 19 '13 at 21:24

1

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

answered Jan 19 '13 at 21:24

He has FKs, so he is obviously using InnoDB. But you are correct, this can be done in MyISAM only. – ypercubeᵀᴹ Jan 19 '13 at 21:30
@ypercube Serious question: Are you guys sure? I don't have any experience on setting multiple columns together as a primary key in a table but I tried and it worked (on a InnoDB table) without any error..? – inhan Jan 20 '13 at 03:09
1

@inhan: You can set a compound key in InnoDB, sure. Noone said otherwise. What you can't do is to set a compound key having the second column (like the `stepNumber` here) as auto-increment and then expect that column to increase per the first column (`recipeId` here). – ypercubeᵀᴹ Jan 20 '13 at 10:20
@ypercube Thanks for the explanation. For some reason I totally forgot about the auto incrementing phase. – inhan Jan 20 '13 at 14:56

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

3 Answers3

Linked