title: Set AUTO_INCREMENT using SqlAlchemy with MySQL on Columns with non-primary keys?
body: <p>I can't figure out how to set AUTO_INCREMENT on a UNIQUE column using SqlAlchemy 0.6.0 with MySQL 5.</p>
  
  <p>I know this can be done in MySQL, but I don't want to have to distribute extra .sql scripts in order to set up the databases for my application. I want SqlAlchemy to build the schema from within Python.</p>
  
  <p>As far as I have found so far, there are two ways that a column can become an auto-incrementing type column in SqlAlchemy:</p>
  
  <ol>
  <li>It is the first <code>sqlalchemy.types.Integer</code> column in the table that has <code>primary_key=True</code> and does not have <code>autoincrement=False</code> set in its definition.
  
  <ul>
  <li>With MSSQL this adds an <code>INDEX(m,n)</code> property to the column.</li>
  <li>With Postgres this causes the <code>SERIAL</code> column type to be used.</li>
  <li>With MySQL adds the <code>AUTO_INCREMENT</code> property to the column.</li>
  </ul></li>
  <li>It is an <code>sqlalchemy.types.Integer</code> column, and is instantiated with an <code>sqlalchemy.schema.Sequence</code> object as an argument.
  
  <ul>
  <li>With MSSQL this adds an <code>INDEX(m,n)</code> property to the column.</li>
  <li>With Postgres this causes the <code>SERIAL</code> column type to be used.</li>
  <li>With MySQL this seems to get ignored.</li>
  </ul></li>
  </ol>
  
  <p>So I can't use #1 because the column I want to auto_increment isn't in the primary key (nor should it be). And I can't use #2 because It doesn't work with MySQL.</p>
  
  <p>Basically, my code to define the table looks like the following:</p>
  
  <pre><code>from sqlalchemy import Column
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.schema import Sequence
  from sqlalchemy.types import Integer, Unicode
  Base = declarative_base()
  
  class Person(Base):
      __tablename__ = "person"
      id = Column(Integer, nullable=False, unique=True)
      first_name = Column(Unicode(100), nullable=False, primary_key=True)
      last_name = Column(Unicode(100), nullable=False, primary_key=True)
  </code></pre>
  
  <p>And produces this SQL to create the table:</p>
  
  <pre><code>CREATE TABLE person (
      id INTEGER NOT NULL, 
      first_name VARCHAR(100) NOT NULL, 
      last_name VARCHAR(100) NOT NULL, 
      PRIMARY KEY (first_name, last_name), 
      UNIQUE (id)
  )
  </code></pre>
  
  <p>What can I do to get it to produce the following SQL?</p>
  
  <pre><code>CREATE TABLE person (
      id INTEGER NOT NULL AUTO_INCREMENT, 
      first_name VARCHAR(100) NOT NULL, 
      last_name VARCHAR(100) NOT NULL, 
      PRIMARY KEY (first_name, last_name), 
      UNIQUE (id)
  )
  </code></pre>