Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Constraints and Data Types

SQL PRIMARY KEY constraint

Reference for the SQL PRIMARY KEY constraint: uniquely identify rows, single and composite keys, NULL rules, auto-increment keys and dialect differences.

A PRIMARY KEY constraint marks the column, or the combination of columns, that identifies each row of a table. The values must be unique and must not be NULL. A table can have only one primary key, and other tables point at it with foreign keys.

Most databases build a unique index behind the primary key, so lookups by key are fast. A key with one column is a simple key. A key made from several columns is a composite key, common in link tables that connect two entities.

SQL
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

Try it

SQL A single-column primary key rejects duplicates (fails on purpose at the end)

Press Run (or Ctrl + Enter) to see the result here.

SQL A composite primary key, as in the company database ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
-- column constraint
column_name data_type PRIMARY KEY

-- table constraint (required for composite keys)
[CONSTRAINT constraint_name] PRIMARY KEY (column_name [, ...])
Part Description
column_name One or more columns that together form the key.
CONSTRAINT constraint_name Optional name, handy for error messages and for dropping the constraint later.

Rules: every key column is implicitly NOT NULL (SQLite is the exception, see below), no two rows may have the same key, and a table can declare only one primary key. Use UNIQUE for additional unique columns.

Dialect differences

Dialect Notes
SQLite INTEGER PRIMARY KEY is an alias for the internal rowid and fills itself in when omitted. Other primary keys may contain NULL unless declared NOT NULL, a long-standing quirk, except in STRICT or WITHOUT ROWID tables.
PostgreSQL Use GENERATED ALWAYS AS IDENTITY or SERIAL for generated keys. The key columns are always NOT NULL.
MySQL AUTO_INCREMENT for generated keys. InnoDB stores rows in primary key order (a clustered index), so choose the key with care.
SQL Server IDENTITY(1,1). The primary key is clustered by default, but you can write PRIMARY KEY NONCLUSTERED.
Oracle GENERATED AS IDENTITY since 12c, sequences before that.
SQL
-- PostgreSQL
CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);

-- MySQL
CREATE TABLE products (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Usage notes

  • Choose stable keys. A key should never change. Natural values such as email addresses or phone numbers do change, so most designs use a meaningless number (a surrogate key).
  • Keep keys narrow. Every foreign key and every secondary index repeats the key value, so small keys save space everywhere.
  • Composite keys order their columns. A key on (employee_id, project_id) serves lookups by employee_id alone, but not by project_id alone, so add an index for that.
  • Primary key vs UNIQUE NOT NULL: both guarantee uniqueness, but only the primary key is the table's identity that foreign keys reference by default.
  • Keys and gaps. Generated numbers are not gapless: rolled-back inserts and deleted rows leave holes. Never use them as a count.
  • Changing a key later means updating every table that references it, unless the foreign key has ON UPDATE CASCADE.
  • Error message: a duplicate key fails with a "UNIQUE constraint failed" message in SQLite and a "duplicate key value" or "Violation of PRIMARY KEY" message in other systems.