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). ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]