Re: Unique indicies

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

On Fri, Feb 22, 2008 at 6:53 AM, Naz Gassiep <naz(at)mira(dot)net> wrote:

> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>