John English <[email protected]> writes: > I have a table with two columns whose combined value I want to be unique: > > create table Foo ( > A integer, > B integer, > primary key(A), > unique(A,B) > ); > > This works fine except when B is null, when I can have multiple rows > containing identical values of the form (A,null). > > Is there an easy way to constrain the values of A to be unique even > when B is null? (I could try to change things so that empty strings > are used instead of nulls, but that would involve changing existing > code and it will take quite a bit of work to ensure that there aren't > any unexpected knock-on effects, so I prefer to stick with nulls if I > can.)
Hi John, I think CREATE UNIQUE INDEX ui ON Foo(A, B) might do what you want. In contrast to a unique constraint, a unique index doesn't allow duplicate nulls. Hope this helps, -- Knut Anders
