[Django]-Django unique, null and blank CharField giving 'already exists' error on Admin page

None of the answers clearly describe the root of the problem.

Normally in the db you can make a field null=True, unique=True and it will work… because NULL != NULL. So each blank value is still considered unique.

But unfortunately for CharFields Django will save an empty string "" (because when you submit a form everything comes into Django as strings, and you may have really wanted to save an empty string "" – Django doesn’t know if it should convert to None)

This basically means you shouldn’t use CharField(unique=True, null=True, blank=True) in Django. As others have noted you probably have to give up the db-level unique constraint and do your own unique checks in the model.

For further reference, see here: https://code.djangoproject.com/ticket/4136
(unfortunately no good solution decided at time of writing)

NOTE:
As pointed out by @alasdair in a comment, that bug has now been fixed – since Django 1.11 you can use CharField(unique=True, null=True, blank=True) without having to manually convert blank values to None

This is an old one but I had a similar issue just now and though I would provide an alternative solution.

I am in a situation where I need to be able to have a CharField with null=True, blank=True and unique=True. If I submit an empty string in the admin panel it will not submit because the blank string is not unique.

To fix this, I override the ‘clean’ function in the ModelForm, and in there I check if it’s a blank string and return the result accordinly.

class MyModelChangeForm(forms.ModelForm):

    class Meta:
        model = models.MyModel
        fields = ['email', 'name', 'something_unique_or_null',]

    def clean_something_unique_or_null(self):
        if self.cleaned_data['something_unique_or_null'] == "":
            return None
        else:
            return self.cleaned_data['something_unique_or_null']

This fixed the problem for me without having to sacrifice the unique attribute on the model field.

Hope this helps.

EDIT:
You need to change where I have put “something_unique_or_null” to the name of your field. For example “clean_twitter_id”.

In Django 1.11, form CharFields will have an empty_value argument, which allows you to use None if the field is empty.

Model forms, including the Django admin, will automatically set empty_value=None if the model’s CharField has null=True.

Therefore, you will be able to use null=True, blank=True and unique=True together in your model CharField without the unique constraint causing problems.

It’s important to solve this at the model level, not at the form level, since data can enter through APIs, through import scripts, from the shell, etc. The downside of setting null=True on a CharField is that the column could end up with both empty strings and NULLs, which is slightly ambiguous but not generally a problem in my experience. If you’re willing to live with that ambiguity, here’s how to do it in a few steps:

1) Set null=True, blank=True on the field and migrate in the change.

2) Massage your data so that all existing empty strings are changed to NULLs:

items = Foo.objects.all()
for item in items:
  if not item.somefield:
    item.somefield = None
    item.save()

3) Add a custom save() method to your model:

def save(self, *args, **kwargs):
    # Empty strings are not unique, but we can save multiple NULLs
    if not self.somefield:
        self.somefield = None

    super().save(*args, **kwargs)  # Python3-style super()

4) Set unique=True on the field and migrate that in as well.

Now you’ll be able to store somefield as empty or as a unique value whether you’re using the admin or any other data entry method.

If you prefer not to have several migrations, here’s an example of how to do it in a single migration:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals
from django.db import migrations, models

def set_nulls(apps, schema_editor):
    Event = apps.get_model("events", "Event")
    events = Event.objects.all()
    for e in events:
        if not e.wdid:
            e.wdid = None
            e.save()


class Migration(migrations.Migration):

    dependencies = [
        ('events', '0008_something'),
    ]

    operations = [
        migrations.AlterField(
            model_name='event',
            name='wdid',
            field=models.CharField(blank=True, max_length=32, null=True),
        ),
        migrations.RunPython(set_nulls),
        migrations.AlterField(
            model_name='event',
            name='wdid',
            field=models.CharField(blank=True, max_length=32, null=True, unique=True),
        ),
    ]

The root of the problem is that Django persist the empty value as empty-string, and not as null. To fix this, you can subclass CharField as follows:

class CharNullField(models.CharField):
    description = "CharField that stores NULL"
    
    def get_db_prep_value(self, value, connection=None, prepared=False):
        value = super(CharNullField, self).get_db_prep_value(value, connection, prepared)
        if value=="":
            return None
        else:
            return value

So get_db_prep_value will make it sure that null gets persisted.

You have to provide default=None in the field

facebook_id = models.CharField(max_length=225,unique=True, null=True,blank=True,default=None)

This worked for me . I used it in phone number. So it can be unique if entered and not mandatory.

you are accepting blank values and expect them to be unique. this means there can only be ONE entry with a blank twitter_id

you can

• either remove the unique contraint
• remove the blank =True
• give a default value for the field ( but default needs to be unique)