session.merge() and composite primary key with 'id' column.

[Sam Magister]

Hi,

I was poking around the SQLA 0.5 documentation for the session.merge()
method and it seems to do exactly what I need it to do as long as I
designate a composite primary key for my tables. What I'm doing is
basically right out of the documentation which I quote here:

"An application which reads an object structure from a file and wishes
to save it to the database might parse the file, build up the
structure, and then use merge() to save it to the database, ensuring
that the data within the file is used to formulate the primary key of
each element of the structure. Later, when the file has changed, the
same process can be re-run, producing a slightly different object
structure, which can then be merged() in again, and the Session will
automatically update the database to reflect those changes."

My only problem is that each of my tables needs an auto-increment id
column because of foreign key references from other tables. However
what dictates whether a row should be inserted or updated depends on a
composite primary key. For example, ideally I would define my
manufacturer_table as follows so that a unique manufacturer is a
unique (name, city):

manufacturer_table = Table('manufacturer', metadata,
Column('id', Integer, autoincrement=True),
Column('name', String(30),
primary_key=True),
Column('city', String(30),
primary_key=True),
Column('description', Text))

However, MySQL Innodb tables do not allow for an autoincrement column
to exist if it is not the primary key.

The reason I would like to use the composite primary key is so I can
use the session.merge() function to let SQLA deal with whether to
insert or update (and make my code cleaner).

Has anyone run into this issue? Is it worth it to look into PostgreSQL
as an alternative to allow this functionality?

Thanks very much.

Sam

[Jonathan Ellis]

On Mon, Aug 4, 2008 at 5:09 PM, Sam Magister <smag...@gmail.com> wrote:
> My only problem is that each of my tables needs an auto-increment id
> column because of foreign key references from other tables. However
> what dictates whether a row should be inserted or updated depends on a
> composite primary key. For example, ideally I would define my
> manufacturer_table as follows so that a unique manufacturer is a
> unique (name, city):
>
> manufacturer_table = Table('manufacturer', metadata,
> Column('id', Integer, autoincrement=True),
> Column('name', String(30),
> primary_key=True),
> Column('city', String(30),
> primary_key=True),
> Column('description', Text))
>
> However, MySQL Innodb tables do not allow for an autoincrement column
> to exist if it is not the primary key.

Your schema is broken. You can fix it in either of two ways:

- use the natural key in foreign keys as well and drop the surrogate key
- designate the surrogate key as the PK and just make a unique
constraint covering the natural key columns

-Jonathan

[Michael Bayer]

If the table has its primary key defined by "name" and "city", typical
relational design would dictate that you wouldn't have an "id" at all,
and theres no issue with this. Foreign keys from other tables would
just have their own "name" and "city" columns that reference the
manufacturer table, this is called a composite foreign key. If you
truly wanted to generate the "id", a database like Postgres would
supply sequences which you can use, but the best you can do in MySQL
is to build your own ID generation function which you can wire in via
sqlalchemy using the "default" keyword to Column.

However if you truly want a surrogate primary key, i wouldnt modify
the schema just to suit the merge() method. What's wrong with the
__new__() strategy I gave you at http://groups.google.com/group/sqlalchemy/browse_thread/thread/79736ff7ef81d1b9/0b80b54dc45ecc28?hl=en&lnk=gst&q=__new__#0b80b54dc45ecc28
? (or is this just google groups delivering emails a week late
again ?)

[Sam Magister]

I think the second suggestion is the way to go.

[Sam Magister]

Michael,

Thanks for the comments. I realize that I could do without the id
columns in principle, but in practice I do need them for unrelated
reasons. You are right I'm going to use the __new__() strategy you
mentioned before.

Thanks,

Sam