Christian Schwanke wrote:
>
> 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
there are separate issues here. one is the DDL issue, for which your
execute_at() workaround seems to work, or alternatively just specifying
the autoincrement column first would work.
The other is the "id" not being first gets missed by SQLA's lastrowid
logic. this is a very old issue but was only reported for the first time
in history two days ago, so i am continuously amazed at how even the most
obscure bugs get reported in clusters. This issue doesn't have anything
to do with DDL afaict - its strictly that 0.5 assumes the autoincrement
column is the first one in the primary key.
The "lastrowid" issue fixed in 0.6 so I would advise working with the 0.6
branch which should be merged to trunk any day now. The DDL part of the
equation is still not working, in which case your patch that adds the DDL
MySQL is looking for could be useful (adding a patch to trac would be
helpful here). It should only take effect if needed, i.e. if InnoDB is in
use.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
