title: Generate auto increment ID with composite primary key
body: <p>I would like to give an entity (Invoice, Order, Reservation, etc.) a unique sequence number, but only unique within that year. So the first invoice of every year (or another field, such as Customer) starts has id 1. That means that there can be a composite primary key (year, id) or one primary key (i.e. invoice_id) and two other columns that are unique together. </p>
  
  <p>My question: What's the best way to give an object a unique combination of an auto-generated ID and another value using Doctrine2 and Symfony2?</p>
  
  <p><strong>Doctrine limitations on composite keys</strong></p>
  
  <p>Doctrine can't assign an auto generated ID to an entity with a composite primary key (<a href="http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/composite-primary-keys.html" rel="nofollow noreferrer">http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/composite-primary-keys.html</a>):</p>
  
  <blockquote>
    <p>Every entity with a composite key cannot use an id generator other
    than “ASSIGNED”. That means the ID fields have to have their values
    set before you call <code>EntityManager#persist($entity)</code>.</p>
  </blockquote>
  
  <p><strong>Setting the sequence number manually</strong></p>
  
  <p>So I have to assign a ID manually. In order to do that, I've tried to look for the highest ID in a certain year and give the new entity that ID + 1. I doubt that's the best way and even if it is, I haven't found the best (DRY) way to do it. Since I think this is a generic question and I want to prevent the <a href="https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem">XY-problem</a>, I've started this question.</p>
  
  <p><strong>Vanilla options: only with MyISAM</strong></p>
  
  <p>I found the 'vanilla' MySQL/MyISAM solution based on <a href="https://stackoverflow.com/questions/5416548/mysql-two-column-primary-key-with-auto-increment">this answer</a>:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `invoice` (
    `year` int(1) NOT NULL,
    `id` mediumint(9) NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`year`,`id`)
  ) ENGINE=MyISAM;
  </code></pre>
  
  <p>That won't work because of the Doctrine2 limitations, so I'm looking for a Doctrine ORM equivalent of this vanilla MySQL solution. </p>
  
  <p><strong>Other solutions</strong></p>
  
  <p>There is a solution for InnoDB as well: <a href="https://stackoverflow.com/questions/18120088/defining-composite-key-with-auto-increment-in-mysql">Defining Composite Key with Auto Increment in MySQL</a></p>