[Fixed]-Django unique together constraint failure?

26👍

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 πŸ™‚
👤Yossi

1👍

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.

👤Toff'

0👍

Just manually create secondary index on slug field, but only for NULL values in foreign_key_id:

CREATE INDEX table_name_unique_null_foreign_key
  ON table_name (slug) WHERE foreign_key_id is NULL

Please note, that Django does not support this, so without custom form/model validation you will get pure IntegrityError / 500.

Possible duplicate of Create unique constraint with null columns

0👍

As hobbyte mentioned, “In Postgresql NULL isn’t equal to any other NULL. Therefore the rows you create are not the same (from Postgres’ perspective).”

Another possible way to address this challenge is to add custom validation at the view level in the form_valid method.

In views.py:

def form_valid(self, form): 

  --OTHER VALIDATION AND FIELD VALUE ASSIGNMENT LOGIC--

  if ModelForm.objects.filter(slug=slug,foreign_key=foreign_key:   
    form.add_error('field',
      forms.ValidationError( _("Validation error message that shows up in your form. "), 
      code='duplicate_row', )) 
    return self.form_invalid(form)

This approach is helpful if you are using class based views, especially if you are automatically assigning values to fields that you want to hide from the user.

Pros:

  • You don’t have to create dummy default values in the database
  • You can still use update forms (see Toff’s answer)

Cons:
– This doesn’t protect against duplicate rows created directly at the database level.
– If you use Django’s admin backend to create new MyModel objects, you’ll need to add this same validation logic to your admin form.

Leave a comment