Composite unique constraint

Composite unique constraint are very common in real database entities. In Django models, those constraints are expressed by the unique_together property of the Meta class.
Lets say that we have an Django model class:
class ModelA(models.Model):
  property1 = models.CharField(max_length=255, null=True)
  property2 = models.IntegerField()

  class Meta(object):
    unique_together = ('property1', 'property2')

It looks fine, now lets say that we want to save something in this model:
m3 = ModelA(property1=None, property2=1)
m3.save()
m4 = ModelA(property1=None, property2=1)
m4.save()

What we could expect is that the second save will fail because of the composite unique constraint. What actually will happen is that there will be two rows in db with property1 set to NULL and property2 set to 1. This is because the SQL standard define that NULL is not a value but the state so in that case the NULL value is not treat as unique accros the rows.  
To solve the issue we have to take care about checking the uniqueness on the Django model level. The save method of the models has to be overwrite. Below is the example how to define abstract class which can be used instead of Django models.Model class.

class Model(models.Model):
  class Meta(object):
    abstract = True

  def _check_uniqueness(self):
    nullable_field_names = set([x.name for x in self._meta._fields() if x.null])

    for unique_together in self._meta.unique_together:
      if set(unique_together).intersection(nullable_field_names):
        values = [(x, getattr(self, x)) for x in unique_together]
        queryset = {}
        for field, value in values:
          if value is None:
            queryset['%s__isnull' % field] = True
          else:
            queryset[field] = value
        exists_query = self.__class__.objects.filter(**queryset)
        if self.pk is not None:
          exists_query = exists_query.exclude(id=self.id)
        if exists_query.exists():
          return unique_together
    return None

    def save(self, *args, **kwargs):
      unique_together = self._check_uniqueness()
      if unique_together is not None:
        raise db.IntegrityError('columns %s are not unique' % ', '.join(unique_together))
      super(Model, self).save(*args, **kwargs)

No comments: