unique_together in Django doesn't work

Question

unique_together doesn't work, it only set the unique constraints on the first field and ignore the second field. Is there any way to enforce unique constraints?

class BaseModel(models.Model):

    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    deleted = models.DateTimeField(db_index=True, null=True, blank=True)
    last_modified_at = models.DateTimeField(auto_now=True)

    class Meta:
        abstract = True


class Book(BaseModel):
    first_form_number = models.CharField(max_length=8)

    class Meta:
        unique_together = (("first_form_number", "deleted"),)

Possible duplicate of [How to have abstract model with unique\_together?](https://stackoverflow.com/questions/7236871/how-to-have-abstract-model-with-unique-together) — Brown Bear, Aug 25 '17 at 12:39
Not an exact duplicate, since OP is subclassing an abstract class (which was the issue in the other question) — dirkgroten, Aug 25 '17 at 15:24
It is not a duplicate at all because Django works correctly here and this problem is related to the database underneath whereas in the linked question about Django bug it failed to create the right index. — hynekcer, Aug 26 '17 at 19:30

score 5 · Accepted Answer · answered Aug 25 '17 at 22:12

Your models work correctly in that extent that the right unique index is created:

$ python manage.py sqlmigrate app 0001_initial
...
CREATE UNIQUE INDEX "app_base_slug_version_a455c5b7_uniq" ON "app_base" ("slug", "version");
...

(expected like the name of your application is "app")

I must roughly agree with user3541631's answer. It depends on the database in general, but all four db engines supported directly by Django are similar. They expect that "nulls are distinct in a UNIQUE column" (see NULL Handling in SQLite Versus Other Database Engines)

I verified your problem with and without null:

class Test(TestCase):
    def test_without_null(self):
        timestamp = datetime.datetime(2017, 8, 25, tzinfo=pytz.UTC)
        book_1 = Book.objects.create(deleted=timestamp, first_form_number='a')
        with self.assertRaises(django.db.utils.IntegrityError):
            Book.objects.create(deleted=timestamp, first_form_number='a')

    def test_with_null(self):
        # this test fails !!! (and a duplicate is created)
        book_1 = Book.objects.create(first_form_number='a')
        with self.assertRaises(django.db.utils.IntegrityError):
            Book.objects.create(first_form_number='a')

A solution is possible for PostgreSQL if you are willing to manually write a migration to create two special partial unique indexes:

CREATE UNIQUE INDEX book_2col_uni_idx ON app_book (first_form_number, deleted)
WHERE deleted IS NOT NULL;

CREATE UNIQUE INDEX book_1col_uni_idx ON app_book (first_form_number)
WHERE deleted IS NULL;

See:

Answer for Create unique constraint with null columns
Django docs Writing database migrations
Django docs migrations.RunSQL(sql)

Does your test case test_without_null raises Integerity Error ? Because mine does. — randy, Aug 26 '17 at 17:50
Yes, test_without_null raised IntegrityError internally because [duplicates are prevented by IntegrityError exception](https://docs.djangoproject.com/en/1.11/ref/models/fields/#unique) and the test passed, while test_with_null fails by "AssertionError: IntegrityError not raised" because duplicates with null can't be prevented by a simple index created by "CREATE UNIQUE INDEX". — hynekcer, Aug 26 '17 at 19:20

score 1 · Answer 2 · answered Aug 25 '17 at 13:17

depending on your database, it is possible that NULL isn't equal to any other NULL.

Therefore the rows you create are not the same, if one of the values is NULL, will be unique only by the non null field, in your case 'first_form_number'.

Also take in consideration that is case sensitive so "char" and "Char" are not the same.

I had a similar situation and I did my own check by overriding the save method on the model.

You check if exist in the database, but also exclude the current instance, in case of updating, not to compare with itself..

if not deleted:
exists = model.objects.exclude(pk=instance.pk).filter(first_form_number__iexact=first_form_number).exists()

I am using postgres. Even if i have non null fields, it still ignores the second field and throw Integrity Error when first field is same but other field has 2 different value. — randy, Aug 26 '17 at 17:48

score -1 · Answer 3 · answered Aug 25 '17 at 15:22

-1

Make sure you actually extend the inherited Meta class, rather than defining your own Meta class (which is ignored by Django):

class Meta(BaseModel.Meta):
        unique_together = (("first_form_number", "deleted"),)

answered Aug 25 '17 at 15:22

dirkgroten

20,112
2
29
42

There is nothing useful that should be inherited from the parent Meta, in this case. – hynekcer Aug 25 '17 at 22:21

unique_together in Django doesn't work

3 Answers3