10

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.

S. Liu
  • 1,018
  • 2
  • 10
  • 25

4 Answers4

15

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

Pratik Mandrekar
  • 9,362
  • 4
  • 45
  • 65
0

Yes its possible but you will need to create a composite key when you use multiple column constraint i.e. foreign key or primary key. For example:

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))
Tomasz Jakub Rup
  • 10,502
  • 7
  • 48
  • 49
edwinmiller
  • 44
  • 2
  • thanks for the answer but i'm afraid this is not the solution for the Django model i'm talking about. it is possible for a database to have composite foreign keys but it seems Django doesn't provide that kind of support yet. – S. Liu Nov 06 '12 at 08:43
0

Anyway, you can to create a "Django fixture" like this:

CREATE INDEX product_category_id_id ON product (category_id, id);

To do this, you must to create a file named product.sql on subfolder sql where your model resides. The fixture is loaded on initial syncdb.

Tomasz Jakub Rup
  • 10,502
  • 7
  • 48
  • 49
Ricardo Carmo
  • 643
  • 5
  • 6
0

@pratik-mandrekar's answer is excellent, but I wanted to point out that even without proper multi-column primary keys; django is able to accommodate queries spanning multi-column foreign keys. Here's an example based on a legacy database who's schema I wasn't permitted to modify:

Given:

from django.db import models

class Account(models.Model):
    # Collectively, location_no and occupant_no function as the primary key for Account.
    location_no = models.IntegerField()
    occupant_no = models.SmallIntegerField()

    name = models.CharField(max_length=100)

    class Meta:
        managed = False
        db_table = 'csracct'
        unique_together = (('location_no', 'occupant_no'),)

class Call(models.Model):
    call_id = models.IntegerField(primary_key=True)
    
    # Collectively, location_no and occupant_no act as a foreign key to Account.
    location_no = models.IntegerField()
    occupant_no = models.SmallIntegerField()

    notes = models.TextField()

    class Meta:
        managed = False
        db_table = 'csrcall'

Here's how you'd use extra() to fetch the 10 most recent calls for accounts with the name 'steve':

calls = Call.extra(
    tables = ['csracct'],
    where = [
        'csracct.location_no=csrcall.location_no',
        'csracct.occupant_no=csrcall.occupant_no',
        'csracct.name=%s',
        ],
    params = ['steve'],
    ).order_by('-call_id')[:10]

It's not the most elegant solution, but extra() is part of django's base queryset toolkit; so it plays well with the rest of your django code. Notice how we order_by, and limit/slice the queryset using the usual django methods.

Aaron
  • 2,409
  • 29
  • 18