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)
