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@xxxxxxxx> 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
