Re: multi-column unique constraints with nullable columns

>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?

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

IF FOUND THEN
RAISE EXCEPTION ''Invalid Row!'';
END IF;

RETURN NEW;
END;
'LANGUAGE 'plpgsql';