__________________________________________________________________________________ Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 Mike.Blackwell@rrd.com http://www.rrdonnelley.com
On Tue, Sep 25, 2012 at 12:37 PM, Andreas Joseph Krogh <andreak@officenet.no> wrote:
On 09/25/2012 05:05 PM, Mike Blackwell wrote:
How would one go about building a multi-column unique constraint where null is a significant value, eg. (1, NULL) <> (2, NULL)?
I see a number of references to not being able to use an index for this, but no mention of an alternative. Any pointers would be appreciated
create table my_table( some_column varchar not null, other_column varchar);
create unique index my_idx on my_table(some_column, other_column) where other_column is not null; create unique index my_fish_idx on my_table(some_column) where other_column is null;
insert into my_table (some_column, other_column) values('a', 'a'); insert into my_table (some_column, other_column) values('a', 'b'); insert into my_table (some_column) values('a'); insert into my_table (some_column) values('b');
-- fails insert into my_table (some_column, other_column) values('a', 'a'); -- also fails insert into my_table (some_column) values('a');
result:
andreak=# insert into my_table (some_column, other_column) values('a', 'a'); ERROR: duplicate key value violates unique constraint "my_idx" DETAIL: Key (some_column, other_column)=(a, a) already exists.
andreak=# insert into my_table (some_column) values('a'); ERROR: duplicate key value violates unique constraint "my_fish_idx" DETAIL: Key (some_column)=(a) already exists.