Обсуждение: 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
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@mira.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
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
oops.... yeah... must have read it clearly
rgds,
dotyet
On 2/22/08, Erik Jones <erik@myemma.com> wrote:
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