Re: Unique or Primary Key?

On Wed, May 02, 2001 at 06:50:09PM -0700, some SMTP stream spewed forth:
> On Thu, May 03, 2001 at 12:58:03AM +0100, pgsql@itsbruce.uklinux.net wrote:
> > This table is man-in-the-middle of a many-to-many relationship:
> >
> > CREATE TABLE cv_entries (
> >         subscriber INTEGER NOT NULL
> >                 REFERENCES subscribers
> >                         ON DELETE CASCADE
> >                         ON UPDATE CASCADE,
> >         entry_type INTEGER NOT NULL
> >             REFERENCES cv_entry_types
> >                         ON DELETE CASCADE
> >                         ON UPDATE CASCADE,
> >         ordinal INTEGER,
> >     value1 TEXT,
> >     value2 TEXT,
> >     minimum_trust SMALLINT,
> >     UNIQUE(subscriber, entry_type, ordinal)
> > );
> >
> > I used a unique index here because I couldn't see any reason for a
> > Primary Key - this table will always be searched on either the
> > subscriber or entry_type index.
> >
> > Was I wrong?  Should this be a Primary Key?
>
> I think it's a distinction without a difference. A primary key is just a
> way to identify a unique tuple that's been chosen from a possible
> set of candidate keys (often there's only one candidate).  And,
> primary keys are enforced with a unique index...
>

Just to expand on Eric's response, a Primary Key directive creates a
unique not null column, whereas a unique column can be null. In this
instance, your columns are already not null, so, as Eric responded, it is
basically the same thing.


gh

> --
> Eric G. Miller <egm2@jps.net>
>