1

I am aware of the fact, the if I use the ORM of Django every table has to have a primary key column. Somehow if you have a many_to_many table which links to tables (let's call them authors and books) you would get something like:

id     author_id     book_id
1      1             1
2      1             2
3      2             3
etc.

I have encountered a book in which it is proposed to avoid the column "id" and to create a compound primary key instead. Does this work out with django?

Aufwind
  • 25,310
  • 38
  • 109
  • 154
  • I'm interested in the arguments for/against. Where is the article? – Sonny Aug 03 '11 at 15:16
  • 1
    For a link table, you don't need to access it directly. Just use Django's ManyToManyField and specify the link table using the 'through' parameter. See: https://docs.djangoproject.com/en/dev/topics/db/models/#many-to-many-relationships – a'r Aug 03 '11 at 15:19
  • 1
    @Sonny: I wrote *article* and meant *book*. Its title is **SQL Antipatterns: Avoiding the Pitfalls of Database Programming** written by **Bill Karwin**. He states, that if you have to put a compound unique constraint over (in the example above) `author_id` and `book_id`. If you do so, the compound key would have the same properties as the `id` column. So it gets redundant. The passage is long and has much more arguments in it but I think this is the essence of it, if I am not mistaken, that is. :-) – Aufwind Aug 03 '11 at 15:29
  • That's nice and all, but his advice doesn't apply well to django since django doesn't support compound primary keys. I'd infact argue that in general compound pk are a bad idea since it ties your keys to your data, makes your pk's much wider, and has all sorts of other small negatives...all this to avoid a redundant unique together. If I get really motivated later I'm might look up some articles on the reasons to never use compound pk's – John Aug 03 '11 at 15:34

1 Answers1

2

You could create a compound primary key on your through table (with ALTER TABLE). You could also drop the id column from the table. None of this would harm django in way since the way ManyToMany fields work in the backend they wouldn't use the id column anyway.

However you should note that getting compound PK to work in django is basically a non starter. This shouldn't be an issue for you as no table should have a ForeignKey to your through table (for any reason that I can think of at least.

So in summary. Compound primary keys don't work with django. So if you ever need to have a ForeignKey to a table with a compound PK you are basically SOL. Finally there is no real pro about using a compound PK here, but no real con either (in this one and only case). So why are you spending your time worrying about this?

John
  • 5,166
  • 27
  • 31
  • I am just trying to understand the ideas of the author and to expend my knowledge of the doable. Nothing productive going on here. By the way, what does *SOL* mean, please? – Aufwind Aug 03 '11 at 15:32
  • Likely the author's argument would be that by creating the redundant unique constraint you'd end up writing two index's for this table instead of one; adding a small amount of write overhead. And to top it off the index on id would likely never get used. In this one case you are most likely safe with django to create the compound pk, but you can't expand this experience to basically any other relationship – John Aug 03 '11 at 15:51