Thread: multi-column unique constraints with nullable columns
I have many tables who's natural key includes a nullable column. In this cases it's a soft-delete or 'deprecated' date time.I'd like to add a table constraint enforcing this constraint without writing a custom procedure, but I've found thatpostgres treats NULLs very consistently with respect to the NULL != NULL behavior. As a result, when I define a constrainton the last two columns in these insert statements... they both succeed. insert into mytable values (1,300, null); insert into mytable values (1,300, null); This is frustrating, and while there may be someone who actually wants constraints to work this way... I can't understandwhy. Now, I understand that the best way to solve my problem would be to use only non-nullable columns for my natural keys. Iactually plan to do that, and use a very high value for my 'undeprecated' date to solve most of my problems related to this.However, I can't release that version of software carelessly and I need to tighten up customer databases in the meantime. Is there a way to get the behavior I want? Also, is this in compliance with SQL92? I'm surprised constraints work this way. Thank you, Phill
On Fri, 29 Apr 2005, Tornroth, Phill wrote: > I have many tables who's natural key includes a nullable column. In this > cases it's a soft-delete or 'deprecated' date time. I'd like to add a > table constraint enforcing this constraint without writing a custom > procedure, but I've found that postgres treats NULLs very consistently > with respect to the NULL != NULL behavior. As a result, when I define a > constraint on the last two columns in these insert statements... they > both succeed. > > insert into mytable values (1,300, null); > insert into mytable values (1,300, null); > > This is frustrating, and while there may be someone who actually wants > constraints to work this way... I can't understand why. > > Now, I understand that the best way to solve my problem would be to use > only non-nullable columns for my natural keys. I actually plan to do > that, and use a very high value for my 'undeprecated' date to solve most > of my problems related to this. However, I can't release that version of > software carelessly and I need to tighten up customer databases in the > meantime. > > Is there a way to get the behavior I want? 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. > Also, is this in compliance with SQL92? I'm surprised constraints work > this way. As far as we can tell, this is explicitly what SQL wants to happen. The UNIQUE predicate (which the UNIQUE constraint is described in terms of) is defined as "If there are no two rows in T such that the value of each column in one row is non-null and is equal to the value of the corresponding column in the other row according to Subclause 8.2 ... then the result of the <unique predicate> is true; otherwise, the result of the <unique predicate> is false."
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).
It may be possible to bypass the NULL != NULL by using coalesce. Here is an example: <BEGIN SQL> create table foo2 ( a integer not null, b integer not null, c integer null, UNIQUE (a,b,c) ); create function foo2_unique_func() RETURNS trigger AS ' DECLARE isfound integer = 0; BEGIN isfound = (select count(*) from foo2 where (new.a,new.b,coalesce(new.c::TEXT,''EmPtY'')) in (select a,b,coalesce(c::TEXT,''EmPtY'') from foo2)); RAISE NOTICE ''isfound: %'', isfound; IF isfound > 0 THEN RAISE EXCEPTION ''Columnsa,b,c Must Be Unique values (%,%,%)'', new.a, new.b, new.c; ELSE RETURN NEW; END IF; END; ' language 'plpgsql'; CREATE TRIGGER foo2_unique BEFORE INSERT OR UPDATE ON foo2 FOR EACH ROW EXECUTE PROCEDURE foo2_unique_func(); insert into foo2 values (1,300, null); insert into foo2 values (1,300, null); select * from foo2; select * from foo2 where (1,300,coalesce(null::TEXT,'EmPtY')) in (select a,b,coalesce(c::TEXT,'EmPtY') from foo2); drop table foo2 cascade; drop function foo2_unique_func() cascade; <END SQL>
Someone else suggested using coalesce to me as well. Isn't your function equivilant to mine? If so, I think the most elegantof these solutions is closer to the one I proposed (with Stephen's modification). Unfortunately, we've still got theconcurrency problems that Stephen pointed out. I sure would like the ability to affect the UNIQUE constraint's behavior to support this. Although, I'm probably in the minorityand it would obviously be un-portable. I think my plan will actually be to plug leaks in the domain layer and move toward non-nullable natural keys. I was hopingto find something better, but I frankly don't have the experience in house to write contstraints I'll have confidencein. Thanks for the tip! Phill -----Original Message----- From: Mikey [mailto:mikeboscia@gmail.com] Sent: Thu 5/5/2005 12:03 PM To: Tornroth, Phill Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] multi-column unique constraints with nullable columns It may be possible to bypass the NULL != NULL by using coalesce. Here is an example: <BEGIN SQL> create table foo2 ( a integer not null, b integer not null, c integer null, UNIQUE (a,b,c) ); create function foo2_unique_func() RETURNS trigger AS ' DECLARE isfound integer = 0; BEGIN isfound = (select count(*) from foo2 where (new.a,new.b,coalesce(new.c::TEXT,''EmPtY'')) in (select a,b,coalesce(c::TEXT,''EmPtY'') from foo2)); RAISE NOTICE ''isfound: %'', isfound; IF isfound > 0 THEN RAISE EXCEPTION ''Columnsa,b,c Must Be Unique values (%,%,%)'', new.a, new.b, new.c; ELSE RETURN NEW; END IF; END; ' language 'plpgsql'; CREATE TRIGGER foo2_unique BEFORE INSERT OR UPDATE ON foo2 FOR EACH ROW EXECUTE PROCEDURE foo2_unique_func(); insert into foo2 values (1,300, null); insert into foo2 values (1,300, null); select * from foo2; select * from foo2 where (1,300,coalesce(null::TEXT,'EmPtY')) in (select a,b,coalesce(c::TEXT,'EmPtY') from foo2); drop table foo2 cascade; drop function foo2_unique_func() cascade; <END SQL>
>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';