PRIMARY KEY

A PRIMARY KEY constraint designates one column, or a combination of columns, as the unique identifier for every row in a table. No two rows may ever have the same primary key value, and the primary key column(s) can never be NULL — together this guarantees that any single row can always be located unambiguously.

A table may have at most one PRIMARY KEY, though that key can be composite — made up of more than one column when no single column is unique on its own.

Declaring a primary key

SQL

-- Single-column primary key, declared inline
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(100) NOT NULL,
  email       VARCHAR(255)
);

-- Composite primary key, declared as a table-level constraint
CREATE TABLE order_items (
  order_id    INT,
  line_number INT,
  product_id  INT NOT NULL,
  quantity    INT NOT NULL,
  PRIMARY KEY (order_id, line_number)
);

In order_items, neither order_id nor line_number is unique by itself — many rows can share the same order_id (one per line item), and many rows across different orders can share the same line_number (1, 2, 3...). Only the combination of the two uniquely identifies a row.

Warning

A PRIMARY KEY automatically implies both NOT NULL and UNIQUE. Attempting to insert a row with a duplicate primary key value, or a NULL primary key value, is rejected by the database with a constraint violation error — there is no need to also declare NOT NULL or UNIQUE separately on a primary key column.

Natural keys vs surrogate keys

A natural key is a primary key built from data that already has real-world meaning — an email address, a national ID number, a product SKU. A surrogate key is an artificial identifier with no business meaning at all, typically an auto-incrementing integer or a generated UUID, whose only job is to identify the row.

	Natural key	Surrogate key
Example	email, national_id, sku	auto-increment id, UUID
Meaningful to the business	Yes	No
Can change over time	Sometimes (people change emails)	Never
Storage size	Often larger (strings)	Small, fixed-size integer/UUID
Join performance	Can be slower (string comparisons)	Fast (integer comparisons)
Risk if it changes	Cascades through every foreign key referencing it	None — the key itself never needs to change

SQL

-- Natural key: risky if the SKU scheme ever changes
CREATE TABLE products_natural (
  sku   VARCHAR(20) PRIMARY KEY,
  name  VARCHAR(100) NOT NULL
);

-- Surrogate key: the SKU can still be tracked and even changed safely
CREATE TABLE products_surrogate (
  product_id  INT PRIMARY KEY,
  sku         VARCHAR(20) NOT NULL UNIQUE,
  name        VARCHAR(100) NOT NULL
);

Tip

Surrogate keys are generally recommended for primary keys, even when a natural key exists. Keep the natural key as a UNIQUE column for lookups and business logic, but use a stable, meaningless surrogate key (auto-increment or UUID) as the actual PRIMARY KEY that every foreign key in the database points to. This insulates the whole schema from real-world data changing underneath it.

Note

Most databases automatically create an index on the primary key column(s), since the primary key is so frequently used to look up and join rows. This is a performance side-effect of the constraint, not something you need to set up separately.