I'm going to change the pg_dump command to pull these constraints out of
pg_constaint where possible, creating the appropriate alter table add
constraint command (see primary key).


Should unique constraints created with 'create index' (no entry in
pg_constraint) be re-created via alter table add constraint, or via
create unique index? 

I prefer ...add constraint.  After a while (release or 2) removal of
create unique index all together.

Since index names are unique, and all unique and primary key constraints
have a matching name in pg_index there isn't a problem with name
conflicts.


12 Jul 2002 23:11:38 -0400
Rod Taylor <rbt@zort.ca>
Unique and Primary Key Constraints
Rod Taylor wrote:
> I'm going to change the pg_dump command to pull these constraints out of
> pg_constaint where possible, creating the appropriate alter table add
> constraint command (see primary key).
> 
> 
> Should unique constraints created with 'create index' (no entry in
> pg_constraint) be re-created via alter table add constraint, or via
> create unique index? 

CREATE UNIQUE INDEX has optimization purpose as well as an constraint
purpose.  I think CREATE UNIQUE INDEX is the way to go.

> I prefer ...add constraint.  After a while (release or 2) removal of
> create unique index all together.

Remove CREATE UNIQUE INDEX entirely?  Why?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 13 Jul 2002 00:20:36 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Unique and Primary Key Constraints
> > I prefer ...add constraint.  After a while (release or 2) removal of
> > create unique index all together.
> 
> Remove CREATE UNIQUE INDEX entirely?  Why?

I was looking to encourage users to use core SQL as I spend more time
than I want converting between systems -- thanks in part to users who
create non-portable structures.

Temporarily forgot there are index types other than btree :)

Anyway, thanks for the answers.


13 Jul 2002 01:33:07 -0400
Rod Taylor <rbt@zort.ca>
Re: Unique and Primary Key Constraints
Rod Taylor wrote:
> > > I prefer ...add constraint.  After a while (release or 2) removal of
> > > create unique index all together.
> > 
> > Remove CREATE UNIQUE INDEX entirely?  Why?
> 
> I was looking to encourage users to use core SQL as I spend more time
> than I want converting between systems -- thanks in part to users who
> create non-portable structures.
> 
> Temporarily forgot there are index types other than btree :)

Not so much non-btree, but non-unique indexes themselves.  UNIQUE index
is funny because it is a constraint and an performance utility.  I see
your point that a constraint is more ANSI standard, but because we can't
get rid of non-unique indexes, I am not sure if there is really a good
reason to move to UNIQUE constraints.  Well, it does make the table
definition and index more compact (one statement) but we split them up
on pg_dump so we can load the table without the index, so it doesn't
seem to be a win.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 13 Jul 2002 10:29:28 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Unique and Primary Key Constraints
On Sat, 2002-07-13 at 10:29, Bruce Momjian wrote:
> Rod Taylor wrote:
> > > > I prefer ...add constraint.  After a while (release or 2) removal of
> > > > create unique index all together.
> > > 
> > > Remove CREATE UNIQUE INDEX entirely?  Why?
> > 
> > I was looking to encourage users to use core SQL as I spend more time
> > than I want converting between systems -- thanks in part to users who
> > create non-portable structures.
> > 
> > Temporarily forgot there are index types other than btree :)
> 
> Not so much non-btree, but non-unique indexes themselves.  UNIQUE index
> is funny because it is a constraint and an performance utility.  I see
> your point that a constraint is more ANSI standard, but because we can't

Yup.  Makes sense.  I submitted a patch which retains the difference. 
If the index is created with CREATE UNIQUE, it's dumped with CREATE
UNIQUE.  Constraint UNIQUE is treated likewise.


13 Jul 2002 11:08:21 -0400
Rod Taylor <rbt@zort.ca>
Re: Unique and Primary Key Constraints
Rod Taylor wrote:
> On Sat, 2002-07-13 at 10:29, Bruce Momjian wrote:
> > Rod Taylor wrote:
> > > > > I prefer ...add constraint.  After a while (release or 2) removal of
> > > > > create unique index all together.
> > > > 
> > > > Remove CREATE UNIQUE INDEX entirely?  Why?
> > > 
> > > I was looking to encourage users to use core SQL as I spend more time
> > > than I want converting between systems -- thanks in part to users who
> > > create non-portable structures.
> > > 
> > > Temporarily forgot there are index types other than btree :)
> > 
> > Not so much non-btree, but non-unique indexes themselves.  UNIQUE index
> > is funny because it is a constraint and an performance utility.  I see
> > your point that a constraint is more ANSI standard, but because we can't
> 
> Yup.  Makes sense.  I submitted a patch which retains the difference. 
> If the index is created with CREATE UNIQUE, it's dumped with CREATE
> UNIQUE.  Constraint UNIQUE is treated likewise.

Yes, very nice.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 13 Jul 2002 11:11:40 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Unique and Primary Key Constraints
Rod Taylor <rbt@zort.ca> writes:
> Yup.  Makes sense.  I submitted a patch which retains the difference. 
> If the index is created with CREATE UNIQUE, it's dumped with CREATE
> UNIQUE.  Constraint UNIQUE is treated likewise.

Yes, I was going to suggest that --- we should try to reproduce the way
that the definition was created, not enforce our own ideas of style.

CREATE INDEX will always be more flexible than constraints anyway
(non-default index type, non-default opclasses, partial indexes for
starters) so the notion that it might go away someday is a nonstarter.

Rod's original pg_depend patch tried to make a pg_constraint entry for
any unique index, but I changed it to only make entries for indexes
that were actually made from constraint clauses, so the distinction
is preserved in the system catalogs.  Just a matter of having pg_dump
respect it.

			regards, tom lane

Sat, 13 Jul 2002 13:03:55 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Unique and Primary Key Constraints 