SQL guide · Constraints

SQL PRIMARY KEY Constraint: Uniquely Identify Every Row

A PRIMARY KEY constraint marks a column, or a combination of columns, as the unique identifier for every row in a table. The database enforces two rules on those columns: no two rows may share the same key value, and no key column may contain NULL. Together these rules guarantee that you can always point at exactly one row, which is what makes updates, deletes and joins reliable.

Every table should have one primary key, and a table can never have more than one. The key can be a single column such as customer_id, or a composite key made of several columns, for example order_id and line_number in an order lines table. Behind the scenes most databases create a unique index to enforce the constraint, so lookups by primary key are fast as well as safe.

Primary keys are also the target of foreign keys. When an orders table stores a customer_id, it normally references the primary key of customers. If you are designing a schema, decide on the key first, then build relationships on top of it. Pick values that never change and carry no business meaning, such as an auto-generated integer or a UUID, rather than something like an email address that can be edited later.

Basic syntax

-- Inline, single column
CREATE TABLE table_name (
  column_name data_type PRIMARY KEY,
  ...
);

-- Named, table-level (supports multiple columns)
CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...
  CONSTRAINT pk_name PRIMARY KEY (column1, column2)
);

-- Add to an existing table
ALTER TABLE table_name
ADD CONSTRAINT pk_name PRIMARY KEY (column1);

pk_name is an optional but recommended constraint name, because a named constraint is far easier to drop or reference later. The column list in parentheses can hold one column or several. Every column listed must be declared NOT NULL, or the database will make it so for you, depending on the dialect.

SQL PRIMARY KEY examples

Single-column primary key on CREATE TABLE

The simplest form puts PRIMARY KEY right after the column definition. Each customer gets a distinct id, and inserting a duplicate id or a null id is rejected with an error. This is the pattern you will use for most lookup and entity tables.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  full_name   VARCHAR(100) NOT NULL,
  email       VARCHAR(255)
);

INSERT INTO customers (customer_id, full_name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com');

-- Fails: customer_id 1 already exists
INSERT INTO customers (customer_id, full_name, email)
VALUES (1, 'Grace Hopper', 'grace@example.com');

Named composite primary key

When no single column is unique on its own, combine columns. Here one order can contain many lines, so the pair of order_id and line_number identifies a row. Naming the constraint with CONSTRAINT pk_order_lines makes it simple to drop or rename later.

CREATE TABLE order_lines (
  order_id    INT NOT NULL,
  line_number INT NOT NULL,
  product_id  INT NOT NULL,
  quantity    INT NOT NULL,
  CONSTRAINT pk_order_lines PRIMARY KEY (order_id, line_number)
);

Auto-generated primary key

Most applications let the database generate the key. The syntax differs by engine, but the result is the same: a unique integer is assigned on insert and you never supply it. The PostgreSQL example uses the standard identity column syntax.

-- PostgreSQL 10+ / Oracle 12c+
CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       VARCHAR(120) NOT NULL,
  price      NUMERIC(10,2)
);

-- MySQL
-- product_id INT AUTO_INCREMENT PRIMARY KEY

-- SQL Server
-- product_id INT IDENTITY(1,1) PRIMARY KEY

Add a primary key to an existing table

If a table was created without a key, add one with ALTER TABLE. The columns must already hold unique, non-null values, otherwise the statement fails. Clean up duplicates first, then add the constraint.

ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);

Where SQL dialects differ

PostgreSQL: Creates a unique B-tree index named after the constraint and sets the columns to NOT NULL automatically. Use GENERATED ALWAYS AS IDENTITY (version 10 and later) or the older SERIAL type for auto-increment keys. Drop with ALTER TABLE t DROP CONSTRAINT pk_name.

MySQL: Uses AUTO_INCREMENT for generated keys. In InnoDB the primary key is the clustered index, so a short key keeps every secondary index smaller. The constraint is always named PRIMARY, so you drop it with ALTER TABLE t DROP PRIMARY KEY, not by custom name.

SQL Server: The key columns must be declared NOT NULL explicitly when adding to an existing table. By default the primary key creates a clustered index; use PRIMARY KEY NONCLUSTERED if you want the clustered index elsewhere. Generated keys use IDENTITY(seed, increment).

SQLite: A single column declared INTEGER PRIMARY KEY becomes an alias for the internal rowid and auto-assigns values. Unlike other engines, SQLite historically allowed NULL in non-integer primary key columns unless you add NOT NULL, or declare the table STRICT or WITHOUT ROWID. You cannot add a primary key with ALTER TABLE; recreate the table instead.

Oracle: Supports CONSTRAINT name PRIMARY KEY inline or in ALTER TABLE. Identity columns are available from 12c; earlier versions used a sequence plus a trigger. Oracle creates a unique index to support the key.

Common mistakes

Using a mutable business value, such as an email or phone number, as the key. When it changes, every foreign key pointing at it must change too, so prefer a stable surrogate id.
Trying to define two primary keys on one table. Only one is allowed; if you need another unique column, use a UNIQUE constraint instead.
Adding a primary key to a table that already contains duplicate or null values. The ALTER TABLE fails until you find and fix those rows.
Forgetting that a composite key is one key. Declaring PRIMARY KEY (a), PRIMARY KEY (b) is an error, and PRIMARY KEY (a, b) only forbids duplicate pairs, not duplicate values in a single column.
Assuming the key name is portable. MySQL ignores the name you give it, so scripts that drop the key by name will break when moved between engines.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator