Unique fields that allow nulls in Django

Question

I have model Foo which has field bar. The bar field should be unque, 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 unque.

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?

Answer 1

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)

Answer 2

Hello,

did you find the solution? I have the same need. Thanks in advance.

Jano

Answer 3

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):
        bar = self.cleaned_data['bar']
        if bar == '':
            bar = None
        return bar

class FooAdmin(admin.ModelAdmin):
    form = FooForm

Answer 4

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 access 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 ''"
    def to_python(self, value):  #this is the value right out of the db, or an instance
       if isinstance(value, models.CharField): #if an instance, just return the instance
              return value 
       if value==None:   #if the db has a NULL (==None in Python)
              return ""  #convert it into the Django-friendly '' string
       else:
              return value #otherwise, return just the value
    def get_db_prep_value(self, value):  #catches value right before sending to db
       if value=="":     #if Django tries to save '' string, send the db None (NULL)
            return None
       else:
            return value #otherwise, just pass the 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.

Answer 5

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()    

Answer 6

The quick fix is to do :

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

    if not self.bar:
        self.bar = None

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