> ## Documentation Index
> Fetch the complete documentation index at: https://miliracle.online/llms.txt
> Use this file to discover all available pages before exploring further.
# Keys and Relationships
> Primary keys, foreign keys, composite keys, identifying and non-identifying relationships, and how to choose between auto-increment and UUID keys.
export const QUIZ = [{
q: "PhoneNumbers has three rows with person_id 1. Is that allowed?",
a: ["No, keys must be unique", "Yes, a foreign key value can repeat", "Only if person_id is NULL"],
c: 1,
why: "person_id in PhoneNumbers is a foreign key. Many phones may point at the same person. Only the primary key, (person_id, phone), must be unique."
}, {
q: "Ben and Chi share the phone 555-0200. Which key keeps PhoneNumbers unique?",
a: ["phone", "person_id", "person_id + phone"],
c: 2,
why: "Each column repeats on its own. Only the pair is different on every row, so the key is composite."
}, {
q: "Person.state points at States. Is that identifying or non-identifying?",
a: ["Identifying", "Non-identifying"],
c: 1,
why: "state is not part of Person's key. Ana is still the same person if she moves. The parent's key stays outside the child's key."
}, {
q: "You give PhoneNumbers its own phone_id key. What stops the same phone being stored twice for Chi?",
a: ["Nothing, unless you add UNIQUE (person_id, phone)", "The primary key", "The foreign key"],
c: 0,
why: "phone_id is new on every row, so the primary key sees no repeat. The foreign key only checks that person 3 exists. You need a UNIQUE constraint on the pair."
}, {
q: "Your URLs look like /orders/1047. What does a visitor learn?",
a: ["Nothing", "Roughly how many orders exist, and the next id", "The time of the order"],
c: 1,
why: "A counter shows how far it has counted and makes the next id easy to guess. A random UUIDv4 shows neither. Either way, check permissions on every request."
}];
export const ID_LANES = [{
name: "Auto-increment",
sql: "BIGINT GENERATED ALWAYS AS IDENTITY",
bytes: 8,
maker: "the database, at insert",
ids: ["1043", "1044", "1045", "1046", "1047"],
pos: [0.9, 0.92, 0.94, 0.96, 0.98],
index: "Always at the end of the index. Inserts stay cheap.",
leak: "A lot. /users/1047 tells a visitor there are about 1,047 users, and /users/1048 is next.",
tone: "is-bad"
}, {
name: "UUIDv4",
sql: "UUID DEFAULT gen_random_uuid()",
bytes: 16,
maker: "anyone: database or app",
ids: ["9f1c2e7a-4b3d-4e8f-a2c1-7d5e6f8a9b0c", "1b7e4d20-8c5f-4a91-b3e6-02d4c8f1a7e5", "d42a91c3-6e0b-4f78-9a15-c3b7e2d8f041", "5c08f3b6-2a7d-4c19-8e4f-61a9d0b2c7f3", "7e6d1a94-f3c2-4b85-a07e-9d2f5c1b3e68"],
pos: [0.62, 0.11, 0.83, 0.36, 0.49],
index: "Anywhere in the index. Each insert touches a random page, which costs more on big tables.",
leak: "Nothing. The value is 122 random bits: no count, no order, no time.",
tone: "is-ok"
}, {
name: "UUIDv7",
sql: "UUID DEFAULT uuidv7()",
bytes: 16,
maker: "anyone: database or app",
ids: ["01a1536e-9b00-7a3c-89e2-4c8d2f1a6b55", "01a1536e-9b04-7b81-84e6-2d9c07e31f42", "01a1536e-9b09-7c47-88a1-5e3f9c2d0a67", "01a1536e-9b0f-791e-8b6c-d4f05a7e3b19", "01a1536e-9b16-7d05-83f7-1e8b62d94c80"],
pos: [0.9, 0.92, 0.94, 0.96, 0.98],
index: "Near the end of the index, like a counter. Inserts stay cheap.",
leak: "When the row was made. The first 12 hex digits are a timestamp: 01a1536e9b00 is 2026-10-19 09:12:00.000 UTC.",
tone: "is-warn"
}];
export const REL_VIEWS = [{
tab: "Identifying",
parent: "Person",
child: "PhoneNumbers",
dashed: false,
label: "identifying",
tables: [{
name: "Person",
cols: ["person_id", "name", "state"],
key: [0],
fk: [2],
rows: [[1, "Ana", "CA"], [2, "Ben", "TX"], [3, "Chi", "CA"]]
}, {
name: "PhoneNumbers",
cols: ["person_id", "phone", "type"],
key: [0, 1],
fk: [0],
rows: [[1, "555-0101", "mobile"], [1, "555-0199", "work"], [2, "555-0200", "home"], [3, "555-0200", "home"], [3, "555-0303", "mobile"]]
}],
note: "The parent's key, person_id, is part of the child's key. A phone row is identified by whose phone it is.",
modes: [{
label: "",
ddl: [["", "CREATE TABLE PhoneNumbers ("], ["fk", " person_id BIGINT REFERENCES Person ON DELETE CASCADE,"], ["", " phone TEXT,"], ["", " type TEXT,"], ["pk", " PRIMARY KEY (person_id, phone)"], ["", ");"]]
}],
actions: [{
label: "Delete person 3 (Chi)",
res: [{
v: "Deleted.",
ok: true,
line: "fk",
ghost: {
0: [2],
1: [3, 4]
},
why: "Chi's two phone rows go with her. A phone row without its person would mean nothing, and its key would point at nobody. CASCADE does this for you."
}]
}, {
label: "Insert a phone with no person",
res: [{
v: "Refused.",
ok: false,
line: "pk",
add: {
t: 1,
row: [null, "555-0404", "mobile"],
stored: false
},
why: "person_id is part of the primary key, so it can never be NULL. The child can't exist without a parent."
}]
}]
}, {
tab: "Non-identifying",
parent: "States",
child: "Person",
dashed: true,
label: "non-identifying",
tables: [{
name: "States",
cols: ["state", "name"],
key: [0],
rows: [["CA", "California"], ["TX", "Texas"], ["NY", "New York"]]
}, {
name: "Person",
cols: ["person_id", "name", "state"],
key: [0],
fk: [2],
rows: [[1, "Ana", "CA"], [2, "Ben", "TX"], [3, "Chi", "CA"]]
}],
note: "state is only a column in Person, not part of its key. Ana is still Ana if she moves to Texas.",
modes: [{
label: "Make state mandatory (NOT NULL)",
ddl: [["", "CREATE TABLE Person ("], ["", " person_id BIGINT PRIMARY KEY,"], ["", " name TEXT NOT NULL,"], ["fk", " state CHAR(2) NOT NULL REFERENCES States ON DELETE RESTRICT"], ["", ");"]]
}, {
label: "Make state optional (NULL allowed)",
ddl: [["", "CREATE TABLE Person ("], ["", " person_id BIGINT PRIMARY KEY,"], ["", " name TEXT NOT NULL,"], ["fk", " state CHAR(2) REFERENCES States ON DELETE SET NULL"], ["", ");"]]
}],
actions: [{
label: "Delete state CA",
res: [{
v: "Refused.",
ok: false,
line: "fk",
bad: {
1: [0, 2]
},
why: "Ana and Chi still point at CA, and state may not be NULL. The delete is refused (RESTRICT). People are never deleted just because a lookup row went away."
}, {
v: "Deleted.",
ok: true,
line: "fk",
ghost: {
0: [0]
},
nulls: {
1: [0, 2]
},
why: "CA is deleted. Ana and Chi stay, and their state becomes NULL (SET NULL). Their identity never depended on it."
}]
}, {
label: "Insert Dan with no state",
res: [{
v: "Refused.",
ok: false,
line: "fk",
add: {
t: 1,
row: [4, "Dan", null],
stored: false
},
why: "Mandatory means every person must have a state. NOT NULL refuses Dan until he picks one."
}, {
v: "Accepted.",
ok: true,
line: "fk",
add: {
t: 1,
row: [4, "Dan", null],
stored: true
},
why: "Optional means a person may have no state yet. Dan is stored with state NULL."
}]
}]
}, {
tab: "With a pseudokey",
parent: "Person",
child: "PhoneNumbers",
dashed: false,
label: "still identifying",
tables: [{
name: "Person",
cols: ["person_id", "name", "state"],
key: [0],
fk: [2],
rows: [[1, "Ana", "CA"], [2, "Ben", "TX"], [3, "Chi", "CA"]]
}, {
name: "PhoneNumbers",
cols: ["phone_id", "person_id", "phone"],
key: [0],
fk: [1],
rows: [[101, 1, "555-0101"], [102, 1, "555-0199"], [103, 2, "555-0200"], [104, 3, "555-0200"], [105, 3, "555-0303"]]
}],
note: "The common modern shape: the child gets its own id, phone_id. person_id leaves the key, but a phone still can't exist without its person.",
modes: [{
label: "Declare it without UNIQUE",
ddl: [["", "CREATE TABLE PhoneNumbers ("], ["pk", " phone_id BIGINT PRIMARY KEY,"], ["fk", " person_id BIGINT NOT NULL REFERENCES Person ON DELETE CASCADE,"], ["", " phone TEXT NOT NULL"], ["uq", " -- nothing stops the same (person_id, phone) twice"], ["", ");"]]
}, {
label: "Add UNIQUE (person_id, phone)",
ddl: [["", "CREATE TABLE PhoneNumbers ("], ["pk", " phone_id BIGINT PRIMARY KEY,"], ["fk", " person_id BIGINT NOT NULL REFERENCES Person ON DELETE CASCADE,"], ["", " phone TEXT NOT NULL,"], ["uq", " UNIQUE (person_id, phone)"], ["", ");"]]
}],
actions: [{
label: "Delete person 3 (Chi)",
res: [{
v: "Deleted.",
ok: true,
line: "fk",
ghost: {
0: [2],
1: [3, 4]
},
why: "Same result as before: NOT NULL plus CASCADE keep the rule “a phone belongs to a person”, even though the key no longer shows it."
}, {
v: "Deleted.",
ok: true,
line: "fk",
ghost: {
0: [2],
1: [3, 4]
},
why: "Same result as before: NOT NULL plus CASCADE keep the rule “a phone belongs to a person”, even though the key no longer shows it."
}]
}, {
label: "Insert Chi's mobile again",
res: [{
v: "Stored a duplicate.",
ok: false,
line: "uq",
bad: {
1: [4]
},
add: {
t: 1,
row: [106, 3, "555-0303"],
stored: true
},
why: "That is the bug: row 106 repeats row 105. phone_id is new, so the primary key sees nothing wrong. The old composite key used to stop this."
}, {
v: "Refused.",
ok: true,
line: "uq",
add: {
t: 1,
row: [106, 3, "555-0303"],
stored: false
},
why: "That is the right answer. UNIQUE (person_id, phone) brings back the rule the composite key used to enforce."
}]
}]
}];
export const relShape = ({parent, child, dashed, label}) => ;
export const KEYSETS = [{
label: "person_id",
cols: [0],
ok: false,
why: "person_id repeats: Ana (1) has two phones and so does Chi (3). It can't be the key on its own."
}, {
label: "phone",
cols: [1],
ok: false,
why: "phone repeats: Ben and Chi share the home phone 555-0200. It can't be the key on its own either."
}, {
label: "person_id + phone",
cols: [0, 1],
ok: true,
why: "Every pair is different, even though each column repeats on its own. Together they are the key: a composite key."
}];
export const INSERTS = [{
label: "Insert person 3 again",
t: 0,
row: [3, "Dan", "NY"],
ok: false,
line: "pk",
clash: [2],
why: "Chi already has person_id 3. A primary key value may appear only once. Otherwise 3 would point at two people."
}, {
label: "Insert a person with no id",
t: 0,
row: [null, "Dan", "NY"],
ok: false,
line: "pk",
clash: [],
why: "A primary key can never be NULL. A row with no id could never be found or pointed at."
}, {
label: "Insert a phone for person 9",
t: 1,
row: [9, "555-0909", "mobile"],
ok: false,
line: "fk",
clash: [],
why: "The foreign key says person_id must match a row in Person. There is no person 9, so the phone would point at nobody."
}, {
label: "Insert a 3rd phone for Ana",
t: 1,
row: [1, "555-0111", "home"],
ok: true,
line: "fk",
clash: [0, 1],
why: "person_id 1 now appears three times in PhoneNumbers. That is fine: many phones may point at one person. Foreign key values can repeat."
}, {
label: "Insert Dan with no state",
t: 0,
row: [4, "Dan", null],
ok: true,
line: "state",
clash: [],
why: "state is a foreign key that allows NULL. NULL means “no state yet”, not a broken pointer, so the row is stored."
}];
export const INSERT_DDL = [["", "CREATE TABLE Person ("], ["pk", " person_id BIGINT PRIMARY KEY,"], ["", " name TEXT NOT NULL,"], ["state", " state CHAR(2) REFERENCES States"], ["", ");"], ["", "CREATE TABLE PhoneNumbers ("], ["fk", " person_id BIGINT REFERENCES Person,"], ["", " phone TEXT,"], ["", " type TEXT,"], ["ppk", " PRIMARY KEY (person_id, phone)"], ["", ");"]];
export const HERO = [{
phone: 4,
person: 2,
state: 0,
text: "Phone 555-0303 says person_id 3. Row 3 in Person is Chi. Chi's state is CA, and CA is California."
}, {
phone: 2,
person: 1,
state: 1,
text: "Phone 555-0200 says person_id 2. Row 2 in Person is Ben. Ben's state is TX, and TX is Texas."
}, {
phone: 1,
person: 0,
state: 0,
text: "Phone 555-0199 says person_id 1. Row 1 in Person is Ana. Ana's state is CA, and CA is California."
}];
export const ddlBlock = ({lines, on, ok}) => {
const Sql = sqlLine;
return
;
};
export const PHONES = {
name: "PhoneNumbers",
cols: ["person_id", "phone", "type"],
key: [0, 1],
fk: [0],
rows: [[1, "555-0101", "mobile"], [1, "555-0199", "work"], [2, "555-0200", "home"], [3, "555-0200", "home"], [3, "555-0303", "mobile"]]
};
export const PERSON = {
name: "Person",
cols: ["person_id", "name", "state"],
key: [0],
fk: [2],
rows: [[1, "Ana", "CA"], [2, "Ben", "TX"], [3, "Chi", "CA"]]
};
export const STATES = {
name: "States",
cols: ["state", "name"],
key: [0],
rows: [["CA", "California"], ["TX", "Texas"], ["NY", "New York"]]
};
export const useOnceVisible = (ref, onEnter) => {
const cb = useRef(onEnter);
cb.current = onEnter;
useEffect(() => {
const el = ref.current;
if (!el) return;
if (typeof IntersectionObserver === "undefined") {
cb.current();
return;
}
const io = new IntersectionObserver(entries => {
if (entries.some(e => e.isIntersecting)) {
io.disconnect();
cb.current();
}
}, {
threshold: 0.35
});
io.observe(el);
return () => io.disconnect();
}, [ref]);
};
export const useReducedMotion = () => {
const [reduce, setReduce] = useState(false);
useEffect(() => {
const mq = window.matchMedia("(prefers-reduced-motion: reduce)");
setReduce(mq.matches);
const on = e => setReduce(e.matches);
mq.addEventListener("change", on);
return () => mq.removeEventListener("change", on);
}, []);
return reduce;
};
> This is the Markdown version of an interactive page. Every animated figure on the web page is replaced here by a table or code block with the same facts. Sources: GeeksforGeeks, ["Difference between Primary Key and Foreign Key"](https://www.geeksforgeeks.org/dbms/difference-between-primary-key-and-foreign-key/) and ["Composite Key in SQL"](https://www.geeksforgeeks.org/sql/composite-key-in-sql/); Clever Cloud, ["Why Auto Increment Is A Terrible Idea"](https://www.clever.cloud/blog/engineering/2015/05/20/why-auto-increment-is-a-terrible-idea/) (2015). Corrections and newer facts are noted in the text.
Chi has a phone number, 555-0303. It is stored in a table called `PhoneNumbers`. Her name is in a different table, `Person`, and the name of her state is in a third one, `States`. No arrow joins these rows on disk. All they share is a few matching values: `3` in two tables, `CA` in two others.
Those values are **keys**. A key is how a row says "this is me", and how another row says "I belong to that one". Pick them well and the database keeps your data consistent for you. Pick them badly and you get phones that belong to nobody, people you can't delete, and URLs that tell strangers how many customers you have.
This post uses one small example the whole way through: three people, their states and their phones. It assumes you know what a table and a row are. For superkeys and candidate keys, see [The Relational Model](/writing/database/relational-model). For choosing between natural and surrogate keys, see [Designing a Relational Database](/writing/database/relational-database-design).
**In short**
* A **primary key** identifies a row. It is unique and never NULL. Each table has exactly one.
* A **foreign key** points at a row in another table. Its values can repeat and can be NULL, but a non-NULL value must match a real row.
* A **composite key** is a key made of two or more columns. The combination must be unique; each column alone may repeat.
* In an **identifying** relationship, the child can't exist without the parent, so the parent's key is part of the child's key. In a **non-identifying** one, the parent's key is just a column. It can be **optional** (NULL allowed) or **mandatory** (NOT NULL).
* Auto-increment keys are small and fast but leak counts. UUIDv4 keys leak nothing but scatter the index. UUIDv7 keys are index-friendly but reveal when a row was made.
## The example
Here are the three tables. Underlined headings in the figures are primary keys. Dotted cells are foreign keys.
`States`:
| state (PK) | name |
| - | - |
| CA | California |
| TX | Texas |
| NY | New York |
`Person`:
| person\_id (PK) | name | state (FK → States) |
| - | - | - |
| 1 | Ana | CA |
| 2 | Ben | TX |
| 3 | Chi | CA |
`PhoneNumbers`:
| person\_id (PK, FK → Person) | phone (PK) | type |
| - | - | - |
| 1 | 555-0101 | mobile |
| 1 | 555-0199 | work |
| 2 | 555-0200 | home |
| 3 | 555-0200 | home |
| 3 | 555-0303 | mobile |
The hero figure follows a phone to its owner: phone 555-0303 has `person_id` 3; person 3 is Chi; Chi's `state` is CA; CA is California. The same works for 555-0200 → Ben → TX → Texas, and 555-0199 → Ana → CA → California.
```sql theme={null}
CREATE TABLE States (
state CHAR(2) PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE Person (
person_id BIGINT PRIMARY KEY,
name TEXT NOT NULL,
state CHAR(2) REFERENCES States
);
CREATE TABLE PhoneNumbers (
person_id BIGINT REFERENCES Person ON DELETE CASCADE,
phone TEXT,
type TEXT,
PRIMARY KEY (person_id, phone)
);
```
Ben and Chi share a home phone (they live together). That small detail matters later.
## Primary key vs foreign key
A **primary key** answers "which row is this?". In `Person` that is `person_id`. Two rules come with it: no two rows may share a value, and the value may never be empty. In SQL terms, a primary key is `UNIQUE` plus `NOT NULL`. A table has exactly one primary key, although that one key may span several columns.
A **foreign key** answers "which row does this belong to?". `PhoneNumbers.person_id` is a foreign key to `Person`. Its rule is different: every non-NULL value must match a row that really exists in the parent table. The database checks this on every insert and update. This rule is called **referential integrity**.
Try five inserts and watch which rule decides each one.
The definition in the figure:
```sql theme={null}
CREATE TABLE Person (
person_id BIGINT PRIMARY KEY,
name TEXT NOT NULL,
state CHAR(2) REFERENCES States
);
CREATE TABLE PhoneNumbers (
person_id BIGINT REFERENCES Person,
phone TEXT,
type TEXT,
PRIMARY KEY (person_id, phone)
);
```
| Insert | Row | Result | Rule that decides it | Why |
| - | - | - | - | - |
| Insert person 3 again | `Person (3, 'Dan', 'NY')` | Refused | `person_id` primary key | Chi already has 3; a primary key value may appear once |
| Insert a person with no id | `Person (NULL, 'Dan', 'NY')` | Refused | `person_id` primary key | A primary key can never be NULL |
| Insert a phone for person 9 | `PhoneNumbers (9, '555-0909', 'mobile')` | Refused | foreign key to `Person` | There is no person 9 |
| Insert a 3rd phone for Ana | `PhoneNumbers (1, '555-0111', 'home')` | Accepted | foreign key to `Person` | Foreign key values can repeat; person 1 exists |
| Insert Dan with no state | `Person (4, 'Dan', NULL)` | Accepted | foreign key to `States` | This foreign key allows NULL, meaning "no state yet" |
Side by side:
| | Primary key | Foreign key |
| - | - | - |
| Job | Identify this row | Point at a parent row |
| Repeats? | Never | Allowed |
| NULL? | Never | Allowed, unless `NOT NULL` |
| Per table | Exactly one | Any number |
Three points where many tutorials, including the source, get loose:
* **A foreign key can point at any unique column**, not only the primary key. A `UNIQUE` email column works too. Pointing at the primary key is just the usual choice.
* **The primary key does not "become" a foreign key.** The child gets its own column, `PhoneNumbers.person_id`, that holds copies of the parent's key values.
* **A parent row *can* be deleted while children point at it.** What happens is up to you. By default the database says no (`RESTRICT` or `NO ACTION`). With `ON DELETE CASCADE` the children are deleted too. With `ON DELETE SET NULL` their foreign key becomes NULL. Figure 3 shows all three.
## Composite keys
Look at `PhoneNumbers` again. Can `person_id` be its key? No: Ana has two phones, so `1` shows up twice. Can `phone` be its key? No: Ben and Chi share 555-0200. But the pair (`person_id`, `phone`) is different on every row. A key made of two or more columns like this is called a **composite key**.
Each choice of key columns, tested on the five `PhoneNumbers` rows:
| Key columns | Repeated values | Verdict |
| - | - | - |
| `person_id` | 1 (Ana, 2 rows), 3 (Chi, 2 rows) | Not a key |
| `phone` | 555-0200 (Ben and Chi) | Not a key |
| `person_id, phone` | none | Key: a composite key |
In SQL you list the columns in one `PRIMARY KEY (…)` clause, as in the `CREATE TABLE` above. A few facts that go with it:
* **Every column in it is NOT NULL.** A primary key can't be partly empty.
* **Only the combination is unique.** `(1, '555-0101')` can appear once, but `1` and `555-0101` can each appear in other rows.
* **Column order matters for speed.** The database builds an index on `(person_id, phone)`. That index finds "all of Ana's phones" quickly, because `person_id` comes first. It does not help a search on `phone` alone.
* **Every child must copy all of it.** If another table pointed at a phone row, it would need both `person_id` and `phone`. That is fine for two columns and painful for four.
Composite keys fit two cases well:
* **Junction tables** in a many-to-many link, with one row per pair.
* **Child rows that belong to a parent**, like phones. This case has a name, which is the next section.
## Identifying vs non-identifying relationships
Every foreign key creates a parent–child relationship. There are two kinds, and the test is one question: **can the child row exist without its parent?**
**Identifying.** A phone number in `PhoneNumbers` is *Chi's* phone. Without Chi, the row means nothing. So the parent's key, `person_id`, is part of the child's key. The child is identified *by* its parent: "Chi's mobile", not just "a mobile". In diagrams this is drawn as a **solid line**.
**Non-identifying.** `Person.state` points at `States`, so `Person` is a child of `States`. But Ana is not identified by her state. If she moves to Texas she is still person 1. So `state` stays outside the primary key: it is a plain column that happens to be a foreign key. This is typical of **lookup tables** such as states, countries or currencies. In diagrams it is drawn as a **dashed line**.
**Identifying: Person → PhoneNumbers (solid line).**
```sql theme={null}
CREATE TABLE PhoneNumbers (
person_id BIGINT REFERENCES Person ON DELETE CASCADE,
phone TEXT,
type TEXT,
PRIMARY KEY (person_id, phone)
);
```
| Change | Result |
| - | - |
| Delete person 3 (Chi) | Done. Chi's two phone rows (555-0200, 555-0303) are deleted with her (`CASCADE`). |
| Insert a phone with no person `(NULL, '555-0404', 'mobile')` | Refused. `person_id` is part of the primary key, so it can't be NULL. |
**Non-identifying: States → Person (dashed line).** `state` is not part of `Person`'s key.
| Change | Mandatory: `state CHAR(2) NOT NULL REFERENCES States ON DELETE RESTRICT` | Optional: `state CHAR(2) REFERENCES States ON DELETE SET NULL` |
| - | - | - |
| Delete state CA | Refused. Ana and Chi still point at CA. | Done. CA is deleted; Ana's and Chi's `state` becomes NULL; both people stay. |
| Insert Dan with no state `(4, 'Dan', NULL)` | Refused by `NOT NULL`. | Accepted. |
**With a pseudokey: Person → PhoneNumbers, still identifying in meaning.**
```sql theme={null}
CREATE TABLE PhoneNumbers (
phone_id BIGINT PRIMARY KEY,
person_id BIGINT NOT NULL REFERENCES Person ON DELETE CASCADE,
phone TEXT NOT NULL,
UNIQUE (person_id, phone) -- without this line, duplicates get in
);
```
Rows: `(101, 1, 555-0101)`, `(102, 1, 555-0199)`, `(103, 2, 555-0200)`, `(104, 3, 555-0200)`, `(105, 3, 555-0303)`.
| Change | Without `UNIQUE` | With `UNIQUE (person_id, phone)` |
| - | - | - |
| Delete person 3 (Chi) | Done. Rows 104 and 105 go with her. | Same. |
| Insert Chi's mobile again `(106, 3, '555-0303')` | Stored: a duplicate of row 105. The new `phone_id` hides it from the primary key. | Refused by `UNIQUE`. |
### Optional or mandatory
A non-identifying relationship has one more choice: may the foreign key be NULL?
* **Optional:** the column allows NULL. "Dan has not told us his state yet" is a valid row. If CA is deleted, `ON DELETE SET NULL` can clear the pointer and keep the person.
* **Mandatory:** the column is `NOT NULL`. Every person must have a state. Deleting a state that is in use is refused.
An identifying relationship is always mandatory. The foreign key is part of the primary key, and primary key columns can't be NULL.
### The pseudokey shortcut
Many teams give every table its own id column, even child tables. Here that means `PhoneNumbers` gets a `phone_id`, and `phone_id` alone becomes the primary key:
* Before: `PRIMARY KEY (person_id, phone)`
* After: `PRIMARY KEY (phone_id)`
A phone still belongs to its person. Only the key changed. But the old key was quietly doing two jobs:
* **It made `person_id` required.** Key columns can never be NULL. Now `person_id` is an ordinary column, so a phone with no owner can be stored.
* **It stopped duplicates.** The pair (`person_id`, `phone`) had to be unique. Now only `phone_id` must be unique, and every new row gets a new `phone_id`. So Chi's mobile can be saved twice, as rows 105 and 106.
Write both rules back by hand:
```sql theme={null}
person_id BIGINT NOT NULL REFERENCES Person ON DELETE CASCADE, -- 1. every phone has an owner
UNIQUE (person_id, phone) -- 2. no phone twice per person
```
`ON DELETE CASCADE` is not one of the lost rules; you choose it with either key. Keep it so Chi's phones still go when Chi does. The third tab of Figure 3 shows the duplicate getting in, then being refused once `UNIQUE` is added.
One side effect: diagram tools pick the line style from the key alone. `person_id` is no longer in the key, so they draw a dashed (non-identifying) line, even though a phone still can't exist without its person. That is only the drawing. The database enforces exactly the constraints you write, nothing more.
## What the key value is made of
The last choice is the key's *value*. Most tables use a **surrogate key**: a value with no meaning, made just to identify the row. There are three common kinds. Watch five new users sign up under each.
Five sign-ups in a row under each key type (values are illustrative):
| | Auto-increment | UUIDv4 | UUIDv7 |
| - | - | - | - |
| SQL (PostgreSQL) | `BIGINT GENERATED ALWAYS AS IDENTITY` | `UUID DEFAULT gen_random_uuid()` | `UUID DEFAULT uuidv7()` (PostgreSQL 18+) |
| Size | 8 bytes | 16 bytes | 16 bytes |
| Made by | The database, at insert | Database or application | Database or application |
| Sample keys | 1043, 1044, 1045, 1046, 1047 | `9f1c2e7a-4b3d-4e8f-a2c1-7d5e6f8a9b0c`, `1b7e4d20-8c5f-4a91-b3e6-02d4c8f1a7e5`, … | `01a1536e-9b00-7a3c-89e2-4c8d2f1a6b55`, `01a1536e-9b04-7b81-84e6-2d9c07e31f42`, … |
| Where new keys land in the index | Always at the end | Random places | Near the end |
| What a URL leaks | Row count and the next id (`/users/1048`) | Nothing | Creation time (first 48 bits are a millisecond timestamp: `01a1536e9b00` = 2026-10-19 09:12:00.000 UTC) |
**Auto-increment** hands out 1, 2, 3, … It is small (8 bytes as `BIGINT`), new rows go at the end of the index, and it is easy to read in logs. The Clever Cloud post lists its costs:
* **It leaks.** `/users/1047` tells anyone roughly how many users you have. Sign up twice a week apart and you know your growth rate too.
* **It is guessable.** If `/invoices/1047` is yours, `/invoices/1046` is someone else's. That only hurts if the server forgets to check who is asking, but that mistake is common.
* **It is per table.** User 5 and order 5 are both "5". Mix them up in a script and you delete the wrong thing.
* **Only the database can make one.** The application must insert a row before it knows its id, which makes creating a parent and its children in one go more awkward.
**UUIDv4** is 122 random bits. Anyone can make one, anywhere, with no clash in practice, so the app can build a parent and its children before saving any of them. It reveals nothing. The cost: 16 bytes instead of 8, and because each value is random, every insert lands at a random place in the index. On big, busy tables that means more pages touched and a less compact index.
**UUIDv7** starts with a timestamp, then random bits. New values sort after older ones, so inserts go near the end of the index, like a counter. It keeps the "make it anywhere" benefit. The trade-off is that anyone who sees the key can read when the row was created.
The 2015 source is dated in a few places:
* Its `serial` overflow worry is real, but `serial` is a 32-bit integer (about 2.1 billion). Today you would use `BIGINT GENERATED … AS IDENTITY`, which does not run out in practice.
* PostgreSQL has had `gen_random_uuid()` built in since version 13, so no extension is needed. Version 18 added `uuidv7()`.
* UUIDv7 did not exist yet. It was standardized in RFC 9562 in 2024 and fixes the scattered-index problem.
* A UUID in a URL is not security. It is hard to guess, but it can still leak through logs, shared links and browser history. Always check that the user may see the row.
### What to pick
There is no single right answer, but a few defaults hold up:
* **Internal tables nobody sees in a URL:** a `BIGINT` identity is the simplest choice.
* **Ids that appear in URLs or APIs, or that the app creates offline:** UUIDv7. If creation time must stay private, use UUIDv4.
* **Both at once:** a common pattern is a `BIGINT` primary key for joins plus a separate `UUID` column, with a `UNIQUE` constraint, for the outside world.
* **A natural key from an outside authority** (an ISO state code like `CA`, an ISBN) is fine when it is stable. That is why `States` uses `state` itself as the key.
## Check yourself
Questions this document answers:
* What is the difference between a primary key and a foreign key? A primary key identifies a row: unique, never NULL, one per table. A foreign key points at a row in another table: values may repeat and may be NULL, but a non-NULL value must match an existing parent row.
* Can a foreign key reference something other than a primary key? Yes, any column (or set of columns) with a `UNIQUE` constraint.
* Can you delete a parent row that has children? Depends on `ON DELETE`: `RESTRICT`/`NO ACTION` (default) refuses, `CASCADE` deletes the children, `SET NULL` clears their foreign key.
* What is a composite key? A primary key made of two or more columns; the combination is unique, each column alone may repeat, and all its columns are NOT NULL.
* PhoneNumbers has three rows with person\_id 1. Is that allowed? Yes. It is a foreign key there; only the full key (person\_id, phone) must be unique.
* What is an identifying relationship? The child can't exist without the parent, so the parent's key is part of the child's primary key (Person → PhoneNumbers). Drawn as a solid line.
* What is a non-identifying relationship? The parent's key is a plain column in the child, outside its primary key (States → Person). Drawn as a dashed line. It can be optional (FK allows NULL) or mandatory (FK is NOT NULL).
* If a child table gets its own surrogate key (phone\_id), what must you add? `person_id NOT NULL` with `ON DELETE CASCADE`, and `UNIQUE (person_id, phone)` to stop duplicates.
* What does an auto-increment id in a URL reveal? Roughly how many rows exist, the growth rate, and the next id.
* UUIDv4 vs UUIDv7? Both 16 bytes and can be made by the app. v4 is fully random: reveals nothing but scatters index inserts. v7 starts with a millisecond timestamp: index-friendly but reveals creation time.
* Is a UUID in a URL a security measure? No. Always check permissions on every request.
## Glossary
| Term | Meaning |
| - | - |
| Primary key | The column(s) that identify each row. Unique and never NULL; one per table. |
| Foreign key | A column whose values must match a key in another (parent) table. |
| Referential integrity | The rule that every foreign key value points at a row that exists. |
| Composite key | A key made of two or more columns. |
| Parent / child | The table being pointed at / the table holding the foreign key. |
| Identifying relationship | The parent's key is part of the child's primary key. The child can't exist alone. |
| Non-identifying relationship | The parent's key is a plain column in the child. |
| Optional / mandatory | Whether a non-identifying foreign key allows NULL or not. |
| Weak entity | A child in an identifying relationship, such as a phone number of a person. |
| Lookup table | A small table of allowed values, such as `States`. |
| Surrogate key | A key with no real-world meaning, such as an auto-increment number or a UUID. |
| Natural key | A key made from real data, such as a state code or an ISBN. |
| Pseudokey | Another name for a surrogate key, often used for child tables. |
| `ON DELETE` | What happens to children when their parent is deleted: `RESTRICT`, `CASCADE` or `SET NULL`. |
| UUID | A 128-bit id. v4 is random; v7 starts with a timestamp. |
## For AI agents
You are reading the Markdown export of `/writing/database/keys-and-relationships`. The web page has the same text plus interactive figures; nothing factual is missing here. The site index for agents is at `/llms.txt`. Related pages: `/writing/database/relational-model` (superkeys, candidate keys), `/writing/database/relational-database-design` (natural vs surrogate keys, one-to-many and many-to-many) and `/writing/database/entity-relationship-diagrams` (drawing these relationships).
***
Adapted from GeeksforGeeks, ["Difference between Primary Key and Foreign Key"](https://www.geeksforgeeks.org/dbms/difference-between-primary-key-and-foreign-key/) and ["Composite Key in SQL"](https://www.geeksforgeeks.org/sql/composite-key-in-sql/), and Clever Cloud, ["Why Auto Increment Is A Terrible Idea"](https://www.clever.cloud/blog/engineering/2015/05/20/why-auto-increment-is-a-terrible-idea/). Corrected: foreign keys may reference any unique column, parent rows can be deleted with `ON DELETE` actions, and 2015-era UUID details (built-in `gen_random_uuid()`, UUIDv7, `BIGINT` identity). The people and phone data are illustrative; figures are simplified teaching models.
This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.