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   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   statement. The official MySQL online manual says that:

For MyISAM and BDB tables you can specify  on a secondary column in a multiple-column index. In this case, the generated value for the   column is calculated as  . This is useful when you want to put data into ordered groups.

Meaning that, in order to get the first result, you need to   your table putting the   column before the   column when you declare the   , 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 docs  values are always reused if you delete the row with the biggest   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   column is part of multiple indexes, MySQL will generate sequence values using the index that begins with the   column, if there is one, regardless of any Primary Key group. For example, if the   table above contained indexes   and  , MySQL would ignore the   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  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!

 

RELATED POSTS

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.

View all posts by Ryan