MySQL Bugs: #103939: Can't create table with composite PK if auto

Bug #103939	Can't create table with composite PK if auto_increment column isn't first
Submitted:	8 Jun 2021 8:04	Modified:	10 Jun 2021 15:17
Reporter:	Lukas Eder	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.24, 8.0.25, 5.7.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a table has a composite primary key with one of the key columns being an auto_increment column, that column can't be the second column of the key.

I don't see a good reason for this limitation other than some internals leaking into the DDL, so I'm guessing, this is a bug.

How to repeat:
-- This works
create table t1 (
  a int not null auto_increment,
  b int not null,
  constraint t1_pk primary key (a, b)
);

-- This doesn't work, it fails with
-- SQL Error [1075] [42000]: Incorrect table definition; there can be only one auto column and it must be defined as a key
create table t2 (
  a int not null auto_increment,
  b int not null,
  constraint t2_pk primary key (b, a)
);

-- This works
create table t3 (
  a int not null auto_increment,
  b int not null,
  constraint t3_pk primary key (b, a),
  constraint t3_uk unique (b)
);

-- But the unique key cannot be dropped either:
alter table t3 drop constraint t2_uk;

Suggested fix:
I'm not sure where the bug or inconsistency is. Either, this limitation should also apply to t1, or (better), this limitation should be lifted and t2 should be an acceptable table definition, or, at least, the error message should be more clear why t1 is ok and t2 is not.

Hello Lukas Eder,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
Hello!

This is expected and documented limitation of auto-increment columns for InnoDB tables.
Our manual says at https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html:

---

15.6.1.6 AUTO_INCREMENT Handling in InnoDB

InnoDB provides a configurable locking mechanism that can significantly improve scalability and performance of SQL statements that add rows to tables with AUTO_INCREMENT columns. To use the AUTO_INCREMENT mechanism with an InnoDB table, an AUTO_INCREMENT column must be defined as part of an index such that it is possible to perform the equivalent of an indexed SELECT MAX(ai_col) lookup on the table to obtain the maximum column value. Typically, this is achieved by making the AUTO_INCREMENT column the first column of some table index. 

---

I agree that error message could be improved though.

Thanks for the explanation. Would it perhaps make sense to add a synthetic index on the auto_increment column if it's not the first, as a workaround? I find the limitation rather arbitrary. If sequences were supported and AUTO_INCREMENT were backed by a sequence (with its own autonomous transaction), then this limitation wouldn't be there, I think?