Composite key in associative tables

Question

I know that for associative entities, the unique identifiers from the neighboring entities must included within the associative entity to have the PK-FK constraint. And we create the constraint between the PK-FK keys with their respective neighbors in the SQL DDL commands.

However, when creating the SQL DDL commands, do we also have to create a composite key for that associative table as well (in addition to the PK-FK key constraints)? Help is much appreciated, thanks.

I don't think you have to, unless you need to ensure uniqueness of the relationship between objects. — Sebas, Apr 12 '15 at 03:34

score 2 · Answer 1 · edited May 23 '17 at 12:06

I think an example would make it a little clearer what you're asking, but if I understand correctly, you're asking whether or not the an entity with a foreign key has to include a the foreign key in its primary key (making it a composite primary key)?

It depends on what you're trying to accomplish, but the answer is usually no.

No

You are using surrogate keys (as opposed to natural keys), meaning the primary key isn't a value that comes from the real world but is rather an arbitrary unique value, usually a sequence of integers. In the example below, the DBA has made up unique identifiers for Teacher and Department that do not exist in the real-world.
```
CREATE TABLE Teacher
(
  Teacher_ID int PRIMARY KEY, -- e.g., 000001
  Department_ID int FOREIGN KEY REFERENCES Department(Department_ID)
);


CREATE TABLE Department
(
   Department_ID int PRIMARY KEY -- e.g., 000001
);
```

You are using natural keys, but the two entities can exist independently of each other. In the example below, this means that in the real world, teachers can be unassigned, and departments can have no teachers.

CREATE TABLE Teacher
(
   Teacher_SSN int PRIMARY KEY,  -- social security number
   Department_Name VARCHAR(255) FOREIGN KEY REFERENCES Department(Department_Name)
);

CREATE TABLE Department
(
   Department_Name VARCHAR(255) PRIMARY KEY
);

You are using natural keys, and the business rules are such that the associative entity needs a parent in order to exist, but it has its own natural primary key (enforced by a simple NOT NULL constraint on the foreign key). This means a department manager cannot exist without a corresponding department to belong to, but whose unique identity is not defined by the department.
```
CREATE TABLE DepartmentManager
(
   Manager_SSN int PRIMARY KEY,
   Department_Name VARCHAR(255) NOT NULL FOREIGN KEY REFERENCES Department(Department_ID)
);

CREATE TABLE Department
(
   Department_ID VARCHAR(255) PRIMARY KEY
);
```

Yes

The business rules or domain of the problem are such that the entity cannot be uniquely described without using the primary key of the parent entity as part of the associative entity's composite primary key, and you don't want to use a surrogate key. In the example below, a room number of "001" might exist multiple times because there are multiple buildings with rooms labeled "001", but if we include the building's name in the composite primary key, then we have a unique identifier. In this case, Room is referred to as a weak entity, and Building is a strong entity. (More on that here.)
```
CREATE TABLE Room
(
   Room_Number int,
   Building_Name VARCHAR(255) FOREIGN KEY REFERENCES Building(Building_Name),
   CONSTRAINT pk_Room PRIMARY KEY (Room_Number,Building_Name)
);

CREATE TABLE Building
(
   Building_Name VARCHAR(255) PRIMARY KEY
);
```

Composite key in associative tables

1 Answers1