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.
Answer #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.
Answer #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')
Answer #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
Answer #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.
