Keys: Natural, Surrogate & UUID

A primary key is the database's answer to "which one row do you mean?" The hard part is choosing the shape of that answer: a real-world value, a generated number, a UUID, or a combination of columns.

There is no universal winner. Good key design starts with two questions: will this value stay stable for the row's whole life, and who needs to create it?

Natural keys: real facts as identity

A natural key is a key made from data the business already uses. A cafe SKU can be a natural key if the cafe controls it and promises it will not be reused:

CREATE TABLE store_items (
  sku text PRIMARY KEY,
  item_name text NOT NULL,
  price numeric(5,2) NOT NULL
);
 
INSERT INTO store_items (sku, item_name, price)
VALUES
  ('COF-ESP-250', 'Espresso Beans 250g', 12.50),
  ('TEA-EARL-100', 'Earl Grey Tin', 8.75);
 
SELECT sku, item_name, price
FROM store_items
ORDER BY sku;

CREATE TABLE
INSERT 0 2
     sku      |      item_name      | price
--------------+---------------------+-------
 COF-ESP-250  | Espresso Beans 250g | 12.50
 TEA-EARL-100 | Earl Grey Tin       |  8.75
(2 rows)

The upside is readability: every foreign key that stores COF-ESP-250 tells you something without a join. The risk is stability. If the SKU format changes, if a vendor owns the code, or if humans edit it to fix naming, every referencing row depends on a fact that moved.

Use a natural key when the value is short, stable, controlled by your system, and genuinely identifies the row. Do not use a natural key just because the column is visible to humans.

Surrogate keys and identity columns

A surrogate key is a made-up identity value with no business meaning. It exists only to name the row. That is why the course keeps calling primary keys "meaningless, permanent identity": the less the key means, the less reason it has to change.

In modern PostgreSQL, the normal generated-number spelling is an identity column, a column PostgreSQL fills from a sequence, a database object that hands out increasing numbers:

CREATE TABLE counter_orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  receipt_code text NOT NULL UNIQUE,
  customer_name text NOT NULL
);
 
INSERT INTO counter_orders (receipt_code, customer_name)
VALUES
  ('R-2026-0001', 'Ada'),
  ('R-2026-0002', 'Ken')
RETURNING order_id, receipt_code, customer_name;

CREATE TABLE
 order_id | receipt_code | customer_name
----------+--------------+---------------
        1 | R-2026-0001  | Ada
        2 | R-2026-0002  | Ken
(2 rows)

GENERATED ALWAYS AS IDENTITY says PostgreSQL owns order_id. You still keep business values like receipt_code, but they become UNIQUE facts, not the row's permanent identity.

You will still see SERIAL in older PostgreSQL code. It works, but it is legacy shorthand for "make an integer column, make a sequence, wire the default." New tables should use identity columns because the generated behavior belongs directly in the column definition.

Natural and surrogate keys are not enemies. A common production pattern is both: a surrogate primary key for joins and a unique natural value for the business rule.

UUIDs: distributed keys with a cost

A UUID is a 128-bit identifier designed to be unique across systems without asking one central database for the next number. That makes UUIDs useful when rows are created on many devices, services, shards, or offline clients.

PostgreSQL 18 can generate both UUIDv4, a random UUID version, and UUIDv7, a time-ordered UUID version. The actual values are nondeterministic, so the lesson verifies their stable properties instead:

SELECT pg_typeof(uuidv7()) AS v7_type,
       uuid_extract_version(uuidv7()) AS v7_version,
       uuid_extract_version(gen_random_uuid()) AS v4_version,
       pg_column_size(uuidv7()) AS uuid_bytes,
       pg_column_size(1::bigint) AS bigint_bytes,
       char_length(uuidv7()::text) AS uuid_text_chars;

 v7_type | v7_version | v4_version | uuid_bytes | bigint_bytes | uuid_text_chars
---------+------------+------------+------------+--------------+-----------------
 uuid    |          7 |          4 |         16 |            8 |              36
(1 row)

That grid is the trade. UUIDs work without coordination, but the stored value is larger than a bigint, and its text form is long. A bigint identity remains the smallest, fastest default when one PostgreSQL database creates the rows.

Use UUIDs when the ability to generate ids outside one database matters more than compactness and easy reading. Use identity numbers when the database can be the id factory.

Composite keys: the honest pair

A composite key is a key made from more than one column. It is the honest shape when no single column identifies the row, but the combination does.

Workshop signups are a clean example. A customer can join many workshops, and a workshop can have many customers, but the same customer should not sign up for the same workshop twice:

CREATE TABLE workshop_customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_name text NOT NULL
);
 
CREATE TABLE workshops (
  workshop_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL UNIQUE
);
 
CREATE TABLE workshop_signups (
  customer_id bigint NOT NULL REFERENCES workshop_customers(customer_id),
  workshop_id bigint NOT NULL REFERENCES workshops(workshop_id),
  signed_up_on date NOT NULL,
  PRIMARY KEY (customer_id, workshop_id)
);
 
INSERT INTO workshop_customers (customer_name)
VALUES ('Ada'), ('Ken');
 
INSERT INTO workshops (title)
VALUES ('Latte Art'), ('Home Espresso');
 
INSERT INTO workshop_signups (customer_id, workshop_id, signed_up_on)
VALUES
  (1, 1, DATE '2026-05-20'),
  (1, 2, DATE '2026-05-21'),
  (2, 1, DATE '2026-05-20');
 
SELECT wc.customer_name,
       w.title,
       ws.signed_up_on
FROM workshop_signups AS ws
JOIN workshop_customers AS wc
  ON wc.customer_id = ws.customer_id
JOIN workshops AS w
  ON w.workshop_id = ws.workshop_id
ORDER BY wc.customer_name, w.title;

CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 2
INSERT 0 3
 customer_name |     title     | signed_up_on
---------------+---------------+--------------
 Ada           | Home Espresso | 2026-05-21
 Ada           | Latte Art     | 2026-05-20
 Ken           | Latte Art     | 2026-05-20
(3 rows)

Composite keys are especially natural in junction tables, where the row is the relationship itself. If other tables need to reference a signup often, a surrogate signup_id may still be worth adding, but do not hide the real uniqueness rule: keep UNIQUE (customer_id, workshop_id) either way.

Key choice is about stability, scope, and the shape of the relationship. Next, 9.4 shows what happens when your first design is not quite right: changing a live schema with ALTER TABLE without pretending production tables are toys.