Keys

The previous lesson arrived at the same point twice: rows need to be distinguishable from one another, and that distinguishing does not happen unless the schema says so. Left unstated, a SQL table is a multiset that accepts the same row twice. This lesson’s question is: how is the column set that identifies a row on its own defined, which one is chosen when several candidates exist, and how is one relation’s row bound to another relation’s row?

Superkey and Candidate Key

Call $H$ the set of attributes in a relation’s heading. A subset $K \subseteq H$ is a superkey if no two distinct rows carry the same values on $K$ in any valid value of the relation. In other words, the values on $K$ determine the row.

This definition is too generous: adding a redundant column to a superkey leaves it a superkey still. If member_no determines the row in the member relation, so does (member_no, name). Reducing away the redundancy gives a candidate key: a superkey none of whose proper subsets is itself a superkey. A candidate key is the smallest determining column set.

A relation can have more than one candidate key. In the member relation, both the member number and the email address determine a row on their own:

sqlite3 :memory: <<'SQL'
CREATE TABLE member (
  member_no     INTEGER PRIMARY KEY,
  email         TEXT NOT NULL UNIQUE,
  name          TEXT NOT NULL,
  registered_at TEXT NOT NULL
);
INSERT INTO member VALUES (41, '[email protected]', 'Alice Kane', '2024-09-12');
INSERT INTO member VALUES (52, '[email protected]', 'Alice K. Kane', '2025-01-08');
SQL

Runtime error near line 8: UNIQUE constraint failed: member.email (19)

The second insert is rejected even though it carries a different member number, because the email address is also a candidate key and that address is already in use. Being a candidate key comes not from the absence of repetition in today’s data, but from the rule that repetition can never occur. This distinction matters: a column that does not currently repeat in the data can be mistaken for a key. The right question is not “does it repeat right now” but “can it possibly repeat.” In a library setting the member name is a good example of this — that it does not repeat today does not mean two members named Alice Kane cannot exist.

Primary and Alternate Key

One of the candidate keys is chosen as the relation’s official identity; this is called the primary key. Candidate keys not chosen are called alternate keys, and they too are written into the schema as uniqueness constraints — the UNIQUE above does exactly that.

Choosing a primary key is a matter of preference, and it also affects the engine’s behavior: references made to rows from other relations are built on the primary key, and an index generally falls on it.

sqlite3 :memory: <<'SQL'
CREATE TABLE member (
  member_no INTEGER PRIMARY KEY,
  email     TEXT NOT NULL UNIQUE,
  name      TEXT NOT NULL
);
INSERT INTO member VALUES (41, '[email protected]', 'Alice Kane');
INSERT INTO member VALUES (41, '[email protected]', 'Marcus Reyes');
SQL

Runtime error near line 7: UNIQUE constraint failed: member.member_no (19)

Composite Key

A key need not consist of a single column. A key formed by several columns together is called a composite key. In the library, a book’s physical copy — from here on, a copy — is determined by three things: which book, which branch, which copy number at that branch.

sqlite3 :memory: <<'SQL'
CREATE TABLE copy (
  isbn        TEXT    NOT NULL,
  branch_code TEXT    NOT NULL,
  copy_no     INTEGER NOT NULL,
  PRIMARY KEY (isbn, branch_code, copy_no)
);
INSERT INTO copy VALUES ('978-975-0000-01-1', 'CEN', 1),
                        ('978-975-0000-01-1', 'CEN', 2),
                        ('978-975-0000-01-1', 'BHC', 1);
INSERT INTO copy VALUES ('978-975-0000-01-1', 'BHC', 1);
SQL

Runtime error near line 10: UNIQUE constraint failed: copy.isbn, copy.branch_code, copy.copy_no (19)

The first and second copies of the same book at the Central branch and the first copy at the Bahcelievler branch are separate rows; the fourth insert is rejected because it collides with the third. A composite key’s column order is immaterial to keyness — a set is a set — but it matters to index behavior; that distinction belongs to courses covering the engine’s internals.

Natural and Surrogate Key

A key can come from the domain’s own data or be generated for the purpose of identification. One that comes from the domain’s own data is called a natural key: a book’s ISBN, a branch code, a member’s email address. One generated purely to distinguish rows is called a surrogate key: a loan transaction’s sequence number, for instance.

sqlite3 :memory: <<'SQL'
.headers on
.mode box
CREATE TABLE loan (
  loan_no     INTEGER PRIMARY KEY,
  member_no   INTEGER NOT NULL,
  pickup_date TEXT    NOT NULL
);
INSERT INTO loan (member_no, pickup_date) VALUES (41, '2025-03-02'), (52, '2025-03-04');
SELECT * FROM loan;
SQL

┌─────────┬───────────┬─────────────┐
│ loan_no │ member_no │ pickup_date │
├─────────┼───────────┼─────────────┤
│ 1       │ 41        │ 2025-03-02  │
│ 2       │ 52        │ 2025-03-04  │
└─────────┴───────────┴─────────────┘

The insert statement gave no value for the loan_no column; the values were generated by the system. How the identity column is generated varies by engine: some engines define a separate sequence-generator object, some declare it in the column definition, some use the table’s hidden row number. That generated values will advance without gaps is a guarantee in no engine; the number consumed by a rolled-back transaction does not come back. Reading a surrogate key as “which record number” is therefore a mistake.

A natural key makes the data self-explanatory and needs no extra column; against that, it can change if the domain’s rules change. A surrogate key is stable and narrow; against that, it carries no meaning and does not by itself guarantee uniqueness — even in a relation carrying a surrogate key, the natural candidate key still needs to be written with UNIQUE, or the same book could be entered twice under two different numbers.

Foreign Key

Relations are bound to one another by a foreign key: the values of a column set in one relation must be found among the candidate key values of another relation.

sqlite3 :memory: <<'SQL'
PRAGMA foreign_keys = ON;
CREATE TABLE member (
  member_no INTEGER PRIMARY KEY,
  name      TEXT NOT NULL
);
CREATE TABLE loan (
  loan_no     INTEGER PRIMARY KEY,
  member_no   INTEGER NOT NULL REFERENCES member (member_no),
  pickup_date TEXT    NOT NULL
);
INSERT INTO member VALUES (41, 'Alice Kane');
INSERT INTO loan VALUES (1001, 41, '2025-03-02');
INSERT INTO loan VALUES (1002, 77, '2025-03-04');
SQL

Runtime error near line 13: FOREIGN KEY constraint failed (19)

Member 41 exists in the table, so the first loan record passes; member 77 does not exist, so the second is rejected. The foreign key is the sole mechanism the relational model uses to bind relations together — rows are bound not by pointer but by value. This is a fundamental break from the linked lists of the Data Structures course: because the binding rests on a key value rather than a memory address, it keeps its validity when the database is closed and reopened, moved to another machine, or restored from a backup.

Criteria for Choosing a Key

Four criteria are useful when choosing a primary key among candidate keys.

• Stability. The key’s value should not change, since every row that references it carries that value. An email address is a poor primary key for this reason and a good alternate key.
• Narrowness. A key repeats in every relation that references it and takes up space in indexes. A single integer, in place of a three-column text composite, makes a clear difference as the number of references grows.
• Indivisibility. A key carrying information that can be parsed out — a string combining a branch code and a year, say — breaks when that rule changes.
• Privacy. A key appears in addresses and links exposed to the outside. Making a personal value such as a national identity number a primary key also means exposing it.

Summary

• A superkey is any column set that determines the row; a candidate key is a superkey none of whose proper subsets is itself a superkey.
• One of the candidate keys is chosen as the primary key; the rest are written as alternate keys with a uniqueness constraint.
• Keyness comes not from today’s data but from the rule placed on the data; “it does not currently repeat” is not proof of keyness.
• A natural key comes from the data and can change; a surrogate key is generated and does not change, though it does not remove the need for the natural candidate key’s uniqueness.
• A foreign key binds rows by value rather than by address, so it keeps its validity across migration and restoration.

Next Step

This lesson defined the foreign key and rejected one violation of it, but it did not name the rule behind that rejection: why can a loan record not be written for a member who does not exist, and what should happen if that member’s record is later deleted? The next lesson defines the two integrity rules keys rest on — entity integrity and referential integrity — and shows the behaviors the system can choose on deletion and update.