Thread: Unique and Primary Key Constraints
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.
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, Pennsylvania19026
> > 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.
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, Pennsylvania19026
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.
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, Pennsylvania19026
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