MySQL Bugs: #60104: Compound Primary Key param with Auto

Bug #60104	Compound Primary Key param with Auto_INCREMENT raises ERROR: 1075
Submitted:	10 Feb 2011 21:19	Modified:	17 Feb 2011 12:24
Reporter:	Ralph Bernardes	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.5, 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	compound, position, primary key

Description:
I tried to create a simple table like the following one:

CREATE TABLE Product(
    Id INT AUTO_INCREMENT NOT NULL,
    Number VARCHAR(10) NOT NULL,
    Description VARCHAR(100),
    PRIMARY KEY(Id, Number)
);

Ok, this one above works fine! But, this does not:

CREATE TABLE Product(
    Id INT AUTO_INCREMENT NOT NULL,
    Number VARCHAR(10) NOT NULL,
    Description VARCHAR(100),
    PRIMARY KEY(Number, Id) ----> the only thing different is the position here
)

The only differece as said above is the position of the parameters in the PRIMARY KEY clause. If we have a column configured to be AUTO_INCREMENT, and put this column as part of the key but after any column that is not AUTO_INCREMENT, you get the error.

When I try to run this SQL above, I got:

Error Code: 1075
Incorrect table definition; there can be only one auto column and it must be defined as a key.

The problem here is a simple solution, as long as you are not dealing with any SQL generator, like Workbench, EclipseLink...whatever. Or this generator get the positioning of the items in the PRIMARY KEY CLAUSE right, then we're good to go. 

How to repeat:
Execute this SQL:

CREATE TABLE Product(
    Id INT AUTO_INCREMENT NOT NULL,
    Number VARCHAR(10) NOT NULL,
    Description VARCHAR(100),
    PRIMARY KEY(Number, Id) 
);

Suggested fix:
Maybe create the primary key after the auto column. Looks like the auto column is being created first.

Thank you for the bug report. Duplicate of bug: http://bugs.mysql.com/bug.php?id=45987.

C:\DBS>c:\dbs\5.5\bin\mysql -uroot --port=3540 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.10 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 >use d1
Database changed
mysql 5.5 >set storage_engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >CREATE TABLE Product(
    ->     Id INT AUTO_INCREMENT NOT NULL,
    ->     Number VARCHAR(10) NOT NULL,
    ->     Description VARCHAR(100),
    ->     PRIMARY KEY(Number, Id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql 5.5 >
mysql 5.5 >set storage_engine=Innodb;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >drop table Product;
Query OK, 0 rows affected (0.00 sec)

mysql 5.5 >CREATE TABLE Product(
    ->     Id INT AUTO_INCREMENT NOT NULL,
    ->     Number VARCHAR(10) NOT NULL,
    ->     Description VARCHAR(100),
    ->     PRIMARY KEY(Number, Id)
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql 5.5 >
mysql 5.5 >CREATE TABLE Product(
    ->     Id INT AUTO_INCREMENT NOT NULL,
    ->     Number VARCHAR(10) NOT NULL,
    ->     Description VARCHAR(100),
    ->     PRIMARY KEY(Id, Number)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql 5.5 >

What if I need my table to have InnoDB engine?
Will I not lose any feature that InnoDB supplies? Like foreign key constraints?