MySQL Forums
Forum List  »  InnoDB

MySQL and AUTO_INCREMENT columns in multiple-column index for InnoDB tables: support status?
Posted by: Alessandro Forghieri
Date: August 04, 2017 12:30AM

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 [1]). Yesterday, however, I ran into this thread[2] 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.

the manual contains, in parts unrelated, this rather oblique statement [3]:

“…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.

I am rather wary of deploying in production something unannounced (which might be only experimentally present) so I hope somebody can point me to the official status of this.

[1]: https://stackoverflow.com/questions/18120088/defining-composite-key-with-auto-increment-in-mysql
[2]: https://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with-auto-increment
[3]: https://dev.mysql.com/doc/refman/5.7/en/replication-features-auto-increment.html

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL and AUTO_INCREMENT columns in multiple-column index for InnoDB tables: support status?
52
August 04, 2017 12:30AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.