UNIQUE constraint and indexing

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

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.

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

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"