--- title: Keys source: 'https://academia.sh/en/courses/relational-theory/keys' course: 'Data Modeling and Relational Theory' language: en updated: '2026-08-23T07:00:48+00:00' license: 'CC BY-SA 4.0' --- # Keys Definitions of superkey, candidate key, primary key, alternate key, and foreign key; the choice between a natural and a surrogate key, and the criteria for choosing a key. 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: ```sh 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, 'alice.kane@example.test', 'Alice Kane', '2024-09-12'); INSERT INTO member VALUES (52, 'alice.kane@example.test', '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. ```sh 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, 'alice.kane@example.test', 'Alice Kane'); INSERT INTO member VALUES (41, 'marcus.reyes@example.test', '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. ```sh 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. ```sh 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. ```sh 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.