Missing support for AUTO_INCREMENT columns in multiple-column index for InnoDB tables is a widely known limitation, as is the trigger workaround for when it is needed (see e.g this post). Yesterday, however, I ran into this thread claiming support for this feature was added as early as 5.1. I tried the following statement on my 5.7 install (mysql-community-server-5.7.19-1.el7.x86_64), and it appears to work flawlessly:
CREATE TABLE `tbl1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`data` datetime NOT NULL,
PRIMARY KEY (`id`,`data`)
) ENGINE=InnoDB;
My problem is that, for the life of me, I cannot find a positive statement of support of the feature on MySQL's documentation site – or elsewhere. I am rather wary of deploying in production something unannounced (which might be experimentally present) so I hope somebody can point me to the official status of this.
Edit: It turns out that the manual contains, in parts unrelated, this rather oblique statement:
“…an InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column.”
which suggests, but does not state, support.
Edit 2: Using information from @rolandoMYSQLDBA below, I also located this additional snippet: "An AUTO_INCREMENT column must appear as the first column in an index on an InnoDB table.". I am therefore accepting his answer.
And in case someone is wondering, the multiple column index thing is needed for table partitioning – the customer has auto_increment PK on all tables and they want to partition on a date column.
Best Answer
Under normal circumstances, I would have closed this question as a duplicate. There are already close votes. In this particular instance, since you put a bounty on this question, I'll try to answer it as best as I can.
Look at the first post you mentioned. Note the table definition
The auto_increment column is
sr_no
. This is neither the only column nor the leftmost column in the primary key definition. Now, if the primary key was writtenthen
sr_no
would be leftmost. No error would have occurred with InnoDB.When you ran the command
your auto_increment column
id
was leftmost.You made reference to a second post. Please note that in the answer to that second post, it clearly notes that MyISAM and BDB storage engines support auto_increment ids. When it comes to MyISAM, the auto_increment behavior allows an auto_increment number to be paired with one or more keys in such a way that the number 1 can appear multiple times but be associated uniquely with the columns. I have discussed this a few times over the years:
Apr 21, 2012
: How can you have two auto-incremental columns in one table?Jun 10, 2012
: MySQL get next unique value without auto incrementFeb 26, 2013
: How to use 2 auto increment columns in MySQL phpmyadminAug 28, 2013
: How to go about modeling the main object of relationship?Keep in mind that while InnoDB support for an auto_increment column in a
PRIMARY KEY
exists, it does not have the same auto_increment behavior as MyISAM. MyISAM can bind with other columns to allow a number to exist multiple times, InnoDB does not do that. Once an auto_increment value is used, it is unique for the whole table.The excerpt you found
comes from a page in the MySQL Documentation that has to do with how replication sees and handles auto_increment values. This is a vital issue since the order data are written on a Master may get serialized in a different order on a Slave.
Just recently (back on
Jun 16, 2017
) I answered Does it ever make sense to create an index with additional columns after the primary key?, where I mentioned from the MySQL Documentation that when there is an auto_increment column in a partitioned table, it is mandatory that the column is included in thePRIMARY KEY
along with the columns that define the sharding of the table rows.To conclude, let me just say this: support for auto_increment columns in a
PRIMARY KEY
in InnoDB does not come with any fringe benefits or additional bell-and-whistles like it does with MyISAM. That's why the MySQL Documentation does not have a lot written about it.Support for this was mentioned long ago. If you look in the MySQL 5.1 Documentation on
InnoDB Restrictions
, you see bulletpoint #15There is more extensive stuff in the MySQL 5.1 Documentation AUTO_INCREMENT Handling in InnoDB. It is also available in the MySQL 5.5 Documentation. Unfortunately, these are in the old Documentation format. This is probably why you can't find anything now. Just be assured that the support is there.