I'm using MySQL and want to use a combined primary-key where the second column is autogenerated while the first part is an assigned value. This is how my Table-definition looks like:
table_a = Table('table_a', metadata, Column('assigned_id', Integer(), primary_key=True, autoincrement=False), Column('id', Integer(), primary_key=True, autoincrement=True), mysql_engine='InnoDB' ) However, the MySQL-Dialect is not able to generate this table: OperationalError: (OperationalError) (1075, 'Incorrect table definition; there can be only one auto column and it must be defined as a key') The DDL generated looks like this: CREATE TABLE table_a ( assigned_id INTEGER NOT NULL, id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (assigned_id, id) )ENGINE=InnoDB What's missing here is the explicit key for the autoincremented column. The correct code has to look like this: CREATE TABLE table_a ( assigned_id INTEGER NOT NULL, id INTEGER NOT NULL AUTO_INCREMENT, PRIMARY KEY (assigned_id, id), KEY key_autoinc(id) )ENGINE=InnoDB This error only occurs if the autoincremented column is not the first column of the primary key and innodb is used as storage engine. I tried to work around this problem by explicitly setting autoincrement to False and altering the table with an DDL.execute_at()-call. That worked for the DDL but afterwards the generated id was not propagated to dependent relations (the docs say autoincrement is only used during DDL generation but that's not what I experienced). Since this solution is not very elegant and didn't work for me due to the broken id propagation, I looked into the MySQL-Dialect of SQLA itself. I've now fixed the problem locally by modifying the MySQL-Dialect that comes with SQLA. I copied the visit_create_table method from DDLCompiler ton the MySQLDDLCompiler class and modified it so that a separate Key is appended if an autoincremented column exists that is not the first PK column) To reproduce the error: import logging import sqlalchemy from sqlalchemy import * from sqlalchemy.orm import * metadata = MetaData() table_a = Table('table_a', metadata, Column('assigned_id', Integer(), primary_key=True, autoincrement=False), Column('id', Integer(), primary_key=True, autoincrement=True), mysql_engine='InnoDB' ) # Models class ItemA(object): def __init__(self, name): self.name = name # Mapping mapper(ItemA, table_a) # Init engine engine = create_engine('mysql://localhost/sqltest', connect_args= {'user':'root', 'passwd':''}, echo=True) engine_logger = sqlalchemy.log.instance_logger(engine) engine_logger.setLevel(logging.DEBUG) metadata.drop_all(engine) metadata.create_all(engine) session_factory = scoped_session(sessionmaker(bind=engine)) session = session_factory() itemA1 = ItemA(name = 'ItemA1') session.add(itemA1) session.commit() In my modified method, I store the auto_incremented column in a local variable ("auto_inc_column") and add the KEY-Part if this variable is set. Most of the code is copy/pasted from the 0.6-version of the DDLCompiler class, so this fix is probably not very elegant but it works for me. Here is the modified version that I currently use: def visit_create_table(self, create): table = create.element preparer = self.dialect.identifier_preparer text = "\n" + " ".join(['CREATE'] + \ table._prefixes + \ ['TABLE', preparer.format_table(table), "("]) separator = "\n" # if only one primary key, specify it along with the column first_pk = False auto_inc_column = None for column in table.columns: text += separator separator = ", \n" text += "\t" + self.get_column_specification(column, first_pk=column.primary_key and not first_pk) if column.primary_key and column.autoincrement and first_pk: auto_inc_column = column if column.primary_key: first_pk = True const = " ".join(self.process(constraint) for constraint in column.constraints) if const: text += " " + const # On some DB order is significant: visit PK first, then the # other constraints (engine.ReflectionTest.testbasic failed on FB2) if table.primary_key: text += ", \n\t" + self.process(table.primary_key) const = ", \n\t".join( self.process(constraint) for constraint in table.constraints if constraint is not table.primary_key and constraint.inline_ddl and (not self.dialect.supports_alter or not getattr(constraint, 'use_alter', False)) ) if const: text += ", \n\t" + const # append KEY for autoincrement column if necessary if auto_inc_column is not None: text += ", \n\t" + "KEY `idx_autoinc_%s`(`%s`)" % (auto_inc_column.name, self.preparer.format_column(auto_inc_column)) text += "\n)%s\n\n" % self.post_create_table(table) return text --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---