tables with multiple primary keys cant be created

[sqlalchemy-bot]

Migrated issue, originally created by Michael Bayer (@zzzeek)

the PRIMARY KEY clause can only be specified once to a CREATE statement. need a way to get the extra primary keys in there, probably via UNIQUE.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: high priority
• changed title from "write unit tests for ORM with a basic multiple pri" to "tables with multiple primary keys cant be created"
• set milestone to "first release"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

we are going to use UNIQUE to put a clustered unique constraint on all the "primary keys" specified in a Table when its created. only the first primary_key in the column list will actually be called 'primary key'.

as far as when we reflect such a table back from the DB, im not sure yet. how do we differentiate an ordinary unique index from one that is a clustered primary key ? might want to skip this step.

multiple primary keys are already supported by the Mapper if you explicitly send the "primary_keys" argument, which will name any arbitrary cols as primary keys to be used for an object's ID. I might just leave it this way, so that a multi-primary-key ORM is possible, just needs a little more explicitness in some cases.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• removed labels: orm
• added labels: schema

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

ok, that went differently than I thought. both postgres and sqlite support a separate PRIMARY KEYS (col1, col2) clause. however, when you use it with sqlite, sqlite suddenly will not autoincrement anymore.

so, the default behavior when a table has multiple primary keys is to use PRIMARY KEYS (col1, col2). with SQLIte, it creates the first column as a PRIMARY KEY, then creates a UNIQUE index encompassing all the primary keys. it also will reflect a table with a UNIQUE index containing a primary key as a multiple primary key table.

when using tables with multi-primary keys, its safe to assume the first primary key column is autoincrementing. successive keys, its not. it works with postgres, not with sqlite. so in that case, an application has to populate all but the first primary key on a table or object instance before INSERT or objectstore.commit().

changeset 651. primary key unit tests added previously to test/objectstore.py and test/engines.py.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed