Compound unique constraint with a nullable column

[Guido Winkelmann]

Hi,

How can I go about having a cross-platform compatible compound unique constraint over two columns where there can be only one record with NULL in one column and a given value in the other?

I want something like this:

UniqueConstraint("parent_id", "name")

except "parent_id" is nullable, and I want to disallow creating multiple records with parent_id=NULL and the same name. The problem here is that some database management systems, PostgreSQL for example, will treat all NULL values as not-equal inside a unique constraint, which makes sense for a single column unique, but not really for a multi-column one. This will allow multiple records with the same name and parent_id=NULL again.

In PostgreSQL, apparently I would need to create a unique index in addition to the constraint. I would really like to solve this without writing database-specific code, though.

Does anybody have a good solution for that?

  Guido

[Michael Bayer]

I’ve had an issue in this area before and decided just to forego it; I was targeting PG and SQL Server, but I don’t remember exactly which NULL behavior I was looking for.   per the SQL standard I think PG’s behavior is correct. NULL means, “unknown”, so two NULL values are never equivalent.

In any case this is more of a stack overflow question, I can show you how to run different DDL on different backends* but you need to figure out what specific uniques/indexes you want to make in each case.

* once you know your DDL just use a combination of events, UniqueConstraint/Index and/or DDL(“exact sql”) to produce what you want, I’d roll it into a single function that you can reuse, docs at http://docs.sqlalchemy.org/en/rel_0_9/core/ddl.html#controlling-ddl-sequences.

[Guido Winkelmann]

Am Montag, 17. November 2014 16:25:54 UTC+1 schrieb Michael Bayer:

On Nov 17, 2014, at 7:37 AM, Guido Winkelmann <gu...@ambient-entertainment.de> wrote:

Hi,

How can I go about having a cross-platform compatible compound unique constraint over two columns where there can be only one record with NULL in one column and a given value in the other?

I want something like this:

UniqueConstraint("parent_id", "name")

except "parent_id" is nullable, and I want to disallow creating multiple records with parent_id=NULL and the same name. The problem here is that some database management systems, PostgreSQL for example, will treat all NULL values as not-equal inside a unique constraint, which makes sense for a single column unique, but not really for a multi-column one. This will allow multiple records with the same name and parent_id=NULL again.

In PostgreSQL, apparently I would need to create a unique index in addition to the constraint. I would really like to solve this without writing database-specific code, though.

Does anybody have a good solution for that?

I’ve had an issue in this area before and decided just to forego it; I was targeting PG and SQL Server, but I don’t remember exactly which NULL behavior I was looking for.   per the SQL standard I think PG’s behavior is correct. NULL means, “unknown”, so two NULL values are never equivalent.

 If I wanted to add a manual check in Python for that, how would I go about that?

Could I register an event handler for the "before_insert" event for this model?

What would the event handler have to do to refuse an insert? Throw an exception?

   Guido W.

[Michael Bayer]

you could use a before_insert event, though that’s expensive as you’d need to query the whole table to check for dupes for every row.

I think organizing the kinds of indexes/constraints that apply to different backends would be the best approach here.

[Guido Winkelmann]

On Monday 17 November 2014 14:44:02 Michael Bayer wrote:
>> On Nov 17, 2014, at 1:55 PM, Guido Winkelmann
>> <gu...@ambient-entertainment.de> wrote:>
>> Am Montag, 17. November 2014 16:25:54 UTC+1 schrieb Michael Bayer:
>>> On Nov 17, 2014, at 7:37 AM, Guido Winkelmann

That's not a big deal in this case. This particular table is never going to be
very big or very busy. It probably won't ever have more than fifty records.

>I think organizing the kinds of indexes/constraints that apply to different
>backends would be the best approach here.

Backend specific code is exactly what I was trying to avoid here. I might come
back to that if similar problems come up in more performance-sensitive parts,
but for now, I think I will just stick with the before_insert event handler:

https://github.com/pyfarm/pyfarm-master/pull/271/files

Regards,

Guido W.

[Jonathan Vanasco]

These might be backend specific and not supported in other databases... but there are some popular postgres tricks to deal with this:

* in the constraint, coalesce NULL into an empty string (or other value).

* use multiple indexes

both are actually discussed in the comments to this question:  http://stackoverflow.com/questions/8289100/create-unique-constraint-with-null-columns