unique_together constraint on multiple columns with NULL values

Question

I have a database of automotive parts which includes branded & unbranded items. If the brand name is unavailable, the full_name is simply Part.name, else it is Brand.name + Part.name.

How can I define a unique_together constraint to ensure uniqueness of full_name at database level? The current constraint allows multiple parts with same name if brand name is missing (i.e. brand_id ForeignKey is NULL)

class Brand(models.Model):
    name = models.CharField(max_length=100)

class Part(models.Model);
    name = models.CharField(max_length=100)
    brand = models.ForeignKey(Brand, null=True, blank=True)

    def get_full_name(self):
        if self.brand_id:
            full_name = '%s %s'.format(self.brand.name, self.name)
        else:
            full_name = self.name
        return full_name

    class Meta:
        unique_together = (('name', 'brand'),)

Using : Django 1.6 + PostgreSQL 9.3

P.S. There's an excellent suggestion here to achieve this with SQL. I'm looking for a solution using Django ORM. https://stackoverflow.com/a/8289253/781695

With django2.2 you can use now the UniqueConstraint and its condition-parameter: https://docs.djangoproject.com/en/2.2/ref/models/constraints/#condition — ralfzen, Jul 03 '19 at 12:26

Greg · Accepted Answer · 2016-03-31T21:35:43.890

There's no way to tell django to add that index I'm afraid, unique_together only handles simple indexes. However, you can validate at the model layer using the clean method, which you should call before saving your model instance (ModelForm instances call it for you):

from django.core.validators import ValidationError

class Part(models.Model):

    def clean(self):
        duplicates = Part.objects.exclude(pk=self.pk).filter(name=self.name)
        if self.brand:
            duplicates = duplicates.filter(brand=self.brand)
        if duplicates.count():
            raise ValidationError(u'Duplicate part/brand')

Ideally you should manually add the index to your Postgres database, using a RunSQL operation in your migrations.

Just be aware that this is called by ModelForm, but **not** when `save` is called — Rebs, Dec 07 '15 at 00:20

unique_together constraint on multiple columns with NULL values

1 Answers1