PRIMARY KEYDDL
Buy Me A Coffee

A column or set of columns that uniquely identifies each row in a table. Enforces uniqueness and NOT NULL, and typically drives the clustered index.

Uniquely identifies each row in a table; must be unique and not NULL. A table should almost always have one. Can be composite (multiple columns). Used as the target of foreign keys and the default clustering key. Typically auto-generated for surrogate keys.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all InnoDB tables are clustered by the primary key — row data is physically stored in PK order. AUTO_INCREMENT is the standard way to generate surrogate keys. Composite PKs supported. No natural integer sequence type (use AUTO_INCREMENT).
PostgreSQL ✓ Supported all PK implicitly adds a UNIQUE index and NOT NULL. SERIAL (legacy) or IDENTITY (SQL standard, PG 10+) or GENERATED ALWAYS AS IDENTITY for auto-increment. Tables are heap-organized (not clustered by PK) — use CLUSTER command to physically reorder if needed.
SQL Server ✓ Supported all By default, PK creates a CLUSTERED index — data pages are physically ordered by PK. IDENTITY(seed, increment) for auto-increment. Can explicitly create as NONCLUSTERED if you need a different clustered index. NEWID() / NEWSEQUENTIALID() for UUID primary keys.
Oracle ✓ Supported all PKs create a unique index (B-tree by default). Auto-increment via SEQUENCE + trigger (legacy) or GENERATED AS IDENTITY (12c+). Oracle tables are heap-organized by default; index-organized tables (IOT) cluster by PK.
SQLite ✓ Supported all INTEGER PRIMARY KEY (exactly this type + constraint) is a special alias for the internal rowid — it auto-increments. AUTOINCREMENT keyword additionally prevents rowid reuse after deletions. Any other PK type creates a standard unique constraint without rowid aliasing.

Details

Physical storage differs significantly: SQL Server and MySQL (InnoDB) cluster the table by the primary key, making PK choice affect read performance. PostgreSQL and Oracle are heap-organized. SQLite's INTEGER PRIMARY KEY rowid aliasing is unique and subtle — using TEXT PRIMARY KEY does not auto-increment.

Standard Syntax

CREATE TABLE users ( id INT PRIMARY KEY, name TEXT NOT NULL ); -- Or as a named table constraint: CREATE TABLE users ( id INT, name TEXT, CONSTRAINT pk_users PRIMARY KEY (id) );

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

InnoDB tables are clustered by the primary key — row data is physically stored in PK order. AUTO_INCREMENT is the standard way to generate surrogate keys. Composite PKs supported. No natural integer sequence type (use AUTO_INCREMENT).

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL ) ENGINE=InnoDB; -- Composite: CREATE TABLE order_items ( order_id INT, item_id INT, PRIMARY KEY (order_id, item_id) );

PostgreSQL

PK implicitly adds a UNIQUE index and NOT NULL. SERIAL (legacy) or IDENTITY (SQL standard, PG 10+) or GENERATED ALWAYS AS IDENTITY for auto-increment. Tables are heap-organized (not clustered by PK) — use CLUSTER command to physically reorder if needed.

-- Modern (PG 10+): CREATE TABLE users ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT NOT NULL ); -- Legacy SERIAL style: CREATE TABLE users ( id SERIAL PRIMARY KEY, name TEXT NOT NULL );

SQL Server

By default, PK creates a CLUSTERED index — data pages are physically ordered by PK. IDENTITY(seed, increment) for auto-increment. Can explicitly create as NONCLUSTERED if you need a different clustered index. NEWID() / NEWSEQUENTIALID() for UUID primary keys.

CREATE TABLE users ( id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(100) NOT NULL ); -- Non-clustered PK: CREATE TABLE users ( id INT PRIMARY KEY NONCLUSTERED, name NVARCHAR(100) );

Oracle

PKs create a unique index (B-tree by default). Auto-increment via SEQUENCE + trigger (legacy) or GENERATED AS IDENTITY (12c+). Oracle tables are heap-organized by default; index-organized tables (IOT) cluster by PK.

-- Modern (12c+): CREATE TABLE users ( id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR2(100) NOT NULL ); -- Legacy sequence approach: CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1; CREATE TABLE users (id NUMBER DEFAULT user_seq.NEXTVAL PRIMARY KEY, name VARCHAR2(100));

SQLite

INTEGER PRIMARY KEY (exactly this type + constraint) is a special alias for the internal rowid — it auto-increments. AUTOINCREMENT keyword additionally prevents rowid reuse after deletions. Any other PK type creates a standard unique constraint without rowid aliasing.

CREATE TABLE users ( id INTEGER PRIMARY KEY, -- auto-increment via rowid alias name TEXT NOT NULL ); -- AUTOINCREMENT prevents rowid reuse: CREATE TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT );