Postgres Pro
Downloads
Home   >   mailing lists

Thread: UNIQUE constraint and indexing

UNIQUE constraint and indexing

From
Arcady Genkin
Date:
Is the index, created implicitely by "UNIQUE" constraint, the same
kind as created explicitely with "CREATE INDEX"?  In other words,
is the following piece of SQL redundant?

create table foo (
       bar serial primary key,
       baz text not null unique );
create index foo_baz_idx on foo(baz);

Many thanks,
--
Arcady Genkin
i=1; while 1, hilb(i); i=i+1; end

Re: UNIQUE constraint and indexing

From
Stephan Szabo
Date:
On 27 Sep 2001, Arcady Genkin wrote:

> Is the index, created implicitely by "UNIQUE" constraint, the same
> kind as created explicitely with "CREATE INDEX"?  In other words,
> is the following piece of SQL redundant?
>
> create table foo (
>        bar serial primary key,
>        baz text not null unique );
> create index foo_baz_idx on foo(baz);

Apart from the index for unique being a unique index, I believe so.

Re: UNIQUE constraint and indexing

From
"Daniel M . Kurry"
Date:
On Thu, Sep 27, 2001 at 06:30:50PM -0400, some SMTP stream spewed forth:
> Is the index, created implicitely by "UNIQUE" constraint, the same
> kind as created explicitely with "CREATE INDEX"?  In other words,
> is the following piece of SQL redundant?
>
> create table foo (
>        bar serial primary key,
>        baz text not null unique );
> create index foo_baz_idx on foo(baz);

Yes, to the best of my knowledge.

Daniel M. Kurry

> Many thanks,
> --
> Arcady Genkin
> i=1; while 1, hilb(i); i=i+1; end

Re: UNIQUE constraint and indexing

From
Arcady Genkin
Date:
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:

> On 27 Sep 2001, Arcady Genkin wrote:
>
> > Is the index, created implicitely by "UNIQUE" constraint, the same
> > kind as created explicitely with "CREATE INDEX"?  In other words,
> > is the following piece of SQL redundant?
> >
> > create table foo (
> >        bar serial primary key,
> >        baz text not null unique );
> > create index foo_baz_idx on foo(baz);
>
> Apart from the index for unique being a unique index, I believe so.

So, the unique index will still be used when processing SELECT
queries, right?  In other words, is the index foo_baz_idx redundant?

THanks,
--
Arcady Genkin

Re: UNIQUE constraint and indexing

From
Keary Suska
Date:
> So, the unique index will still be used when processing SELECT
> queries, right?  In other words, is the index foo_baz_idx redundant?

Yes. It is better to think of "Unique" as creating an index with the added
benefit of a "unique" constraint.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"

> From: Arcady Genkin <a.genkin@utoronto.ca>
> Date: 01 Oct 2001 10:45:18 -0400
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] UNIQUE constraint and indexing
>
> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>
>> On 27 Sep 2001, Arcady Genkin wrote:
>>
>>> Is the index, created implicitely by "UNIQUE" constraint, the same
>>> kind as created explicitely with "CREATE INDEX"?  In other words,
>>> is the following piece of SQL redundant?
>>>
>>> create table foo (
>>> bar serial primary key,
>>> baz text not null unique );
>>> create index foo_baz_idx on foo(baz);
>>
>> Apart from the index for unique being a unique index, I believe so.
>
> So, the unique index will still be used when processing SELECT
> queries, right?  In other words, is the index foo_baz_idx redundant?
>
> THanks,
> --
> Arcady Genkin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>