Keys & Identity

Every table needs a way to point at exactly one row. That way is a key. A key is a column, or a group of columns, whose value finds one row.

In chapter 2 you drew entities and the relationships between them. This chapter makes those relationships real. You will choose keys that stay correct when the data changes.

The shop again

This chapter uses the shop from the SQL chapters, and the SQL examples use PostgreSQL. One thing is new: customers now has an email column. Here is all the data.

What you will learn

1. What a primary key is, and how to check one.
2. Natural and surrogate keys, and why most tables use both.
3. What breaks when a business id changes, repeats or is reused.
4. Why a surrogate key stays private.
5. Composite keys, foreign keys and delete rules.
6. How one person with many ids becomes one identity.

You met primary keys in the SQL chapters. Here you check one.

A primary key is the column that officially identifies each row. It follows two rules. Every value is unique, and no value is NULL.

NULL means a value is missing.

In orders, order_id is the primary key. Here is the table in SQL:

CREATE TABLE orders (
  order_id    BIGINT        PRIMARY KEY,
  customer_id BIGINT        NOT NULL,
  status      TEXT          NOT NULL,
  amount      NUMERIC(10,2),
  order_date  DATE          NOT NULL
);

Here is what each part does:

• BIGINT is a whole number. TEXT is words. NUMERIC(10,2) is a number with two decimals, good for money.
• PRIMARY KEY on order_id means unique and not NULL. The database checks both on every insert.
• NOT NULL means the value must be there. amount has no NOT NULL, so order 105 can have no amount yet.

Now try to add a second order 101:

INSERT INTO orders (order_id, customer_id, status, amount, order_date)
VALUES (101, 2, 'paid', 10.00, '2026-02-01');

This error is the point of a key. There can be only one row for order 101.

Candidate keys

Sometimes more than one column could be the key. Each one is a candidate key. In customers, customer_id and email are both unique, so both are candidates.

You choose one candidate as the primary key. You give the others a UNIQUE rule. A UNIQUE rule refuses duplicates, but it is not the primary key.

A column that repeats today cannot be a key. Find the countries that more than one customer has. HAVING keeps only the groups that pass its test.

SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
HAVING COUNT(*) > 1;

A natural key is a real value from the business. An email, a passport number and a product code are natural keys. People already know them.

A surrogate key is an id that the database makes. Surrogate means a substitute. It stands in for the real value and has no meaning, like 1, 2, 3.

Compare the two kinds for one customer:

Most tables use both. The surrogate key is the primary key, and the natural key gets a UNIQUE rule.

Here is customers built that way:

CREATE TABLE customers (
  customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email       TEXT NOT NULL UNIQUE,
  name        TEXT NOT NULL,
  country     TEXT
);

• GENERATED ALWAYS AS IDENTITY means the database writes the next number. ALWAYS means you cannot type your own.
• PRIMARY KEY makes customer_id the official key.
• UNIQUE on email means no two customers share an email.

Now add Asha. You do not give an id. RETURNING shows the row that was made.

INSERT INTO customers (email, name, country)
VALUES ('asha@example.com', 'Asha', 'IN')
RETURNING customer_id, email;

A natural key looks safe until its value changes. Asha gets a new email. We test two designs.

A foreign key is a column that must match a key in another table.

In design 1, orders points at customers by email:

CREATE TABLE orders (
  order_id       BIGINT PRIMARY KEY,
  customer_email TEXT   NOT NULL REFERENCES customers (email)
);

Now Asha changes her email:

UPDATE customers
SET email = 'asha.k@example.com'
WHERE email = 'asha@example.com';

You must now change the email in customers and in orders together. Every other table that stores the email needs the same change. Miss one, and its rows no longer match the customer.

In the second design, orders stores customer_id. The email lives in one place only.

UPDATE customers
SET email = 'asha.k@example.com'
WHERE customer_id = 1;

The UNIQUE rule on email still works. Asha can have the new email, but nobody else can take it.

A business value can break in two more ways. It can be given to a new person. Or two systems can use the same value for different people.

A reused id

A source system deletes Dev. Later it gives his number, 4, to a new customer called Fay. Your copy of order 106 still says customer_no 4.

SELECT o.order_id, c.name
FROM orders_copy AS o
JOIN customers_source AS c
  ON c.customer_no = o.customer_no;

Your own surrogate key is the fix, if you use it the right way.

1. When you first load Dev, you give Dev your own key, for example 4.
2. You save that key in your copy of order 106, not the source number.
3. When the source gives number 4 to Fay, you see she is a new person. She gets a new key, such as 6.

Order 106 still points at Dev. Compare the two designs:

Two systems, the same id

The web shop and the mobile app each count customers from 1. Both have a customer 1, and they are different people.

Mia exists only in this example.

SELECT source_id, COUNT(*) AS rows_found
FROM all_customers
GROUP BY source_id
HAVING COUNT(*) > 1;

The pair (source_system, source_id) is unique. A key made of two or more columns is a composite key. You also add a surrogate key of your own.

CREATE TABLE customer_identity (
  customer_key  BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source_system TEXT NOT NULL,
  source_id     TEXT NOT NULL,
  UNIQUE (source_system, source_id)
);

You can skip this section on a first read.

A surrogate key is an id for inside use. Keep it inside your system. Do not hand it to customers or partners as the id they use.

If you share it, it becomes a business key. You can then never change it and never reuse it. There are three reasons not to share it.

1. Other people copy it. A customer writes “order 101” in an email. A partner saves it in a file. You cannot change it now.
2. It changes if you reload the table (see the reload section). The database can give out different numbers.
3. It is easy to guess. If the page /orders/101 works, someone will try /orders/102. Guessable ids can leak data if the page does not check who is asking.

Give the outside world its own id. It has one job: to be seen outside.

A UUID is a long random id. gen_random_uuid() makes one, in PostgreSQL 13 and later. DEFAULT fills it for every row. Add a public id to orders:

ALTER TABLE orders
  ADD COLUMN public_id UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE;

SELECT order_id, public_id
FROM orders
WHERE order_id IN (101, 102);

Never reuse a surrogate id. PostgreSQL never gives out a used number again, unless you reset it on purpose. Even a failed insert uses a number.

Gaps in the numbers are normal. Do not fill them.

Sometimes no single column is unique. Look at order_items. Order 101 appears twice, and Pen appears twice.

The pair (order_id, product) is unique. An order has each product at most once.

You saw a composite key in the Mia example. This pair is another one.

CREATE TABLE order_items (
  order_id BIGINT NOT NULL,
  product  TEXT   NOT NULL,
  quantity INT    NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product)
);

• PRIMARY KEY (order_id, product) means the pair is unique. Neither column has to be unique alone.
• CHECK (quantity > 0) refuses a quantity of zero or less.
• Order 101 may have Pen and Notebook. Order 104 may also have Pen. Order 101 may not have Pen twice.

Test it. Add Pen to order 101 again:

INSERT INTO order_items (order_id, product, quantity)
VALUES (101, 'Pen', 5);

The key also says what one row means. Here, one row is one product in one order. This is the grain, and chapter 4 explains it fully.

You met the foreign key in the SQL chapters. Now we give it a rule.

The table with the foreign key is the child. The table it points to is the parent. Here orders is the child and customers is the parent.

Each order points at one customer. The two customer_id values match:

The foreign key says every order belongs to a customer who exists. Here is orders again, with the link added:

CREATE TABLE orders (
  order_id    BIGINT        PRIMARY KEY,
  customer_id BIGINT        NOT NULL REFERENCES customers (customer_id),
  status      TEXT          NOT NULL,
  amount      NUMERIC(10,2),
  order_date  DATE          NOT NULL
);

The database checks the link on every write. This guarantee is called referential integrity. Try to add an order for customer 9:

INSERT INTO orders (order_id, customer_id, status, amount, order_date)
VALUES (107, 9, 'paid', 30.00, '2026-02-02');

Without a foreign key you can get an orphan. An orphan is a child row whose parent does not exist. Joins drop orphans without any error, so totals quietly go wrong.

The check works in the other direction too. Try to delete Asha:

DELETE FROM customers
WHERE customer_id = 1;

The last delete was refused. You can choose a different rule for each foreign key.

The rule is called a referential action. There are three:

If you set no rule, the default is NO ACTION. Here it acts like RESTRICT.

The coupon_id column is new. It is only for this example. Here is each rule in SQL:

-- RESTRICT: a customer with orders cannot be deleted.
customer_id BIGINT NOT NULL REFERENCES customers (customer_id) ON DELETE RESTRICT

-- CASCADE: deleting an order deletes its items too.
order_id BIGINT NOT NULL REFERENCES orders (order_id) ON DELETE CASCADE

-- SET NULL: deleting a coupon keeps the order. coupon_id must allow NULL.
coupon_id BIGINT REFERENCES coupons (coupon_id) ON DELETE SET NULL

First see SET NULL. Orders 101 and 102 have coupons. Delete coupon 7:

DELETE FROM coupons WHERE coupon_id = 7;

SELECT order_id, coupon_id
FROM orders
WHERE order_id IN (101, 102)
ORDER BY order_id;

Now see CASCADE on the shop. Delete order 101 when order_items has CASCADE:

DELETE FROM orders WHERE order_id = 101;

SELECT * FROM order_items ORDER BY order_id, product;

Real data is messy. Never trust a key until you check it. The check is the same each time: group by the key, and look for any count above one.

Check order_id in orders:

SELECT order_id, COUNT(*) AS rows_found
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

Now see what a broken key does. A bad load copied Asha twice into customers_load. This table has no key rule, so no error appears.

SELECT c.name, SUM(o.amount) AS total
FROM customers_load AS c
JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE c.customer_id = 1
GROUP BY c.name;

You can skip this section on a first read.

A warehouse reloads data often. An IDENTITY column gives numbers in the order rows arrive. A reload can give the same row a different number.

Here customers_dim holds yesterday’s rows. A dimension table is a table of descriptions, such as customers.

staging_customers holds today’s load. The rows arrive in the other order:

TRUNCATE empties the table, and RESTART IDENTITY sets the numbers back to 1. Then INSERT ... SELECT copies the staging rows in:

TRUNCATE customers_dim RESTART IDENTITY;

INSERT INTO customers_dim (email)
SELECT email FROM staging_customers;

No error appears. The reports just show the wrong person. A key made from the row itself fixes this, and the next section builds one.

You can skip this section on a first read.

A hash key avoids renumbering. A hash function turns any text into a short code. The same text always gives the same code. md5 gives 32 letters and numbers.

In PostgreSQL, || joins text together. ::text turns a number into text. So order_id::text || '|' || product makes 101|Pen.

Build a key for each row of order_items from its natural key, the pair (order_id, product):

SELECT order_id, product,
       md5(order_id::text || '|' || product) AS item_key
FROM order_items
WHERE order_id IN (101, 102)
ORDER BY order_id, product;

The | between the parts matters. Imagine order_items had a line_no column. Compare two different rows: (1, 12) and (11, 2).

SELECT order_id, line_no,
       md5(order_id::text || line_no::text)         AS without_bar,
       md5(order_id::text || '|' || line_no::text)  AS with_bar
FROM (VALUES (1, 12), (11, 2)) AS t(order_id, line_no);

If any part is NULL, the joined text is NULL. COALESCE swaps NULL for a value you choose. Wrap each part in it:

SELECT md5(COALESCE(order_id::text, '') || '|' || COALESCE(product, '')) AS item_key
FROM order_items
WHERE order_id = 101 AND product = 'Pen';

You can skip this section on a first read.

A visitor opens the shop website. They are not logged in, so the site gives them an anonymous id, kept in a cookie. A cookie is a small file the browser stores.

Later, the visitor logs in. Now you also know their customer_id. The same person has two ids, and the events before login look like a different person.

Here are five events. Visitor v-77a is Asha. She logs in at event 3.

First, count people the simple way. Use customer_id when it exists, and anonymous_id when it does not.

SELECT COUNT(DISTINCT COALESCE(customer_id::text, anonymous_id)) AS people
FROM events;

Stitching fixes this. Stitching means linking the anonymous id to the customer id. Events 3 and 4 show both ids together, so they give the link.

WITH id_map AS (
  SELECT DISTINCT anonymous_id, customer_id
  FROM events
  WHERE customer_id IS NOT NULL
)
SELECT COUNT(DISTINCT COALESCE(m.customer_id::text, e.anonymous_id)) AS people
FROM events AS e
LEFT JOIN id_map AS m
  ON m.anonymous_id = e.anonymous_id;

Here is what stitching does to each event:

This only works when each anonymous id belongs to one customer. A shared computer can break it. Check this first:

SELECT anonymous_id
FROM events
WHERE customer_id IS NOT NULL
GROUP BY anonymous_id
HAVING COUNT(DISTINCT customer_id) > 1;

In a bigger model, a person gets a surrogate key of their own. A second table lists every id that belongs to that person.

CREATE TABLE person_identifier (
  person_key BIGINT NOT NULL,
  id_type    TEXT   NOT NULL,
  id_value   TEXT   NOT NULL,
  PRIMARY KEY (id_type, id_value)
);

Five design exercises for this chapter. You build each one on the ERD canvas. A review then checks it against sample rows.

Try them with the chapter closed. If one goes wrong, go back to the section named in its prompt.