db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Unique constraints and nulls
Date Mon, 19 May 2014 12:14:49 GMT
John English <john.foreign@gmail.com> 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

Hope this helps,

Knut Anders

View raw message