[Django]-Unique_together in Django doesn't work

by

5👍

✅

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:

👤hynekcer

1👍

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()
👤user3541631

-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"),)
👤dirkgroten

Leave a comment