Does a Join table (association table) have a primary key ? many to many relationship

Question

Does a Join table (association table) have a primary key ? many to many relationship. I've seen some Join tables with a primary key and some without can someone please explain when you would have a primary key in a join table and why?

Thank you in advance;-)

Answer 1

In a pure 'join' or junction table all the fields will be part of the primary key. For example let's consider the following tables:

CREATE TABLE USERS
  (ID_USER NUMBER PRIMARY KEY,
   FIRST_NAME VARCHAR2(32),
   LAST_NAME VARCHAR2(32));

CREATE TABLE ATTRIBUTES
  (ID_ATTRIBUTE NUMBER PRIMARY KEY,
   ATTRIBUTE_NAME  VARCHAR2(64));

A junction table between these to allow many users to have many attributes would be

CREATE TABLE USER_ATTRIBUTES
  (ID_USER NUMBER REFERENCES USERS(ID_USER),
   ID_ATTRIBUTE NUMBER REFERENCES ATTRIBUTES(ID_ATTRIBUTE),
   PRIMARY KEY(ID_USER, ID_ATTRIBUTE));

Sometimes you'll find the need to add a non-primary column to a junction table but I find this is relatively rare.

Share and enjoy.

Answer 2

All tables should have a primary key. :-)

You can either use a compound foreign key, or a blind integer key.

You would use the compound foreign key when there are no other elements in your association table.

You could use the blind integer key when the association table has elements of its own. The compound foreign key would be defined with two additional indexes.

Answer 3

It depends on the records you are associating. You can create a composite primary key on the id's of the associated records as long as you don't need multiple records per association.

However, its far more important that you make sure both these columns are indexed and have referential integrity defined.

Answer 4

Relationship tables frequently have three candidate keys, one of which need not be enforced with a constraint, and the choice of which key (if any) should be 'primary' is arbitrary.

Consider this example from Joe Celko:

CREATE TABLE Couples
(boy_name INTEGER NOT NULL UNIQUE -- nested key
REFERENCES Boys (boy_name),
girl_name INTEGER NOT NULL UNIQUE -- nested key,
REFERENCES Girls(girl_name),
PRIMARY KEY(boy_name, girl_name)); -- compound key

The "Couples" table lets you insert these rows from the original set:

('Joe Celko', 'Brooke Shields')
('Alec Baldwin', 'Kim Bassinger')

Think about this table for a minute. The PRIMARY KEY is now redundant. If each boy appears only once in the table and each girl appears only once in the table, then each (boy_name, girl_name) pair can appear only once.

From a theoretical viewpoint, I could drop the compound key and make either boy_name or girl_name the new primary key, or I could just leave them as candidate keys.

SQL products and theory do not always match. Many products make the assumption that the PRIMARY KEY is in some way special in the data model and will be the way to access the table most of the time.

...HOWEVER I suspect you question is implying something more like, "Assuming I'm the kind of person who shuns natural keys in favour of artificial identifiers, should I add an artificial identifier to a table that is composed entirely of artificial identifiers referenced from other tables?"