Creating Partial Indexes with Django 1.7

Question

The documentation for Django 1.7 mentions RunSQL classes can be used to create partial indexes on your tables. I have a table where I want the combination of title, blog & category to be unique. However if category is not provided, the combination of title & blog should still be unique.

class Post(models.Model):
    title = models.CharField(max_length=200)
    blog = models.ForeignKey(Blog)
    category = models.ForeignKey(Category, null=True, blank=True)

I can achieve this constraint with partial indexes (like the SQL shown below). Where do I add this code if I'm using Django 1.7 migrations?

CREATE UNIQUE INDEX idx1 
  ON Post (title, blog_id, category_id) 
  WHERE category_id IS NOT NULL;

CREATE UNIQUE INDEX idx2 
  ON Post (title, blog_id)
  WHERE category_id IS NULL;

Kevin Christopher Henry · Accepted Answer · 2020-04-25T08:46:04.217

Django 2.2 and later

As of version 2.2 Django supports declarative partial unique indexes on databases that support them (PostgreSQL and SQLite). So you could do something like:

from django.db.models import Model, Q, UniqueConstraint

class Post(Model):
    ...
    class Meta:
        constraints = [
            UniqueConstraint(
                fields=["title", "blog", "category"],
                name="idx1",
                condition=Q(category__isnull=False)),
            UniqueConstraint(
                fields=["title", "blog"], 
                name="idx2",                    
                condition=Q(category__isnull=True)),
        ]

Django 2.1 and earlier

In older versions you need to do this with migrations. First create a new, empty migration file:

python manage.py makemigrations --empty yourappname

Then, for each index add an appropriate RunSQL line:

operations = [
    migrations.RunSQL("CREATE UNIQUE INDEX..."),
    migrations.RunSQL("CREATE UNIQUE INDEX..."),
]

Finally, run migrate.

Thanks. I couldn't find this in the docs. When & how do I use the `sql` & `reverse_sql` params? — user4150760, Oct 17 '14 at 04:04
@user4150760: The `sql` parameter is the string creating the index, as you gave it above. If you want to be able to roll back the migration then provide the `reverse_sql` argument as well. In this case that would be the equivalent `DROP INDEX ...` command. The `state_operations` argument doesn't apply here. — Kevin Christopher Henry, Oct 17 '14 at 05:33

score -1 · Answer 2 · answered Nov 27 '15 at 10:52

-1

You could just provide an unique_together like so:

class Post(models.Model):
    title = models.CharField(max_length=200)
    blog = models.ForeignKey(Blog)
    category = models.ForeignKey(Category, null=True, blank=True)

class Meta:
    unique_together = ("title", "blog", "category")

NULLs for category will work how you want in that if not set then title/blog has to be unique.

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

answered Nov 27 '15 at 10:52

dalore

5,594
1
36
38

The documentation you linked neither supports nor refutes what you said. Certain (all?) databases never equate null values (i.e. a column of null values satisfies a unique constraint on it), thus if any of title, blog, or category is null, the unique_together clause will be trivially satisfied. – DylanYoung Nov 02 '16 at 16:03
2

unique_together is not for conditional indexes, just multi-key indexes – Dj Mamana Apr 19 '17 at 13:57

Creating Partial Indexes with Django 1.7

2 Answers2

Linked