How to define two fields "unique" as couple

django django-models

Is there a way to define a couple of fields as unique in Django?

I have a table of volumes (of journals) and I don't want more then one volume number for the same journal.

class Volume(models.Model):
    id = models.AutoField(primary_key=True)
    journal_id = models.ForeignKey(Journals, db_column='jid', null=True, verbose_name = "Journal")
    volume_number = models.CharField('Volume Number', max_length=100)
    comments = models.TextField('Comments', max_length=4000, blank=True)

I tried to put unique = True as attribute in the fields journal_id and volume_number but it doesn't work.

Mark Mikofski

There is a simple solution for you called unique_together which does exactly what you want.

For example:

class MyModel(models.Model):
  field1 = models.CharField(max_length=50)
  field2 = models.CharField(max_length=50)

  class Meta:
    unique_together = ('field1', 'field2',)

And in your case:

class Volume(models.Model):
  id = models.AutoField(primary_key=True)
  journal_id = models.ForeignKey(Journals, db_column='jid', null=True, verbose_name = "Journal")
  volume_number = models.CharField('Volume Number', max_length=100)
  comments = models.TextField('Comments', max_length=4000, blank=True)

  class Meta:
    unique_together = ('journal_id', 'volume_number',)

I would say you'll get a "ValidationError" exception. Have a look at the Django docs: Model.validate_unique

How would you handle this say if volume_number could be null? Mysql won't seem to enforce unique in that case.

FYI it throws a django.db.utils.IntegrityError if you try to add a duplicate.

@Greg - According to the ANSI standard SQL:2003 (and previous ones as well), a UNIQUE constraint should disallow duplicate non-NULL values, but allow multiple NULL values (see draft wiscorp.com/sql_2003_standard.zip, Framework, p. 22). If you want your unique constraint to disallow multiple null values, you are probably doing something wrong, like using NULL as a meaningfull value. Remember, nullable field says "We don't always have a value for that field but when we do it must be unique.".

What about multiple unique_together constraints? For example - when I want to have mode columns to be unique in the scope of the parent? Well, this property is actually a tuple itself, see: docs.djangoproject.com/en/1.4/ref/models/options/… So your constraint should be more explicitly written as: unique_together = (('journal_id', 'volume_number',),).

Alonme

Django 2.2+

Using the constraints features UniqueConstraint is preferred over unique_together.

From the Django documentation for unique_together:

Use UniqueConstraint with the constraints option instead. UniqueConstraint provides more functionality than unique_together. unique_together may be deprecated in the future.

For example:

class Volume(models.Model):
    id = models.AutoField(primary_key=True)
    journal_id = models.ForeignKey(Journals, db_column='jid', null=True, verbose_name="Journal")
    volume_number = models.CharField('Volume Number', max_length=100)
    comments = models.TextField('Comments', max_length=4000, blank=True)

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=['journal_id', 'volume_number'], name='name of constraint')
        ]

In what situation would the 'name' parameter of the UniqueConstraint be used for? I assume it works like the name parameter of a URL path?

@user7733611 naming the constraint can be helpful in a number of situations. For example if you are connecting to a legacy database, or if you just want the constraint names to be more human readable in the database. One time I migrated the character set of a MySQL database and Django's generated constraint names were actually too long for our particular target.

Not 100% sure it comes from UniqueConstraint but I get weird psycopg2.errors.DuplicateTable: relation "name_of_the_constraint" already exists when I switch to Postgres

It should be noted that text fields like CharField can be case sensitive or case insensitive depending on your database configurations!

Arash Hatami

Yes you can define more than one field as unique using Django class Meta as this example:

class Volume(models.Model):
    id = models.AutoField(primary_key=True)
    journal_id = models.ForeignKey(Journals, db_column='jid', null=True, verbose_name = "Journal")
    volume_number = models.CharField('Volume Number', max_length=100)
    comments = models.TextField('Comments', max_length=4000, blank=True)

    class Meta:
        unique_together = ('volume_number', 'journal_id')

Note:
To make things go writes you should not add attribute unique=True to any field that you define in unique_together attribute otherwise it will not work as unique together.

SuperNova

in Django 4.0,

The new *expressions positional argument of UniqueConstraint() enables creating functional unique constraints on expressions and database functions. For example:

from django.db import models
from django.db.models import UniqueConstraint
from django.db.models.functions import Lower


class MyModel(models.Model):
    first_name = models.CharField(max_length=255)
    last_name = models.CharField(max_length=255)

    class Meta:
        constraints = [
            UniqueConstraint(
                Lower('first_name'),
                Lower('last_name').desc(),
                name='first_last_name_unique',
            ),
        ]