Django Multi-Column Foreign Key

Question

Is is possible to define foreign keys referencing multi columns in another model?

For example one foreign key references a two-column index in the product table, and the SQL statement:

FOREIGN KEY (product_category, product_id) REFERENCES product(category, id)

BTW I've looked into django.contrib.contenttypes and don't think that's the perfect solution for this kind of scenario.

Answer 1

It is not supported yet. There is a ticket and possible ways to handle it if you want to. maybe you could even run custom sql

Multi-Column Primary Key support

Relational database designs use a set of columns as the primary key for a table. When this set includes more than one column, it is known as a “composite” or “compound” primary key. (For more on the terminology, here is an ​article discussing database keys). Currently Django models only support a single column in this set, denying many designs where the natural primary key of a table is multiple columns. Django currently can't work with these schemas; they must instead introduce a redundant single-column key (a “surrogate” key), forcing applications to make arbitrary and otherwise-unnecessary choices about which key to use for the table in any given instance. This page discusses how to have Django support these composite primary keys. There are a lot of details to get right here, but done right, it would allow for more flexibility and potential simplicity in data modeling.

Current Status

Current state is that the issue is accepted/assigned and being worked on, and there is a partial implementation at ​http://github.com/dcramer/django-compositepks. The implementation allows having composite primary keys. However, support for composite keys is missing in ForeignKey and RelatedManager. As a consequence, it isn't possible to navigate relationships from models that have a composite primary key.

Discussions:

David Cramer's initial patch

The composite foreign key API design

Ticket

Note - SqlAlchemy allows this as described below and you can use SqlAlchemy to replace Django's ORM

Foreign keys may also be defined at the table level, using the ForeignKeyConstraint object. This object can describe a single- or multi-column foreign key. A multi-column foreign key is known as a composite foreign key, and almost always references a table that has a composite primary key. Below we define a table invoice which has a composite primary key:

invoice = Table('invoice', metadata,
    Column('invoice_id', Integer, primary_key=True),
    Column('ref_num', Integer, primary_key=True),
    Column('description', String(60), nullable=False)
)

And then a table invoice_item with a composite foreign key referencing invoice:

invoice_item = Table('invoice_item', metadata,
    Column('item_id', Integer, primary_key=True),
    Column('item_name', String(60), nullable=False),
    Column('invoice_id', Integer, nullable=False),
    Column('ref_num', Integer, nullable=False),
    ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id', 'invoice.ref_num'])
)

Reference