AUTO_INCREMENT non-primary key (but unique) column in MySQL

[Anthony Theocharis]

Hi everybody,

I'm looking to create an auto increment column on a non-primary key column. I'm using SqlAlchemy 0.6.0 and MySQL 5

I can do this in plain SQL with the following:

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

but would like to do it with SQLAlchemy, so I can keep my whole schema defined in Python.

I've tried using a Sequence() object as an argument to my column definition, but to no avail with MySQL. Is it possible to just add the "AUTO_INCREMENT" string to the table definition? I realize this would be a MySQL-only solution, but would be willing to accept that for now.

I've made a StackOverflow post with more details: http://stackoverflow.com/questions/2937229/set-auto-increment-using-sqlalchemy-with-mysql-on-columns-with-non-primary-keys

Thanks,

Anthony Theocharis

[Michael Bayer]

your best bet for now is to issue an ALTER TABLE that applies the AUTO_INCREMENT, if MySQL supports that.

from sqlalchemy.schema import DDL

DDL("ALTER TABLE person ...", on='mysql').execute_at('after-create', person_table)

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.