# SQL PRIMARY KEY Constraint: Uniquely Identify Every Row

- URL: https://betteranalyst.com/sql/primary-key

SQL guide · Constraints

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

- [SQL FOREIGN KEY](https://betteranalyst.com/sql/foreign-key)
- [SQL UNIQUE](https://betteranalyst.com/sql/unique)
- [SQL NOT NULL](https://betteranalyst.com/sql/not-null)
- [SQL CREATE TABLE](https://betteranalyst.com/sql/create-table)
- [SQL ALTER TABLE](https://betteranalyst.com/sql/alter-table)
- [SQL ADD CONSTRAINT](https://betteranalyst.com/sql/add-constraint)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)