Are Primary Keys Necessary for Linking Tables?

[bhobbs]

We have a PostgreSQL database that contains several linking tables (all they contain is two foreign keys to link 2 other tables together). Is it necessary and/or advisable to specify the two attributes in these tables as a joint primary key? There is some disagreement among our team members and I'd like to get some more opinions.

Thanks,
Brandy

[cam]

Hi,

As you must be aware that primary keys prevent violation of duplicate data. But if you have the two fields a joint primary key and if the data that you are linking it to only gets displayed once then yes why not have a joint primary key instead of foreign keys. This will make it a composite key. Or alternative another way around this will be a have a joint UNIQUE key. so it will look something like this:-

create table test
(
a int,
b int,
constraint primary key (a,b)
);

OR

create table test
(
a int,
b int,
constraint UNIQUE key (a,b)
);

Any project or design has many solutions but every solution has to be the best decision for a company. But thats my point of view on it.

;) have fun!!!!!!

We have a PostgreSQL database that contains several linking tables (all they contain is two foreign keys to link 2 other tables together). Is it necessary and/or advisable to specify the two attributes in these tables as a joint primary key? There is some disagreement among our team members and I'd like to get some more opinions.

Thanks,
Brandy