Thread: Unique indicies

If you have an index like this:

CREATE UNIQUE INDEX foo ON tablename (f1, f2);

Is there any value in having independent indicies on f1 and f2 as well
or are they unnecessary?

Thanks
- Naz.

that would mean

Every row in foo for column f1 has to be unique
and
Every row in foo for column f2 has to be unique

Say for example:

create table test1 ( a int, b int ) ;
create unique index idx_t1_a on test1(a) ;
create unique index idx_t1_b on test1(b) ;


insert into test1 values (1,1) ; -- OK
insert into test1 values (1,2) ; -- FAIL
insert into test1 values (2,2) ; -- OK
insert into test1 values (2,1) ; -- FAIL

This is because the each record is composed to two unique columns, and the uniqueness is broken down to each column, rather than the record as a whole.

In case when you are creating the index as:
create unique index idx_t1_ab on test1(a, b) ;

insert into test1 values (1,1) ; -- OK
insert into test1 values (1,2) ; -- OK
insert into test1 values (2,2) ; -- OK
insert into test1 values (2,1) ; -- OK


This is because, each combination of column a and column b is unique in it's entirety.

hth,
dotyet

Naz Gassiep <naz@mira.net> writes:
> If you have an index like this:
> CREATE UNIQUE INDEX foo ON tablename (f1, f2);

> Is there any value in having independent indicies on f1 and f2 as well
> or are they unnecessary?

See
http://www.postgresql.org/docs/8.3/static/indexes.html
particularly sections 11.3 and 11.5

            regards, tom lane

On Feb 22, 2008, at 8:38 AM, Dot Yet wrote:

> that would mean
>
> Every row in foo for column f1 has to be unique
> and
> Every row in foo for column f2 has to be unique

The OP was asking about maintaining normal indexes on each in
addition to the unique index on (f1, f2), not separate unique indexes.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com