Multi-column foreign keys

[Former user]

(original reporter: rtl) SQLAlchemy already supports multi-column primary keys but doesn't support their 'mirror image', multi-column foreign keys. Ideally, the current foreign key syntax should be extended to support specifying multiple columns and the output will need to be in the format of a separate constraint: e.g.

create table foo ( col1 integer primary key, col2 integer primary key )

create table foo2 ( col1 integer primary key, foo_col1 integer, foo_col2 integer ) FOREIGN KEY( foo_col1, foo_col2) REFERENCES foo(col1, col2)

As foreign keys are a type of constraint, it would be useful to base them on a separately constructable Constraint object, which is needed in any case when two tables are mutually dependent on each other. Constraint, while handling create/drop functionality like anything else, could also apply foreign key objects to the relevant Column objects as well so you can specify them in place of the usual foreign key objects.

[Former user]

(original author: evan) I've written createable/droppable/reflectable foreign key constraint objects as part of Migrate. I didn't plan on multi-column foreign keys, but I have multi-column primary key constraints that behave similarly and work fine, so it wouldn't be much of a stretch. (I'm not volunteering to do the logic of attaching multi-column FKs to tables; just their creation/deletion in the database.)

Right now they're Postgres-only, though the plan is to support other DBs within the next couple weeks. I'll prepare a patch when that's done.

[Mike Bayer]

as of changeset:1726 this is implemented as ForeignKeyConstraint, PrimaryKeyConstraint which subclass Constraint. the ForeignKey object interacts with ForeignKeyConstraint so both exist if just one is specified. reflection also works with sqlite, postgres, mysql and oracle; not sure if ms-sql supports composite primary keys and havent touched firebird.

[Mike Bayer]

Removing milestone: 0.3.0 (automated comment)