Django Multi Column Primary and Foreign Key usage example needed

Question

I am trying to have a implementation in Django models where Table 1 will have 5 columns, say like:

table1 : (column1, column2, column3, column4, column5)

where I want column1, column2 to act as composite primary key instead of Id column of Django.

table 2: (column6, column7, column8, column9, column10, column 11)

where column6, column7 are foreign keys which refer to primary keys of column1, column2 in table 1 and which also act as composite primary keys in table 2.

How can I achieve this in Django, so that as a admin user i can add data to table 1 and also add data to table 2, where first two columns of table 2 i.e column6, column7 is autopopulated with column1, column2 data when i want to add data to table 2.

Is this possible in DJango, if yes pls provide concrete working example?

https://stackoverflow.com/questions/2270808/compound-composite-primary-unique-key-with-django/10139474 — iklinac, May 13 '20 at 17:56
Does this answer your question? [Compound/Composite primary/unique key with Django](https://stackoverflow.com/questions/2270808/compound-composite-primary-unique-key-with-django) — Stephen M, May 13 '20 at 20:09

Ameya Joshi · Accepted Answer · 2020-05-16T11:42:30.843

1

Basic property of primary key is that it is unique and have index on it. so try using unique_together and index_together options in Meta class. e.g.

class Model1(models.Model):
    col1 = ....
    col2 = ....

    class Meta:
          unique_together = (('col1', 'col2'),)
          index_together = (('col1', 'col2'),)

Here is the official doc.

If you want to avoid default id column as primary key,

class Model1(models.Model):
    # `models.IntegerField` is just for example.
    col1 = models.IntegerField(primary_key=True)
    col2 = ....

    class Meta:
          unique_together = (('col1', 'col2'),)

edited May 16 '20 at 11:42

answered May 13 '20 at 17:41

Ameya Joshi

384
1
5

Thank you, here is what i was trying, but still id column is getting created in the table. Below are my models. Basically i have 3 tables. table 1:Flat type: ex: 1 BHK, 2 BHK etc table 2: Flat type detail: 1 BHK, 1000 sqtf, 1 BHK 800 sqft, 2 BHK 1200 sqft, 2 BHk 100 sqft table 3:BlockBaseImage: 1 BHK 1000 sqft block A 5,1 BHK 800 sqft block A 2, – Babu Ajay Kumar May 16 '20 at 11:26
yeah, so "id" column is kind of default primary key column for Django. Django don't let you create table without any primary key.. It first checks if you have created primary key or not, if not, django adds "id" column by default. So if you don't want to create this ID column, you will have make some other column as Primary key, and then just add `unique_together` constraint with some other column. to have it as composite primary key. – Ameya Joshi May 16 '20 at 11:33
Ok thanks a lot, let me try it and will let you know. – Babu Ajay Kumar May 16 '20 at 11:38
Hi Ameya i added like below table 1: flat_type = models.ForeignKey(FlatType, on_delete=models.DO_NOTHING,primary_key=True) sqft = models.IntegerField() class Meta: unique_together = (('flat_type', 'sqft'),) index_together = (('flat_type', 'sqft'),) but now when i want to add a 2nd row with same value for flat_type using django admin it does not allow. These 2 columns needs to be uniqe and should allow me to add data. and i want to use these same 2 columns as primary key in a new table with additional columns. I am pretty confused. Can you pls help me. – Babu Ajay Kumar May 16 '20 at 13:41
ohh. Got it. Sorry I didn't think this through before suggesting this approach.. Now, you are not able to add 2nd row with same flat_type is because it is primary key.. it means it is unique column by default. And I have searched through some docs, Django does not allow compound/composite primary key. So the way you want to implement this is not possible.. So, to make this work, remove flat_type as primary key, and let id column be the primary key for your table. Just add unique_together and index_together constraint to flat_type and sqft column (which you have already donel.) – Ameya Joshi May 16 '20 at 14:24
Thank you I will go ahead with using id key. but how can i refer flat_type,sqft(are unique together) columns as foreign keys in table 2 (which also acts as primary key in this table), because even that throws error. i cannot use 2 references for same table twice as below. block_flat_type = models.ForeignKey(FlatTypeDetail, on_delete=models.DO_NOTHING) block_sqft = models.ForeignKey(FlatTypeDetail, ,on_delete=models.DO_NOTHING) block_flat_type: (fields.E304) Reverse accessor for 'BlockBaseImage.block_flat_type' clashes with reverse accessor for 'BlockBaseImage.block_sqft'. – Babu Ajay Kumar May 16 '20 at 14:35
You can not use two columns as foreign key in another table i.e. both column referencing to same table. And when you have primary key in your table (i.e. id) , you don't use another column as foreign key in second table. So in table 2 just use: table1 = models.ForeignKey(FlatTypeDetail, on_delete=models.DO_NOTHING). This will create "table1_id" column in your database. – Ameya Joshi May 16 '20 at 14:47
i wanted to explore this composite primary key as we use in conventional DBs without having a id column, but seems with django its not allowed. But thanks a lot for your quick response i will go ahead with single id key only and see how we retrieve data and playaround. – Babu Ajay Kumar May 16 '20 at 16:12

Django Multi Column Primary and Foreign Key usage example needed

1 Answers1