>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
aboutindexing 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
createthe indexes manually. If anyone sees any problems with this, I'd love some input. Also, if anyone at the end of
thisemail 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
nulland NEW.c is null) or c = NEW.c);
IF FOUND THEN RAISE EXCEPTION ''Invalid Row!''; END IF; RETURN NEW;END;
'LANGUAGE 'plpgsql';
