Unique or Primary Key?

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

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