Primary key

What is a primary key?

A primary key is the column, or set of columns, that uniquely identifies every row in a table. In a customer table that is usually a customer_id: each customer gets exactly one value, and no two customers ever share it.

In SQL, a primary key is more than a note in your head about which column is the identifier. The database enforces it. Once you declare a primary key, the engine guarantees two things on every insert and update: the value is never empty (not null) and never repeats (unique). A row with no key value is rejected, and so is a duplicate. That is what lets you trust the key when you join tables or point one record at another.

A table has at most one primary key. It is the row's official identity, closer to a membership number than to a name. Names collide and addresses change, but the membership number stays put and belongs to one member.

Declaring a primary key in SQL

The simplest form marks a single column as the primary key inside the table definition.

CREATE TABLE customers (
  customer_id integer PRIMARY KEY,
  name        text NOT NULL,
  email       text
);

When the key spans more than one column, you write it as a separate clause at the end. That is the only way to declare a multi-column key. The column-level PRIMARY KEY attribute can mark just one column, so you cannot use it to combine two.

CREATE TABLE order_items (
  order_id   integer NOT NULL,
  product_id integer NOT NULL,
  quantity   integer NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

A key built from several columns is a composite key. Here order 5000 can appear on many lines and product 42 can sit on many orders, but the pair (5000, 42) may show up only once. The database checks the combination, not each column on its own.

Two things happen automatically when you declare the key. The engine marks the columns not null if you did not, and it builds a unique database index behind the key so the uniqueness check stays fast. In SQL Server that index is clustered by default, which also fixes the physical order of the rows on disk.

Primary key versus the keys it gets confused with

Several related ideas sit close to the primary key, and mixing them up leads to real modelling mistakes.

Candidate key

A table can have more than one set of columns that could each identify every row on its own. Each of those is a candidate key. The primary key is just the candidate you promote to be the official identifier. A customer table might hold both customer_id and a unique national tax number as candidates; you name one as the primary key and the others stay as alternates.

Unique constraint

A unique constraint also blocks duplicate values, so at first glance the two look the same. The differences are precise. A unique constraint allows null values, and in many databases two nulls count as different, so more than one row can leave the column empty. A primary key forbids null outright. A table can also carry any number of unique constraints but only one primary key. A common setup is a customer table with customer_id as the primary key and a separate unique constraint on email: the address must not duplicate, but it is allowed to be missing and it can change.

Foreign key

A foreign key is a column in another table that points back at this table's primary key. An order row stores its buyer as a foreign key referencing customers.customer_id. That reference only holds if the target has a stable primary key to point at, which is why the primary key has to exist first.

Natural versus surrogate keys

When the identifier is a real value the data already carries, such as an ISBN or a VAT number, it is a natural key, often called a business key. When you invent a meaningless number purely to identify the row, usually an auto-incrementing integer, it is a surrogate key. Either can be the primary key. Surrogate keys are popular because they never have to change when the business facts do.

Why a primary key matters

• Blocking duplicates at the door. The database refuses a second row with the same key. That is a hard limit on the way in, not a cleanup job you run afterwards, so it protects data quality earlier than any downstream check can.

• Incremental loads. A data pipeline can tell new rows from changed ones by their key, which is what makes an upsert or a merge work on every run.

Choosing a primary key that lasts

The technical rules are easy to satisfy. The judgement is in picking a key that stays true over time.

Pick something stable. A value that changes later, like an email address or a phone number, makes a poor primary key, because every row that referenced it has to change along with it. That is the main reason teams reach for a surrogate key instead of a natural one.

Keep identity apart from meaning. A key answers which row this is, not what the row means. Descriptive facts belong in ordinary columns, so that fixing a typo in a name never disturbs the identifier the rest of the database leans on.

The hard case is data from more than one system. Two sources can each hand you a customer 123 without meaning the same person. When you merge them in a data warehouse you normally fold the source system into the key, or mint a fresh surrogate key, so the two identities never collide.