Django unique together constraint failure?

Question

Using Django 1.5.1. Python 2.7.3.

I wanted to do a unique together constraint with a foreign key field and a slug field. So in my model meta, I did

foreign_key = models.ForeignKey("self", null=True, default=None)
slug = models.SlugField(max_length=40, unique=False)

class Meta:
    unique_together = ("foreign_key", "slug")

I even checked the table description in Postgres (9.1) and the constraint was put into the database table.

-- something like
"table_name_foreign_key_id_slug_key" UNIQUE CONSTRAINT, btree (foreign_key_id, slug)

However, I could still save into the database table a foreign_key of None/null and duplicate strings.

For example,

I could input and save

# model objects with slug="python" three times; all three foreign_key(s) 
# are None/null because that is their default value
MO(slug="python").save()
MO(slug="python").save()
MO(slug="python").save()

So after using unique_together, why can I still input three of the same valued rows?

I'm just guessing right now that it might have to do with the default value of None for the foreign_key field, because before the unique_together, when I just had unique=True on slug, everything worked fine. So if that is the case, what default value should I have that indicates a null value, but also maintains the unique constraint?

Answer 1

In Postgresql NULL isn't equal to any other NULL. Therefore the rows you create are not the same (from Postgres' perspective).

Update

You have a few ways to deal with it:

• Forbid the Null value for foreign key and use some default value
• Override the save method of your model to check that no such row exists
• Change SQL standard :)

Answer 2

Add a clean method to your model, so you can edit an existing row.

def clean(self):
    queryset = MO.objects.exclude(id=self.id).filter(slug=self.slug)
    if self.foreign_key is None:
        if queryset.exists():
            raise ValidationError("A row already exists with this slug and no key")
    else:
        if queryset.filter(foreign_key=self.foreign_key).exists():
            raise ValidationError("This row already exists")

Beware, clean (or full_clean) isn't called by the default save method.

NB: if you put this code in the save method, update forms (like in the admin) won't work: you will have a traceback error due to the ValidationError exception.