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?

Answer: 
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](http://sqlfiddle.com/#!2/7e8ef/1) 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`)

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