Thread: Unique or Primary Key?

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?

--
Bruce

Bitterly it mathinketh me, that I spent mine wholle lyf in the lists
against the ignorant.
        -- Roger Bacon, "Doctor Mirabilis"

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

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

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

On 5/3/01, 2:29:09 AM, GH <grasshacker@over-yonder.net> wrote regarding
Re: [GENERAL] Unique or Primary Key?:



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

OK.  I was just worried that there might be some more subtle issues
regarding query/index optimisation.

Besides, I couldn't see much use in creating it as a primary key.  How
would I ever reference it from another table?

--

Bruce

> Besides, I couldn't see much use in creating it as a primary
> key.  How
> would I ever reference it from another table?

What does this have to do with beeing a primary key?
Well you reference to primary keys as you do with others?!?

When you have for example user ids you shouldn't have a customer without
a number.. So a primary key would be a good idea, wouldn't it?

> > Besides, I couldn't see much use in creating it as a primary
> > key.  How
> > would I ever reference it from another table?

If you're questioning how to use a multi-field primary key, it's easy...


create table p (id1 int not null, id2 int not null, primary key(id1,
id2));

create table c (id1 int, id2 int, foreign key (id1, id2) references p);

insert into p values (1,2);
insert into c values (1,1);
insert into c values (1,0);

ERROR:  <unnamed> referential integrity violation - key referenced from c
not found in p

--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington