Back to all posts

What Is a Primary Key in SQL? Definition and Examples (2026)

For SQL beginners writing their first CREATE TABLE statements, who have to choose the column that identifies each row.

On this page

A primary key is a column, or a group of columns, that identifies each row of a table. For example, each city in a cities table can get its own id number. The database refuses any row whose key is empty or already taken.

Give every table a primary key. Without one, a table can hold two rows for the same city. Then an UPDATE can't change just one of the two rows, and another table can't point at just one.

You declare a primary key in CREATE TABLE, in one of two forms:

CREATE TABLE table_name (
  column_name data_type PRIMARY KEY,
  ...
);

CREATE TABLE table_name (
  column_1 data_type,
  column_2 data_type,
  CONSTRAINT constraint_name PRIMARY KEY (column_1, column_2)
);
  • PRIMARY KEY after a column makes that one column the key.
  • PRIMARY KEY (column_1, column_2), written after the columns, makes one or several columns the key.
  • CONSTRAINT constraint_name names the key. A constraint is a rule that the database checks. Without this part, the database picks a name.

Create the sample table, cities, with four rows. You create other tables later, in the examples that use them.

CREATE TABLE cities (
  city_id      int PRIMARY KEY,
  name         varchar(30),
  country_code char(2)
);

INSERT INTO cities VALUES
  (1, 'Paris',  'FR'),
  (2, 'Lyon',   'FR'),
  (3, 'Paris',  'US'),
  (4, 'Austin', 'US');

Every statement ran on PostgreSQL 18, MySQL 9.1, and SQLite 3.50, with the same outcome except where a database is named. The errors shown are PostgreSQL's. The SQL Server claims come from Microsoft's documentation only.

Practice this in Lesson 8 of the SQL tutorial: on a shop database in your browser, you see which rows a primary key and a foreign key refuse, with exercises.

PRIMARY KEY

PRIMARY KEY makes the database refuse a row whose key repeats a value or is empty. For example, you can try to add a city with city_id 2, and a city with no city_id.

INSERT INTO cities VALUES (2, 'Nice', 'FR');
INSERT INTO cities VALUES (NULL, 'Nice', 'FR');

Both rows are refused:

ERROR:  duplicate key value violates unique constraint "cities_pkey"
DETAIL:  Key (city_id)=(2) already exists.
ERROR:  null value in column "city_id" of relation "cities" violates not-null constraint
DETAIL:  Failing row contains (null, Nice, FR).

Lyon already has city_id 2, so Nice can't take that number. The second row's city_id is NULL, which means empty. PostgreSQL 18 marks every key column NOT NULL, so the column refuses NULL even where CREATE TABLE doesn't say so.

The cities table with city_id as its primary key; the row (2, Nice, FR) is refused because Lyon has city_id 2, and the row (NULL, Nice, FR) is refused because its key is empty

The database checks each new row against a unique index of the key, a lookup structure that holds each value once. In PostgreSQL the index has the key's name, cities_pkey. In MySQL, SHOW INDEX FROM cities lists the index as PRIMARY.

MySQL refuses both rows too. SQLite accepts the row with a NULL city_id (see What goes wrong).

Composite primary key

A composite primary key is one key over several columns. For example, a table of city names can use name and country code together as its key.

In cities, Paris appears twice and FR appears twice. No pair of name and country_code appears twice, so the pair can be the key:

CREATE TABLE city_names (
  name         varchar(30),
  country_code char(2),
  PRIMARY KEY (name, country_code)
);

INSERT INTO city_names VALUES
  ('Paris', 'FR'),
  ('Paris', 'US');

INSERT INTO city_names VALUES ('Paris', 'FR');

The last INSERT fails:

ERROR:  duplicate key value violates unique constraint "city_names_pkey"
DETAIL:  Key (name, country_code)=(Paris, FR) already exists.

The first two rows share a name but not a country, so both pairs are new. The third row repeats the pair (Paris, FR) and is refused.

If you write PRIMARY KEY after each of the two columns, you ask for two primary keys, and CREATE TABLE fails:

CREATE TABLE city_pairs (
  name         varchar(30) PRIMARY KEY,
  country_code char(2) PRIMARY KEY
);
ERROR:  multiple primary keys for table "city_pairs" are not allowed

MySQL refuses this CREATE TABLE with "Multiple primary key defined".

ALTER TABLE ADD PRIMARY KEY

ALTER TABLE ADD PRIMARY KEY adds a primary key to a table that has none. For example, you can add a key named pk_countries to a countries table:

CREATE TABLE countries (
  country_code char(2),
  name         varchar(30)
);

INSERT INTO countries VALUES
  ('FR', 'France'),
  ('US', 'United States');

ALTER TABLE countries
  ADD CONSTRAINT pk_countries PRIMARY KEY (country_code);

INSERT INTO countries VALUES ('FR', 'Francia');

The ALTER TABLE succeeds, and the last INSERT fails with the key's name in the error:

ERROR:  duplicate key value violates unique constraint "pk_countries"
DETAIL:  Key (country_code)=(FR) already exists.

France already has FR, so Francia is refused. To drop the key, use its name: ALTER TABLE countries DROP CONSTRAINT pk_countries.

MySQL ignores the name pk_countries, because MySQL names every primary key PRIMARY. On MySQL, ALTER TABLE countries DROP PRIMARY KEY drops the key.

In SQLite, declare keys in CREATE TABLE. SQLite 3.50 rejects every ALTER TABLE in this article, because its ALTER TABLE can't add a constraint.

FOREIGN KEY

A foreign key column holds values of another table's primary key. Each value points at one row of the other table. For example, each city's country_code can point at a row of countries.

REFERENCES is followed by the table and the key column to point at:

ALTER TABLE cities
  ADD FOREIGN KEY (country_code) REFERENCES countries (country_code);

INSERT INTO cities VALUES (5, 'Berlin', 'DE');

The foreign key is added, and the INSERT fails:

ERROR:  insert or update on table "cities" violates foreign key constraint "cities_country_code_fkey"
DETAIL:  Key (country_code)=(DE) is not present in table "countries".

Every city so far has FR or US, and both codes are in countries. Berlin's DE is not in countries, so Berlin is refused.

Each row of cities stores FR or US in country_code, which points at the row of countries with that primary key value; the row (5, Berlin, DE) is refused because countries has no DE

PostgreSQL 18 accepts a foreign key only to a primary key or to UNIQUE columns. country_code is the primary key of countries, so this foreign key works.

While the foreign key exists, dropping pk_countries fails with "cannot drop constraint pk_countries on table countries because other objects depend on it". For more on foreign keys, read What Is a Foreign Key?

UNIQUE and natural keys

UNIQUE refuses a repeated value, as a primary key does. For example, you can make cities refuse a second row for Paris, FR.

ALTER TABLE cities
  ADD CONSTRAINT uq_cities_name UNIQUE (name, country_code);

INSERT INTO cities VALUES (5, 'Paris', 'FR');

The INSERT fails on the UNIQUE constraint, not on the key:

ERROR:  duplicate key value violates unique constraint "uq_cities_name"
DETAIL:  Key (name, country_code)=(Paris, FR) already exists.

city_id 5 is new, so the primary key alone would have accepted a second Paris, France. The UNIQUE constraint on the pair refuses the row. Use UNIQUE on every other value that must not repeat.

city_id is a surrogate key: a number with no meaning outside the table. country_code in countries is a natural key, a value that exists outside the database.

If a natural value is short and never changes, it can be the key. Otherwise, choose a surrogate key and put UNIQUE on the natural value, as cities does. An email address or a product code can change, and every table that stores the old value then changes too.

The three constraints compare like this:

Primary keyUNIQUEForeign key
Refuses a repeated valueYesYesNo
Accepts NULLNoYesYes
Per tableOneAny numberAny number

PostgreSQL, MySQL and SQLite accept any number of NULLs in a UNIQUE column. SQL Server accepts only one NULL.

AUTO_INCREMENT and IDENTITY

A surrogate key usually gets its numbers from the database. For example, PostgreSQL numbers the rows of a towns table with GENERATED ALWAYS AS IDENTITY:

CREATE TABLE towns (
  town_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name    varchar(30)
);

INSERT INTO towns (name) VALUES ('Nice'), ('Lille');

SELECT * FROM towns;
town_idname
1Nice
2Lille

The INSERT gave only the names, and PostgreSQL numbered the rows.

Each database writes the key column in its own form. MySQL and SQLite returned the same two rows. The SQL Server form comes from Microsoft's documentation:

EngineThe key column
PostgreSQL 18town_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
MySQL 9.1town_id int AUTO_INCREMENT PRIMARY KEY
SQLite 3.50town_id INTEGER PRIMARY KEY
SQL Servertown_id int IDENTITY(1,1) PRIMARY KEY

What goes wrong

A repeated value stops ALTER TABLE

ALTER TABLE ADD PRIMARY KEY fails when the column already holds a value twice. For example, the city_list table holds Paris twice:

CREATE TABLE city_list (
  name varchar(30)
);

INSERT INTO city_list VALUES ('Paris'), ('Lyon'), ('Paris');

ALTER TABLE city_list ADD PRIMARY KEY (name);
ERROR:  could not create unique index "city_list_pkey"
DETAIL:  Key (name)=(Paris) is duplicated.

Find the repeated values before you add the key:

SELECT name, count(*) AS copies
FROM city_list
GROUP BY name
HAVING count(*) > 1;
namecopies
Paris2

After you delete or change one Paris row, the ALTER TABLE goes through. A NULL in name stops the ALTER TABLE too, and WHERE name IS NULL finds it.

SQLite accepts NULL keys

On SQLite, the Nice row with a NULL city_id went in. This query finds it:

SELECT * FROM cities WHERE city_id IS NULL;
city_idnamecountry_code
NULLNiceFR

SQLite allows NULL in most primary key columns. Early versions allowed NULL by mistake. SQLite keeps the mistake, so old applications still work.

To make SQLite refuse the row, declare the column NOT NULL, or make the table STRICT or WITHOUT ROWID. An INTEGER PRIMARY KEY column fills a NULL with the next number instead.

Primary keys in DbSchema

DbSchema connects to a database and draws its tables on a diagram, with a key icon on every primary key column. A table with no key icon has no primary key. On the cities and countries tables, DbSchema shows both keys and draws the foreign key as a line.

The cities and countries tables on a DbSchema diagram, with a key icon on city_id and on country_code, and the foreign key drawn as a line from cities to countries

To make one column the key, double-click the table header. In the Edit Table dialog, open the column's dialog from the Columns tab, and tick Primary Key.

For a composite key, follow Set the primary key:

  1. Double-click the table header and open the Primary Key / Unique / Indexes tab.
  2. Press Add.
  3. Choose Primary Key.
  4. Press Add under Columns for each column of the key.
  5. Press OK in both dialogs.
The DbSchema column dialog of city_id in the cities table, with Primary Key ticked

While DbSchema is connected, OK runs the change in the database at once. After Database → Offline (connect later), the change stays in the DbSchema model until you synchronize the model with the database. Synchronization is part of DbSchema Pro.

Download DbSchema at https://dbschema.com/download.html, connect it to PostgreSQL, MySQL or SQLite, and look on the diagram for tables without a key icon. The free Community Edition includes connecting, the diagram, and editing tables.

DbSchema ER diagram designer DbSchema ER diagram designer

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download