Django Unique Field Throws Error for None Value

Question

I'm getting a duplicate key value violates unique constraint "users_user_email_key" DETAIL: Key (email)=(None) already exists. error when trying to create a second user that doesn't have an email.

Email field definition:

email = models.EmailField(verbose_name='email address', max_length=255, unique=True, null = True, blank = True)

From the form creating the user:

def clean_email(self):
    email = self.cleaned_data.get('email')
    if email:
        if email == "":
            return None
        else:
            return email
    else:
        return None

What am I doing wrong here? All input is appreciated, thanks!

remove unique=True and check for duplicates on `clean_mail` – Meska May 25 '17 at 22:20 — Meska, May 25 '17 at 22:20

score 0 · Answer 1 · answered May 25 '17 at 22:22

That's a pretty good message. You have a field that has a unique constraint on it, and you are allowing it to be null able. If you have one null value email, you can't have another, as that would violate your unique constraint.

You should think about the design here. If you allow users to be in your system without an email address, you need to remove the unique constraint. Another option would be to create a unique composite key, combining email with a non-null field, but one that would be unique with email (or more fields, if required). You're starting to muddy the business requirements, possibly, by doing that, however. If you're not concerned with data integrity or validity, you can also insert a random ID or sequence.next_val as an email address, but at that point you're getting really dirty.

Simplest design and strongest integrity here would be to require not only an email address, but confirm that it is a valid email address or at least email address format. You'd keep your unique constraint but not allow null values and add some front and back end support for the value provided.

That makes sense but this is a unique situation that requires some additional flexibility. I have implemented similar solutions without issue which is why I'm wondering what's wrong here. See https://stackoverflow.com/questions/454436/unique-fields-that-allow-nulls-in-django — apardes, May 25 '17 at 23:33
It appears as if sqlite doesn't enforce uniqueness on NULL values, which goes against SQL Standard (see #26 here: https://sqlite.org/faq.html#q26). My guess is that a python None is not equivalent to a sqlite NULL, and therefore is being checked for uniqueness. — R Scott, May 26 '17 at 12:02

score 0 · Answer 2 · answered Dec 16 '19 at 16:05

If you move the empty string to None conversion into the form's save method, it will work as you describe. Ex:

def save(self, commit=True):
    if self.instance.email == '':
        self.instance.email = None
    return super().save(commit)

This is the cleanest approach I have found to converting char fields that are unique (if present) but optional.

Django Unique Field Throws Error for None Value

2 Answers2