PRIMARY KEY

DDL
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

Filter by Database
SQL PRIMARY KEY Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native 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 Native 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 Native 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 Native 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 Native 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: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 );