Set AUTO_INCREMENT using SqlAlchemy with MySQL on Columns with non-primary keys?

Question

I can't figure out how to set AUTO_INCREMENT on a UNIQUE column using SqlAlchemy 0.6.0 with MySQL 5.

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.

As far as I have found so far, there are two ways that a column can become an auto-incrementing type column in SqlAlchemy:

1. It is the first sqlalchemy.types.Integer column in the table that has primary_key=True and does not have autoincrement=False set in its definition.
   • With MSSQL this adds an INDEX(m,n) property to the column.
   • With Postgres this causes the SERIAL column type to be used.
   • With MySQL adds the AUTO_INCREMENT property to the column.
2. It is an sqlalchemy.types.Integer column, and is instantiated with an sqlalchemy.schema.Sequence object as an argument.
   • With MSSQL this adds an INDEX(m,n) property to the column.
   • With Postgres this causes the SERIAL column type to be used.
   • With MySQL this seems to get ignored.

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.

Basically, my code to define the table looks like the following:

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)

And produces this SQL to create the table:

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)
)

What can I do to get it to produce the following SQL?

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)
)

Answer 1

You might be interested in I need to auto_increment a field in MySQL that is not primary key

I'm not familiar with sqlalchemy, but I do know this can be accomplished in MySQL. You must first define the id column as the PRIMARY KEY and set it AUTO_INCREMENT. Then you can ALTER TABLE and DROP PRIMARY KEY, replacing it with an ADD UNIQUE KEY. This will preserve the AUTO_INCREMENT functionality on that column. You can then create the PRIMARY KEY on the other columns.

To break it down, your first step is to create the table thusly:

CREATE TABLE person (
    id INTEGER NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
);

Next modify the id key:

ALTER TABLE person DROP PRIMARY KEY, ADD UNIQUE KEY(id);

Finally, add your PRIMARY KEY on the other columns:

ALTER TABLE person ADD PRIMARY KEY(first_name, last_name);