Primary Keys

By convention, every table in a relational database should have a primary key. A primary key is a column (or a set of columns) whose value uniquely identifies each record in the table.

Primary Key Rules:

  1. A primary key must contain unique values.
  2. A primary key column cannot have NULL values.
  3. A table can have only one primary key.

Simple Primary Key Example

Let's revisit our library database.

Authors Table

AuthorID AuthorName Nationality
101 J.R.R. Tolkien British
102 George Orwell British
103 Frank Herbert American

In the Authors table, AuthorID is the primary key. Each author has a unique ID, ensuring we can distinguish between two authors who might have the same name.

Books Table

BookID Title AuthorID (Foreign Key)
1 The Hobbit 101
2 1984 102
3 The Fellowship of the Ring 101

In the Books table, BookID is the primary key. The AuthorID column is a foreign key—it's a key used to link two tables together. It refers to the primary key in the Authors table, creating the crucial relationship between a book and its author.

Composite Primary Keys

Sometimes, a single column is not enough to uniquely identify a record. In these cases, we use a composite primary key, which is a primary key made up of two or more columns.

When to Use a Composite Key

The most common use for composite keys is in linking tables (also called junction or associative tables). These tables are used to model many-to-many relationships.

For example, a book can belong to multiple categories ("Fantasy" and "Adventure"), and a single category ("Fantasy") can contain hundreds of books. To model this, we create a BookCategories table.

Categories Table

CategoryID CategoryName
201 Fantasy
202 Adventure
203 Dystopian

BookCategories Linking Table

BookID CategoryID AssignedDate IsMainCategory
1 201 2023-01-15 TRUE
1 202 2023-01-20 FALSE
2 203 2023-02-10 TRUE
3 201 2023-03-05 TRUE

Why a Composite Key is Necessary Here:

  1. Individual Columns Fail:
    • BookID cannot be the primary key because Book 1 appears twice (for two different categories).
    • CategoryID cannot be the primary key because Category 201 appears twice (for two different books).
  2. The Combination is Unique: While the individual IDs repeat, the combination of (BookID, CategoryID) should never repeat. Even with extra information like AssignedDate or IsMainCategory, the fundamental relationship being identified is still the unique pairing of a book and a category.

The Big Advantage: Data Integrity

By setting (BookID, CategoryID) as the composite primary key, the database enforces the business rule that a book cannot be assigned to the same category more than once. If you tried to insert another row with 1 and 201 (even with a different date), the database would reject it. This ensures that the relationship itself stays clean and logical.

Natural vs. Surrogate Keys

When choosing a primary key, you generally have two options:

  1. Natural Key: A value that already exists in the real world and is naturally unique, such as a Book's ISBN or a person's Email Address.
  2. Surrogate Key: A unique value generated by the database specifically for identification purposes (like AuthorID or BookID). These values have no real-world meaning; they are just "surrogates" (stand-ins) for the record itself.

Note on Linking Tables: While you could add a new surrogate key (like LinkID) to your linking table, using a composite key is often preferred. This is because a composite key automatically prevents duplicate relationships without needing extra logic.