Thread

1. UNIQUE constraint and indexing

   Arcady Genkin <a.genkin@utoronto.ca> — 2001-09-27T22:30:50Z

   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
   
   

2. Re: UNIQUE constraint and indexing

   GH <grasshacker@over-yonder.net> — 2001-09-28T11:41:38Z

   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
   
   

3. Re: UNIQUE constraint and indexing

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2001-09-28T15:35:25Z

   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.
   
   
   

4. Re: UNIQUE constraint and indexing

   Arcady Genkin <a.genkin@utoronto.ca> — 2001-10-01T14:45:18Z

   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
   
   

5. Re: UNIQUE constraint and indexing

   Keary Suska <hierophant@pcisys.net> — 2001-10-01T16:15:32Z

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