UNIQUE INDEX and PRIMARY KEY

Started by Kynn Jonesover 18 years ago2 messagesgeneral
Jump to latest
#1Kynn Jones
kynnjo@gmail.com

This is a follow-up to a question I asked earlier.

On 10/19/07, Gregory Stark <stark@enterprisedb.com> wrote:

What you need is:

CREATE UNIQUE INDEX foo_uniq_x_y on foo (canonicalize(x,y));

LOCATION: base_yyerror, scan.l:795

OK, now, what if instead of this

-> ALTER TABLE foo ADD CONSTRAINT foo_uniq_x_y UNIQUE(canonicalize(x,y));

what I was trying to do was this

-> ALTER TABLE foo ADD CONSTRAINT foo_pkey PRIMARY KEY(canonicalize(x,y));

Of course, this also elicits a syntax error, but is there a way to
achieve the same effect by creating some index?

I realize that, as far as the indexing and the enforcement of the
uniqueness constraint are concerned, the earlier solution using
"CREATE UNIQUE INDEX" works perfectly fine. But some software that I
use (Perl modules, etc.) get very confused whenever a table does not
have a primary key.

Of course, I could just add one more PRIMARY KEY constraint:

ALTER TABLE foo ADD CONSTRAINT foo_pkey PRIMARY KEY(x, y);

which would be satisfied automatically whenever the UNIQUE constraint
implicit in the index foo_uniq_x_y (see above) is satisfied. But this
amounts to maintaining an entirely superfluous index.

In short, my question is: is there a way to designate a pre-existing
UNIQUE INDEX (based on data contained in NOT NULL fields) as the basis
for a table's PRIMARY KEY?

TIA!

kj

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Kynn Jones (#1)
Re: UNIQUE INDEX and PRIMARY KEY

"Kynn Jones" <kynnjo@gmail.com> writes:

In short, my question is: is there a way to designate a pre-existing
UNIQUE INDEX (based on data contained in NOT NULL fields) as the basis
for a table's PRIMARY KEY?

No. If there were, that client software you mention would very likely
still get confused. Both the SQL standard and a lot of code assume
that the constituents of a PRIMARY KEY are just-plain-columns.

regards, tom lane