14

I am writing a web application for my engineering company (warning: I am a programmer only by hobby) and was planning on using Django until I hit this snag. The models I want to use naturally have multi-column primary keys. Per http://code.djangoproject.com/ticket/373, I can't use Django, at least not a released version. Can anyone help me with a workaround, whether it be via another web framework (Python-based only, please) or by suggesting changes to the model so it will work with Django's limitations? I am really hoping for the latter, as I was hoping to use this as an opportunity to learn Django.

Example: Table one has part_number and part_revision as two fields that should comprise a primary key. A P/N can exist at multiple revisions, but P/N + rev are unique.

Table two has part_number, part_revision and dimension_number as its primary key. A P/N at a specific rev can have a number of dimensions, however, each is unique. Also, in this case, P/N + rev should be a ForeignKey of Table one.

Matthew DeNardo
  • 143
  • 1
  • 1
  • 4

4 Answers4

24

Why not add a normal primary key, and then specify that part_number and part_revision as unique_together?

This essentially is the Djangoish (Djangonic?) way of doing what Mitch Wheat said.

Dominic Rodger
  • 97,747
  • 36
  • 197
  • 212
20

A work around is to create a surrogate key (an auto increment column) as the primary key column and place a unique index on your domain composite key.

Foreign keys will then refer to the surrogate primary key column.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
14

I strongly suggest using a surrogate key. Not because it is "Djangoesque". Suppose that you use a composite key that includes part_number. What if some time later your company decides to change the format (and therefore values) of that field? Or in general terms, any field? You would not want to deal with changing primary keys. I don't know whatever benefit you see in using a composite key that consists of "real" values, but I reckon it isn't worth the hassle. Use meaningless, autoincremented keys (and that should probably render a composite key useless).

shanyu
  • 9,536
  • 7
  • 60
  • 68
  • 5
    This should be modded up a lot. A good article on why surrogates are a better idea is: http://www.agiledata.org/essays/keys.html – cethegeek Oct 26 '09 at 13:21
  • 1
    The big issue with the software industry is that everyone is trying to fix the given problem on the bigger scale. What if I, on the other hand - am trying to apply Django to a predefined dataset whose structure may or may not be changed? What's more important is that this is one of the use-cases of Django itself: a "framework for perfectionists with deadlines". – Andrey Cizov Sep 22 '16 at 17:00
2

SQLAlchemy has support for composite primary and foreign keys, so any SQLAlchemy based framework (Pylons and Werkzeug comes to mind) should suite your needs. But surrogate primary key is easier to use and better supported anyway.

Denis Otkidach
  • 32,032
  • 8
  • 79
  • 100