MySQL: two-columns Primary Key with auto-increment

MySQL Master-Master Replication setup in 5 easy steps

Everyone working with MySQL (and DBMS in general) knows about the AUTO_INCREMENT  attribute that can be used on a column - often the primary key - to generate a unique identity for new rows. To make a quick example, let's take the most classic example from the MySQL online manual:

which returns:

That said... What happens if our primary key consists of two columns and we set the auto-increment feature on one of them?


Will it reset the auto-increment counter when the other column's value changes?


Or will it generate an unique ID for each row?



The answer is: it will depend on which table engine you're using and how you create the index in the CREATE  statement. The official MySQL online manual says that:

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT  column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix . This is useful when you want to put data into ordered groups.

Meaning that, in order to get the first result, you need to CREATE  your table putting the grp  column before the id  column when you declare the  PRIMARY KEY , just like the following:

Needless to say, if you do the opposite you'll get the latter result instead.

A couple important things to notice:

  • Always remember that (as written in the MySQL online docsAUTO_INCREMENT  values are always reused if you delete the row with the biggest AUTO_INCREMENT  value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused. Meaning that, if you use the first scenario, this feature will hit you multiple times - which can be easily a good thing, as long as you're aware of that.
  • If the AUTO_INCREMENT  column is part of multiple indexes, MySQL will generate sequence values using the index that begins with the AUTO_INCREMENT  column, if there is one, regardless of any Primary Key group. For example, if the animals  table above contained indexes PRIMARY KEY (grp, id)  and INDEX (id) , MySQL would ignore the PRIMARY KEY  for generating sequence values. As a result, our result would always be the latter one regardless of anything else.
  • IMPORTANT: If you're using this technique in a replication environment using a version of MySQL lower than 5.1.65 you might run into some consistency issues, as explained in the official docsAn INSERT into a table that has a composite primary key that includes an AUTO_INCREMENT column that is not the first column of this composite key is not safe for statement-based logging or replication. Beginning with MySQL 5.1.64, such statements are marked as unsafe. (Bug #11754117, Bug #45670). Luckily enough, this has been fixed in MySQL 5.1.65.

Unfortunately, if you run InnoDB, there's no way to do that by the book: the online documentation clearly says that any InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column. If you need to replicate a similar behaviour in an InnoDB environment you can take advantage of the InnoDB's clustered composite index feature as explaining in this StackOverflow entry and see if it can work for your own scenario.

That's about it: happy coding!


About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

Leave a Reply

Your email address will not be published. Required fields are marked *

The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.