Composite primary key in django

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.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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')

Method 3

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

Method 4

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0