Primary Keys and Uniqueness
Understand primary keys, surrogate vs natural keys, composite keys, and why every row needs a unique identifier you can trust.
Why Every Row Needs a Unique ID
To update or delete a specific row safely, you must be able to point at it unambiguously.
- Two users named "Alice Adams" can exist
- But two users with
id = 42must NOT exist - The unique identifier is called the primary key
Declaring a Primary Key
You mark a column as the primary key when you create the table:
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
sku VARCHAR(50) NOT NULL,
name VARCHAR(200) NOT NULL,
price NUMERIC(10,2) NOT NULL
);