StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUnique fields that allow nulls in Django
text
Body
copied!<p>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 <code>null</code>, but if it is not <code>null</code> the values must be unique.</p> <p>Here is my model:</p> <pre><code>class Foo(models.Model): name = models.CharField(max_length=40) bar = models.CharField(max_length=40, unique=True, blank=True, null=True, default=None) </code></pre> <p>And here is the corresponding SQL for the table:</p> <pre><code>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) ) </code></pre> <p>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."</p> <p>However when I insert into database (PostgreSQL):</p> <pre><code>insert into appl_foo ("name", bar) values ('test1', null) insert into appl_foo ("name", bar) values ('test2', null) </code></pre> <p>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?</p> <p><b>EDIT</b></p> <p>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 <em>bar</em>, it didn't give any errors, however seamed like it had no effect at all.</p> <p>So far, I've removed the unique specifier from the <em>bar</em> property and handling the <em>bar</em> uniqueness in the application, however still looking for a more elegant solution. Any recommendations?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload