Composite primary key in django

Question

I have a legacy db table which has composite primary key. I don't think I will be able to change the structure to include a surrogate key, as there is some code written that uses that table. And in django, I cannot use that table, as it doesn't have a primary key(non-composite).

Do django models support composite primary keys? If not, is there any workaround without changing the structure of the table?

P.S. I am using postgresql.

score 89 · Accepted Answer · edited Jul 08 '16 at 12:37

89

Try similar below code:

class MyTable(models.Model):
    class Meta:
        unique_together = (('key1', 'key2'),)

    key1 = models.IntegerField(primary_key=True)
    key2 = models.IntegerField()

or if you want only unique mixed fields:

class MyTable(models.Model):
    class Meta:
        unique_together = (('key1', 'key2'),)

    key1 = models.IntegerField()
    key2 = models.IntegerField()

EDIT: I would like to note that there is a problem with this approach if there are 3 columns. Update queries don't work because it tries to update (puts pk fields right after "SET") the fields that are unique together and obviously fails.

edited Jul 08 '16 at 12:37

khajvah

4,889
9
41
63

answered Feb 25 '15 at 06:54

M.javid

6,387
3
41
56

in Meta class every tuple in `unique_together` attribute determining that mixed values of these fields by determined ordering must not duplicate. – M.javid Feb 25 '15 at 07:15
3

I mean, when you create `key2 = models.IntegerField(unique=True)`, shouldn't it imply that `key2` must be unique for all values not just for a single `key1`? – khajvah Feb 25 '15 at 07:18
1

Ok, for my case, the first one worked but I can't really understand why. The second version still needs a primary key. – khajvah Feb 25 '15 at 07:33
`unique_together` related `key2` value to `key1` value. – M.javid Feb 25 '15 at 07:53
Same here. The first code snippet worked. (Also working with a legacy db) – rprasad Jun 28 '16 at 18:12
2

There was a suggested edit saying the following: I would like to note that there is a problem with this approach if there are 3 columns. Update queries don't work because it tries to update (puts pk fields right after "SET") the fields that are unique together and obviously fails. – Vogel612 Jul 08 '16 at 10:56
Thing to be noted here is that the first solution is a workaround to make Django not to create `id` field. It creates `PRIMARY KEY` index only for `key1`. Not to both. – x-yuri Feb 08 '18 at 21:31
Just curious if this has been fixed? Because I tried to duplicate the issue by using an update query like the following, on data model named `Quote`, but it works without error: `Quote.objects.filter(exchange=exchange).update(time_units='1m')`. Is mine the correct way to perform an `Update query`? – MikeyE May 06 '18 at 19:42
2

Just a note that the first suggestion is *not* equivalent to having a composite primary key because setting `primary_key = True` on `key1` makes it unique, which means that you won't be able to add a new combination of `(key1, key2)` if the value of `key1` already exists in the table. A composite primary key is about unique _combinations_ of fields. The second suggestion kind of works, but it does not prevent Django from creating an 'id' column automatically. By the way, this is a [*15-year-old problem*](https://code.djangoproject.com/ticket/373)! – cantordust Apr 17 '20 at 07:12
@M.javid, may I know if this works at database level or in application itself? As I'm concerned in under LB, multiple app would be consistent? – Abhigyan Tiwari Nov 18 '22 at 09:50
Why is this marked as the accepted answer? I am pretty sure Django will spit out an error about key1 not being unique. – Matthaeus Gaius Caesar Mar 13 '23 at 05:43

score 33 · Answer 2 · answered Nov 25 '20 at 13:01

33

The accepted answer is fine. However, it's a little old. unique_together may be deprecated in favor of UniqueConstraint. So, the better way of doing this would be;

UniqueConstraint(fields = ['key1', 'key2'], name = 'constraint_name')

answered Nov 25 '20 at 13:01

The Voyager

617
9
17

1

The accepted answer mentions that you can only use a max of 2 fields for the composite key with their code - does this change with yours? – Shmack Nov 08 '21 at 19:23

score 13 · Answer 3 · answered Dec 22 '20 at 06:14

I solved this with virtual field inherited from django AutoField, that combines a values from several fields into single JSON dict.

That makes such models, compatible with django admin and genetic views.

$ pip install django-viewflow --pre

from viewflow.fields import CompositeKey

class Seat(models.Model):
    id = CompositeKey(columns=['aircraft_code', 'seat_no'])
    aircraft_code = models.ForeignKey(
        Aircraft, models.DO_NOTHING,
        db_column='aircraft_code'
    )
    seat_no = models.CharField(max_length=4)

This makes possible to access as to legacy databases, as to PostgreSQL TimeScaleDB tables

Is it possible to have other models with Seat as a foreign field with this method? — Kasper Skjeggestad, Mar 03 '21 at 12:00

score 6 · Answer 4 · answered Jul 31 '20 at 02:51

Another option is to set managed=False in the model's Meta, then manually create the table.

class MyTable(models.Model):
    foo = models.IntegerField(primary_key=True)
    bar = models.IntegerField()
    baz = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'myapp_mytable'

    def __repr__(self):
        return f'<MyTable: MyTable object ({self.foo}, {self.bar}, {self.baz)>'

In a postgres shell:

CREATE TABLE myapp_mytable (
    foo INTEGER NOT NULL,
    bar INTEGER NOT NULL,
    baz INTEGER NOT NULL,
    PRIMARY KEY(foo, bar, baz)
);

It appears to behave correctly:

>>> MyTable.objects.create(foo=1, bar=1, baz=1)
<MyTable: MyTable object (1, 1, 1)>

>>> MyTable.objects.create(foo=1, bar=1, baz=2)
<MyTable: MyTable object (1, 1, 2)>

>>> MyTable.objects.create(foo=1, bar=1, baz=2)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "myapp_mytable_pkey"
DETAIL:  Key (foo, bar, baz)=(1, 1, 2) already exists.

Note that this is only tested in Django 3.x, so I'm not sure if it works in older versions.

Composite primary key in django

4 Answers4

Linked