Re: multi-column unique constraints with nullable columns

On Sat, 30 Apr 2005, Tornroth, Phill wrote:

> >I believe you can add partial unique indexes to cover the case where a
> >column is null, but if you have multiple nullable columns you need to
> >worry about you end up with a bunch of indexes.
>
> Hmmm. I hadn't thought of that, thanks. Yes, the indexes would be
> unnessecary though. Speaking of, should be concerned about indexing
> NULLABLE columns?

I believe you're at least safe with btree indexes.

The advantage of using the unique partial indexes is that it'll handle
concurrent inserts without you having to worry about it.

> > Also, is this in compliance with SQL92? I'm surprised constraints work
> > this way.
> he
> I read that. I think you're right, it sounds like any comparison
> containing NULL at all will fail.
>
>
> I wrote the following procedure, which seems to do the trick. I guess my
> plan would be to write a bunch of these, and create the indexes
> manually. If anyone sees any problems with this, I'd love some input.
> Also, if anyone at the end of this email is a DBA/Consultant type and
> works in the San Diego area... Definitely let me know :)
>
> CREATE FUNCTION check_mytable_natkey() RETURNS opaque AS '
> DECLARE
> conflictingpk integer;
> BEGIN
>
> SELECT INTO conflictingpk a
> FROM mytable
> WHERE ((b is null and NEW.b is null) or b = NEW.b)
> AND ((c is null and NEW.c is null) or c = NEW.c);

Unfortunately, I don't think this will work if two sessions come in at the
same time trying to insert the same values since they won't see each
other's changes. I think it also will raise an error if the existing row
has been deleted by a not yet committed transaction while our current
implementation of unique constraints would wait to see if the transaction
commits.

On a side note, I believe (x is null and y is null) or x=y can be written
a little more succintly with NOT(x IS DISTINCT FROM y).