Unique fields that allow nulls in Django

Question

I have model Foo which has field bar. The bar field should be unique, but allow nulls in it, meaning I want to allow more than one record if bar field is null, but if it is not null the values must be unique.

Here is my model:

class Foo(models.Model):
    name = models.CharField(max_length=40)
    bar = models.CharField(max_length=40, unique=True, blank=True, null=True, default=None)

And here is the corresponding SQL for the table:

CREATE TABLE appl_foo
(
    id serial NOT NULL,
     "name" character varying(40) NOT NULL,
    bar character varying(40),
    CONSTRAINT appl_foo_pkey PRIMARY KEY (id),
    CONSTRAINT appl_foo_bar_key UNIQUE (bar)
)   

When using admin interface to create more than 1 foo objects where bar is null it gives me an error: "Foo with this Bar already exists."

However when I insert into database (PostgreSQL):

insert into appl_foo ("name", bar) values ('test1', null)
insert into appl_foo ("name", bar) values ('test2', null)

This works, just fine, it allows me to insert more than 1 record with bar being null, so the database allows me to do what I want, it's just something wrong with the Django model. Any ideas?

EDIT

The portability of the solution as far as DB is not an issue, we are happy with Postgres. I've tried setting unique to a callable, which was my function returning True/False for specific values of bar, it didn't give any errors, however seamed like it had no effect at all.

So far, I've removed the unique specifier from the bar property and handling the bar uniqueness in the application, however still looking for a more elegant solution. Any recommendations?

Westbound

I can not comment yet so here a little addition to mightyhal: Since Django 1.4 you would need def get_db_prep_value(self, value, connection, prepared=False) as method call. Check groups.google.com/d/msg/django-users/Z_AXgg2GCqs/zKEsfu33OZMJ for more informations. Following method works for me, too: def get_prep_value(self, value): if value=="": #if Django tries to save '' string, send the db None (NULL) return None else: return value #otherwise, just pass the value – Sitzmark

I opened a Django ticket for this. Add your support. code.djangoproject.com/ticket/30210#ticket – Romonaromonda

Answer 1

Django has not considered NULL to be equal to NULL for the purpose of uniqueness checks since ticket #9039 was fixed, see:

http://code.djangoproject.com/ticket/9039

The issue here is that the normalized "blank" value for a form CharField is an empty string, not None. So if you leave the field blank, you get an empty string, not NULL, stored in the DB. Empty strings are equal to empty strings for uniqueness checks, under both Django and database rules.

You can force the admin interface to store NULL for an empty string by providing your own customized model form for Foo with a clean_bar method that turns the empty string into None:

class FooForm(forms.ModelForm):
    class Meta:
        model = Foo

    def clean_bar(self):
        return self.cleaned_data['bar'] or None

class FooAdmin(admin.ModelAdmin):
    form = FooForm

Wie

If bar is blank then replace it with None in pre_save method. Code will be more DRY I suppose. – Salvatore

This answer only helps for form-based data input, but does nothing to actually protect data integrity. Data may be entered via import scripts, from the shell, through an API or any other means. Much better to override the save() method than to make custom cases for every form that might touch the data. – Caltrop

Django 1.9+ requires a fields or exclude attribute in ModelForm instances. You can work around this by omitting the Meta inner class from the ModelForm for use in admin. Reference: docs.djangoproject.com/en/1.10/ref/contrib/admin/… – Metabolize

This answer is suboptimal, you can see the answer from @tBuLi for why. – Caseworm

Answer 2

** edit 11/30/2015: In python 3, the module-global __metaclass__ variable is no longer supported. Additionaly, as of Django 1.10 the SubfieldBase class was deprecated:

from the docs:

django.db.models.fields.subclassing.SubfieldBase has been deprecated and will be removed in Django 1.10. Historically, it was used to handle fields where type conversion was needed when loading from the database, but it was not used in .values() calls or in aggregates. It has been replaced with from_db_value(). Note that the new approach does not call the to_python() method on assignment as was the case with SubfieldBase.

Therefore, as suggested by the from_db_value() documentation and this example, this solution must be changed to:

class CharNullField(models.CharField):

    """
    Subclass of the CharField that allows empty strings to be stored as NULL.
    """

    description = "CharField that stores NULL but returns ''."

    def from_db_value(self, value, expression, connection, contex):
        """
        Gets value right out of the db and changes it if its ``None``.
        """
        if value is None:
            return ''
        else:
            return value


    def to_python(self, value):
        """
        Gets value right out of the db or an instance, and changes it if its ``None``.
        """
        if isinstance(value, models.CharField):
            # If an instance, just return the instance.
            return value
        if value is None:
            # If db has NULL, convert it to ''.
            return ''

        # Otherwise, just return the value.
        return value

    def get_prep_value(self, value):
        """
        Catches value right before sending to db.
        """
        if value == '':
            # If Django tries to save an empty string, send the db None (NULL).
            return None
        else:
            # Otherwise, just pass the value.
            return value

---

I think a better way than overriding the cleaned_data in the admin would be to subclass the charfield - this way no matter what form accesses the field, it will "just work." You can catch the '' just before it is sent to the database, and catch the NULL just after it comes out of the database, and the rest of Django won't know/care. A quick and dirty example:

from django.db import models


class CharNullField(models.CharField):  # subclass the CharField
    description = "CharField that stores NULL but returns ''"
    __metaclass__ = models.SubfieldBase  # this ensures to_python will be called

    def to_python(self, value):
        # this is the value right out of the db, or an instance
        # if an instance, just return the instance
        if isinstance(value, models.CharField):
            return value 
        if value is None:  # if the db has a NULL (None in Python)
            return ''      # convert it into an empty string
        else:
            return value   # otherwise, just return the value

    def get_prep_value(self, value):  # catches value right before sending to db
        if value == '':   
            # if Django tries to save an empty string, send the db None (NULL)
            return None
        else:
            # otherwise, just pass the value
            return value  

For my project, I dumped this into an extras.py file that lives in the root of my site, then I can just from mysite.extras import CharNullField in my app's models.py file. The field acts just like a CharField - just remember to set blank=True, null=True when declaring the field, or otherwise Django will throw a validation error (field required) or create a db column that doesn't accept NULL.

Snip

in get_prep_value, you should strip the value, in case the value has several spaces. – Secure

Updated answer here works well in 2016 with Django 1.10 and using EmailField. – Pinkston

If you are updating a CharField to be a CharNullField, you'll need to do it in three steps. First, add null=True to the field, and migrate that. Then, do a data migration to update any blank values so they are nulls. Finally, convert the field to CharNullField. If you convert the field before you do the data migration, your data migration won't do anything. – Ankylosis

Note that in the updated solution, from_db_value() should not have that extra contex parameter. It should be def from_db_value(self, value, expression, connection): – Betthezul

The comment from @PhilGyford applies as of 2.0. – Clintonclintonia

Answer 3

You can add UniqueConstraint with condition of nullable_field=null and not to include this field in fields list. If you need also constraint with nullable_field wich value is not null, you can add additional one.

Note: UniqueConstraint was added since django 2.2

class Foo(models.Model):
    name = models.CharField(max_length=40)
    bar = models.CharField(max_length=40, unique=True, blank=True, null=True, default=None)
    
    class Meta:
        constraints = [
            # For bar == null only
            models.UniqueConstraint(fields=['name'], name='unique__name__when__bar__null',
                                    condition=Q(bar__isnull=True)),
            # For bar != null only
            models.UniqueConstraint(fields=['name', 'bar'], name='unique__name__when__bar__not_null')
        ]

Diaconicum

That works! but i get an IntegrityError exception instead of the form validation error. How do you handle that? Catch it and raise ValidationError in create+update views? – Duodenum

God, why did I have to scroll all the way down just for this? Thanks for saving me. – Ninth

If bar is null, you would like to let insert again same name. I think this prevent for doing that. i.e. you would like to be able to insert ("name", null) twice. – Cormack

Answer 4

Because I am new to stackoverflow I am not yet allowed to reply to answers, but I would like to point out that from a philosophical point of view, I can't agree with the most popular answer tot this question. (by Karen Tracey)

The OP requires his bar field to be unique if it has a value, and null otherwise. Then it must be that the model itself makes sure this is the case. It cannot be left to external code to check this, because that would mean it can be bypassed. (Or you can forget to check it if you write a new view in the future)

Therefore, to keep your code truly OOP, you must use an internal method of your Foo model. Modifying the save() method or the field are good options, but using a form to do this most certainly isn't.

Personally I prefer using the CharNullField suggested, for portability to models I might define in the future.

Stein

Answer 5

The quick fix is to do :

def save(self, *args, **kwargs):

    if not self.bar:
        self.bar = None

    super(Foo, self).save(*args, **kwargs)

Colobus

be aware that using MyModel.objects.bulk_create() would bypass this method. – Turning

Does this method gets called when we save from admin panel? I tried but it doesnt. – Striated

@Kishan django-admin panel will skip these hooks unfortunately – Senn

@e-satis your logic is sound so I implemented this, but the error is still a problem. Im getting told null is a duplicate. – Senn

Answer 6

This is fixed now that https://code.djangoproject.com/ticket/4136 is resolved. In Django 1.11+ you can use models.CharField(unique=True, null=True, blank=True) without having to manually convert blank values to None.

Slovene

This worked for me on Django 3.1 with CharField but not with TextField - the constraint failed as an empty string was still being passed by admin form. – Simmers

doesnt work for EmailField on django Django 3.1 – Mcmillan

@Mcmillan Just tried it with Django 3.1.5 and it works with EmailField. – Moonmoonbeam

What about foreign keys? For example 2 fields that are FK, but one is nullable. (In DB it is a multi-column unique index – Cormack

Answer 7

Another possible solution

class Foo(models.Model):
    value = models.CharField(max_length=255, unique=True)

class Bar(models.Model):
    foo = models.OneToOneField(Foo, null=True)

Furl

This is not a good solution since you`re creating an unnecessary relation. – Dayna

Answer 8

If you have a model MyModel and want my_field to be Null or unique, you can override model's save method:

class MyModel(models.Model):
    my_field = models.TextField(unique=True, default=None, null=True, blank=True) 

    def save(self, **kwargs):
        self.my_field = self.my_field or None
        super().save(**kwargs)

This way, the field cannot be blank will only be non-blank or null. nulls do not contradict uniqueness

Stoltzfus

Answer 9

I recently had the same requirement. Instead of subclassing different fields, I chose to override the save() metod on my model (named 'MyModel' below) as follows:

def save(self):
        """overriding save method so that we can save Null to database, instead of empty string (project requirement)"""
        # get a list of all model fields (i.e. self._meta.fields)...
        emptystringfields = [ field for field in self._meta.fields \
                # ...that are of type CharField or Textfield...
                if ((type(field) == django.db.models.fields.CharField) or (type(field) == django.db.models.fields.TextField)) \
                # ...and that contain the empty string
                and (getattr(self, field.name) == "") ]
        # set each of these fields to None (which tells Django to save Null)
        for field in emptystringfields:
            setattr(self, field.name, None)
        # call the super.save() method
        super(MyModel, self).save()    

Comnenus

Answer 10

For better or worse, Django considers NULL to be equivalent to NULL for purposes of uniqueness checks. There's really no way around it short of writing your own implementation of the uniqueness check which considers NULL to be unique no matter how many times it occurs in a table.

(and keep in mind that some DB solutions take the same view of NULL, so code relying on one DB's ideas about NULL may not be portable to others)

Delmadelmar

This is not the correct answer. See this answer for explanation. – Dolores

Agreed this is not correct. I just tested IntegerField(blank=True, null=True, unique=True) in Django 1.4 and it allows multiple rows with null values. – Autogenesis