📘 02-03: Keys

📌 Why Keys Exist

A key is one or more columns used to uniquely identify a row in a relation. Keys exist to satisfy the "no duplicate rows" rule from the previous note — and to let other tables refer back to a specific row reliably.

We'll use the Wedgewood Pacific (WP) schema as the running example for this entire course:

DEPARTMENT (DepartmentName, BudgetCode, OfficeNumber, DepartmentPhone)
EMPLOYEE   (EmployeeNumber, FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress)
PROJECT    (ProjectID, ProjectName, Department, MaxHours, StartDate, EndDate)
ASSIGNMENT (ProjectID, EmployeeNumber, HoursWorked)

(Primary keys are underlined in formal relation notation — shown here in bold instead, since Markdown doesn't render underlines well: DepartmentName, EmployeeNumber, ProjectID, and the composite (ProjectID, EmployeeNumber).)

---

📌 Candidate Keys and Primary Keys

A candidate key is any column (or combination of columns) that could uniquely identify each row. A relation can have more than one candidate key.

• In EMPLOYEE, both EmployeeNumber and EmailAddress might each uniquely identify a row — both are candidate keys.

A primary key is the one candidate key the team actually chooses for the DBMS to use and enforce. Every other candidate key that wasn't chosen is sometimes called an alternate key.

CREATE TABLE employee (
    employee_number SERIAL PRIMARY KEY,      -- chosen primary key
    email_address   VARCHAR(100) UNIQUE,     -- alternate key, still enforced unique
    first_name      VARCHAR(50),
    last_name       VARCHAR(50)
);

---

📌 Composite Keys

A composite key is a key made of two or more columns together — no single column in the group is unique on its own, but the combination is.

The WP ASSIGNMENT table is a textbook example: it records how many hours an employee worked on a project. Neither ProjectID alone nor EmployeeNumber alone is unique (one employee works on many projects; one project has many employees) — but the pair (ProjectID, EmployeeNumber) is unique, because a given employee is only assigned once to a given project.

CREATE TABLE assignment (
    project_id      VARCHAR(10) REFERENCES project(project_id),
    employee_number INT         REFERENCES employee(employee_number),
    hours_worked    NUMERIC(6, 2),
    PRIMARY KEY (project_id, employee_number)
);

---

📌 Natural Keys vs. Surrogate Keys

A natural key is a candidate key made of data that has real-world meaning — an email address, a Social Security number, a department name.

A surrogate key is an artificial, DBMS-generated identifier (usually a plain integer) added purely to serve as the primary key, with no business meaning of its own.

The WP DEPARTMENT table is a great cautionary example. Using DepartmentName as the primary key works — until Marketing gets renamed to "Sales & Marketing," and now every EMPLOYEE and PROJECT row that referenced 'Marketing' has to be updated too, or the whole schema loses referential integrity for a moment. A surrogate DepartmentID avoids that entirely:

-- Natural key design (works, but fragile if the name ever changes):
CREATE TABLE department (
    department_name  VARCHAR(50) PRIMARY KEY,
    budget_code       VARCHAR(20),
    office_number     VARCHAR(10),
    department_phone  VARCHAR(20)
);

-- Surrogate key design (recommended):
CREATE TABLE department (
    department_id     SERIAL PRIMARY KEY,
    department_name   VARCHAR(50) NOT NULL UNIQUE,
    budget_code        VARCHAR(20),
    office_number       VARCHAR(10),
    department_phone   VARCHAR(20)
);

Tip

A good surrogate key is short, numeric, and never changes — once assigned, it stays with that row forever, even if every other column about it gets edited. This is why SERIAL/auto-increment integers (or UUIDs) are such common primary key choices in real schemas, even when a natural key like an email address is also unique.

---

📌 Foreign Keys and Referential Integrity

A foreign key is a primary key from one relation, placed as a column in another relation, to represent a relationship between the two. In formal relation notation, a foreign key attribute is written in italics.

EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Position, Supervisor, OfficePhone, EmailAddress)

Here, Department is a foreign key referencing DEPARTMENT.DepartmentName — it's how a specific employee row is linked to a specific department row.

The referential integrity constraint: every value stored in the foreign key column must match an existing value of the primary key it references (or be NULL, if the relationship is optional).

Here's the full WP schema as runnable PostgreSQL, using surrogate keys throughout:

CREATE TABLE department (
    department_id     SERIAL PRIMARY KEY,
    department_name   VARCHAR(50) NOT NULL UNIQUE,
    budget_code        VARCHAR(20),
    office_number       VARCHAR(10),
    department_phone   VARCHAR(20)
);

CREATE TABLE employee (
    employee_number  SERIAL PRIMARY KEY,
    first_name        VARCHAR(50) NOT NULL,
    last_name          VARCHAR(50) NOT NULL,
    department_id      INT REFERENCES department(department_id),
    position           VARCHAR(50),
    supervisor         INT REFERENCES employee(employee_number),  -- self-referencing FK
    office_phone       VARCHAR(20),
    email_address      VARCHAR(100) UNIQUE
);

CREATE TABLE project (
    project_id     VARCHAR(10) PRIMARY KEY,
    project_name    VARCHAR(100) NOT NULL,
    department_id   INT REFERENCES department(department_id),
    max_hours       NUMERIC(8, 2),
    start_date      DATE,
    end_date        DATE
);

CREATE TABLE assignment (
    project_id       VARCHAR(10) REFERENCES project(project_id),
    employee_number  INT         REFERENCES employee(employee_number),
    hours_worked     NUMERIC(6, 2),
    PRIMARY KEY (project_id, employee_number)
);

Notice EMPLOYEE.Supervisor — a foreign key that references its own table's primary key. This is a common pattern for representing an org chart: every employee's supervisor is just another employee.

Note

Naming foreign keys the same as the table they reference. The original WP textbook schema names the foreign key column Department in both EMPLOYEE and PROJECT — the same as the table it references (DEPARTMENT), not the same as the primary key column (DepartmentName). This works, but can read as if the column is naming the table itself. A cleaner habit — and the one used above — is to name the foreign key after the primary key it points to (department_id), so it's obvious at a glance which column is a PK and which is an FK, even in a schema with hundreds of tables.

---

📌 Dialect Differences: Declaring Keys

Dialect	Auto-incrementing primary key
PostgreSQL	id SERIAL PRIMARY KEY or id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
MySQL / MariaDB	id INT AUTO_INCREMENT PRIMARY KEY
SQL Server	id INT IDENTITY(1,1) PRIMARY KEY
SQLite	id INTEGER PRIMARY KEY
Oracle	id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Foreign key syntax (REFERENCES table(column)) is essentially identical across all five engines — one of the more portable corners of SQL.

---

See also: Characteristics of Relations, Functional Dependencies

---

All Notes  |  Module 02 Exercise  |  Next: Functional Dependencies