Geek Answers Handbook

Django Multi-Column Foreign Key

Is it possible to define foreign keys that reference multiple columns in another model?

For example, one foreign key refers to an index with two columns in the product table and an SQL statement:

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

BTW I looked at django.contrib.contenttypes and I don't think this is the perfect solution for such a scenario.

+6
source share
3 answers

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

Support for multiple core columns

Relational database structures 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 “composite” primary key. (For more on terminology, see an article that discusses database keys). Currently, Django models support only one column in this set, negating the many constructs where several columns are the natural primary key of a table. Django is currently unable to work with these schemes; instead, they must enter a redundant single-column key ("surrogate" key), forcing applications to make arbitrary and unnecessary choices about which key to use for the table in any given instance. This page discusses how to support Django with these composite primary keys. There are a lot of details, but done correctly, this will provide greater flexibility and potential simplicity in data modeling.

Current status

The current state is that the problem is accepted / assigned and processed, and a partial implementation is performed at http://github.com/dcramer/django-compositepks. Implementation allows you to have composite primary keys. However, support for composite keys is not available in ForeignKey and RelatedManager. As a result, it is not possible to correlate relationships with models with a composite primary key.

Discussion:

David Cramer Starter Patch

Composition API External API API

Ticket

Note. SqlAlchemy allows this as described below and you can use SqlAlchemy to replace Django ORM

Foreign keys can also be defined at the table level using the ForeignKeyConstraint object. This object can describe one or more columns of a foreign key. A multi-column foreign key is known as a composite foreign key and almost always refers to a table with a composite primary key. Below we define an invoice table that 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 the invoice_item table with a composite foreign key referencing the 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']) )

Link

+11
source

Yes, it is possible, but you will need to create a composite key when you use several column constraints, i.e. a foreign key or primary key. For instance:

 CREATE TABLE Student ( S_num INTEGER, S_Cate INTEGER, S_descr CHAR(200), PRIMARY KEY (S_num, S_Cate)) CREATE TABLE sub_Student ( Ssub_ID INTEGER PRIMARY KEY, Sref_num INTEGER, Sref_Cate INTEGER, sub_descr CHAR(500), FOREIGN KEY (Sref_num, Sref_Cate) REFERENCES Student (S_num, S_Cate))
0
source

In any case, you can create a "Django fixture" as follows:

 CREATE INDEX product_category_id_id ON product (category_id, id);

To do this, you must create a file called product.sql in the sql subfolder where your model is located. Charging is loaded on the original clock.

0
source

More articles:


All Articles