26

A field on a model, foo = models.ForeignKey(Foo) will automatically add a database index for the column, in order to make look-ups faster. That's good and well, but Django's docs don't state whether the fields in a model-meta's unique_together receive the same treatment. I happen to have a model in which one char field which is listed in unique_together requires an index for quick lookups. I know that it won't hurt anything to add a duplicate db_index=True in the field definition, but I'm curious.

orokusaki
  • 48,267
  • 47
  • 159
  • 244

5 Answers5

29

For anyone coming here wondering if they need an index_together in addition to unique_together to get the index's performance benefit, the answer for Postgres is no, they are functionally the same.

Tom
  • 19,972
  • 3
  • 61
  • 86
13

If unique_together does add an index, it will be a multiple column index.

If you want one of the columns to be indexed individually, I believe you need to specify db_index=True in the field definition.

Alasdair
  • 253,590
  • 43
  • 477
  • 449
  • 1
    I can confirm this from postgresql, here is an example from the postgresql console of a uniquere_toguether index: `public | registration_something | registration_something_field_5c0b3bdb9d08b87c_uniq | | CREATE UNIQUE INDEX registration_something_field_5c0b3bdb9d08b87c_uniq ON registration_something USING btree (field1, field2)` – Hassek Jan 10 '14 at 20:07
8

unique_together does not automatically add indexes for each field included in the list.

The new versions of Django suggest using Index & constraint meta options instead:

https://docs.djangoproject.com/en/2.2/ref/models/options/#unique-together

https://docs.djangoproject.com/en/2.2/ref/models/options/#index-together

orokusaki
  • 48,267
  • 47
  • 159
  • 244
auvipy
  • 597
  • 7
  • 13
  • Hey there, thanks for this new answer. The new constraint API is awesome. I’ve added a bit above your answer to answer the original question directly, so that I can except this as the new correct answer, because it’s much more relevant now. – orokusaki May 22 '19 at 12:43
7

In Django 1.5 and higher, you can use the {Model}.Meta.index_together class attribute. If you had two fields named foo and bar, you would add:

class Meta(object):
    index_together = unique_together = [
        ['foo', 'bar']
    ]

If you have only one set of unique fields, you can use a one-dimensional iterable for unique_together. However, the documentation does not indicate that the same applies to index_together.

This would also be okay:

class Meta(object):
    unique_together = 'foo', 'bar'
    index_together = [
        ['foo', 'bar']
    ]

This, however, is NOT supported by the documentation:

class Meta(object):
    unique_together = 'foo', 'bar'
    index_together = 'foo', 'bar'
Joe Tricarico
  • 353
  • 2
  • 5
  • 2
    `index_together` with 2 fields is different than `db_index=True` for the same 2 fields. `index_together` allows you to combine multiple indexes into 1, so that clauses like `WHERE a = 5 AND b = 10` can be compared while the database scans a single index. – orokusaki May 09 '14 at 19:35
1

According to the docs, it will only enforce uniqueness on database level. I think generally making a field unique does not imply it has an index. Though you could also simply check on db level if the index exists. Everything indicates though it does not.

Torsten Engelbrecht
  • 12,227
  • 4
  • 39
  • 47
  • The "imply" part of my question pertains to Django-specific behavior. `models.ForeignKey` sets `db_index=True` by default, in the field definition. I believe as well that a `unique=True` field also creates an index (database-level), which is how the unique checks operate quickly (correct me, if I'm wrong). – orokusaki Oct 20 '11 at 03:05
  • I think that depends how your database engine handles UNIQUE constraints. Most do set an index if I am right. This has nothing to do with Django though. – Torsten Engelbrecht Oct 20 '11 at 03:48
  • Indeed, the `models.ForeignKey` part has to do with Django, not the `unique=True`. I mentioned the `unique=True` as an aside, because of the `making a field unique does not...` portion of your answer. – orokusaki Oct 20 '11 at 04:02
  • 2
    `unique_together` is also enforced at the database level and not in Django. Assuming that your Database engine does support indexes on unique columns automatically, the index will be set. If not, then not. That's your answer. Additionally browsing through Django's code does not indicate they would set a db index on a `unique_together`. – Torsten Engelbrecht Oct 20 '11 at 08:09