Brazos Donaho wrote:
I have an application using MySQL where I want to have a composite
primary_key (multiple columns), but the columns are actually foreign keys to
other tables.
As you can see, having the first primary_key column created as Autoincrement
doesn't work. Are there any other options for creating this primary_key?
Multi-column primary keys are specified by designating all the required columns
as primary_key=True, e.g.
Column('user_id', Integer, ForeignKey('users.id'), primary_key = True),
Column('code', String, ForeignKey('foo.code'), primary_key = True),
Column('user_data', String),...
will create a table with user_id and date as the elements of the primary key,
being foreign keys of the users and foo tables respectively
There is currently an outstanding ticket to add support for multi-column foreign
keys, so if your primary key columns are all foreign keys of the same table,
currently you will need to manually create the foreign key constraint. I'm doing
this for an association between two tables each with multi-column primary keys
by using execute() directly, i.e.
engine.execute(
'''CREATE TABLE foo_membership(
baz_code VARCHAR(7) NOT NULL,
baz_date TIMESTAMP NOT NULL,
foo_code VARCHAR(7) NOT NULL,
foo_date TIMESTAMP NOT NULL,
FOREIGN KEY(baz_code, baz_date) REFERENCES baz_daily(code, date),
FOREIGN KEY(foo_code, foo_date) REFERENCES foo_daily(code, date),
PRIMARY KEY (baz_code, baz_date, foo_code, foo_date)
)''', {})
HTH
Robert
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=110944&bid=241720&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
