CODEWITHSUNDEEP
djangopostgresqldjango-models
user
user

Reputation: 18549

unique_together constraint on multiple columns with NULL values

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

Upvotes: 3

Views: 3463

Answers (1)

Greg
Greg

Reputation: 10352

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.

Upvotes: 5

Related Questions