TopolearnKeys are what updates, deletes, foreign keys and joins use to find exactly one row.
A primary key is a column, or set of columns, whose value uniquely identifies a row. Declaring one gives you three things at once: values must be unique, they may not be NULL, and the database builds a unique index to enforce that, which also makes lookups by key fast. A table has at most one primary key.
There are two schools of key:
- A natural key is a real-world attribute, like an ISBN or a country code. Tempting, but real-world values change and are reused more often than anyone expects.
- A surrogate key is a meaningless id generated by the database. It never needs to change, which is why most schemas use one, plus a
UNIQUEconstraint on the natural attribute (such asemail).
For generated integer ids, the standard syntax, supported by PostgreSQL 10+, Oracle 12c+ and DB2, is an identity column. GENERATED ALWAYS rejects manually supplied values; GENERATED BY DEFAULT allows them. Older PostgreSQL code uses serial, which does the same job less cleanly. MySQL uses AUTO_INCREMENT; SQL Server uses IDENTITY(1,1). Identity values come from a sequence and can have gaps after rollbacks or failed inserts: never rely on them being contiguous.
uuid keys can be generated anywhere (even in the client) without coordination and do not leak row counts, at the cost of 16 bytes and, for random version-4 UUIDs, scattered index inserts. Time-ordered version-7 UUIDs avoid most of that cost.
A key can span several columns. A line in order_items is identified by the order and the product together, so its key is PRIMARY KEY (order_id, product_id).
Example
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE -- natural attribute stays unique
);
CREATE TABLE order_items (
order_id bigint NOT NULL,
product_id bigint NOT NULL,
quantity integer NOT NULL,
unit_price numeric(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id) -- composite key
);
INSERT INTO customers (name, email) VALUES ('Ana', 'ana@example.com')
RETURNING id; -- 1
INSERT INTO customers (id, name, email) VALUES (99, 'X', 'x@example.com');
-- ERROR: cannot insert a non-DEFAULT value into column "id"
-- (column is GENERATED ALWAYS)Remember
- A primary key is unique, NOT NULL and automatically indexed
- Prefer surrogate keys plus UNIQUE constraints on natural attributes
- Use GENERATED ... AS IDENTITY (standard) rather than serial
- Generated ids can have gaps; never depend on them being sequential
Watch out for
- Using a changeable natural value, such as an email address, as the primary key referenced by other tables.