>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';
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
