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 KEYafter 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_namenames 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 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.
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 key | UNIQUE | Foreign key | |
|---|---|---|---|
| Refuses a repeated value | Yes | Yes | No |
| Accepts NULL | No | Yes | Yes |
| Per table | One | Any number | Any 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_id | name |
|---|---|
| 1 | Nice |
| 2 | Lille |
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:
| Engine | The key column |
|---|---|
| PostgreSQL 18 | town_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY |
| MySQL 9.1 | town_id int AUTO_INCREMENT PRIMARY KEY |
| SQLite 3.50 | town_id INTEGER PRIMARY KEY |
| SQL Server | town_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;
| name | copies |
|---|---|
| Paris | 2 |
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_id | name | country_code |
|---|---|---|
| NULL | Nice | FR |
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.
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:
- Double-click the table header and open the Primary Key / Unique / Indexes tab.
- Press Add.
- Choose Primary Key.
- Press Add under Columns for each column of the key.
- Press OK in both dialogs.
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.

