Keys and Relationships

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. For choosing between natural and surrogate keys, see Designing a Relational Database.

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.

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. 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. 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.

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:

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. 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

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

Adapted from GeeksforGeeks, “Difference between Primary Key and Foreign Key” and “Composite Key in SQL”, and Clever Cloud, “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.

All posts

Frontend

Database

Keys and Relationships

The example

Primary key vs foreign key

Composite keys

Identifying vs non-identifying relationships

Optional or mandatory

The pseudokey shortcut

What the key value is made of

What to pick

Check yourself

Glossary

For AI agents

​The example

​Primary key vs foreign key

​Composite keys

​Identifying vs non-identifying relationships

​Optional or mandatory

​The pseudokey shortcut

​What the key value is made of

​What to pick

​Check yourself

​Glossary

​For AI agents

The example

Primary key vs foreign key

Composite keys

Identifying vs non-identifying relationships

Optional or mandatory

The pseudokey shortcut

What the key value is made of

What to pick

Check yourself

Glossary

For AI agents