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.
CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT NOT NULL );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- 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. |
-- 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 byemployee_idalone, but not byproject_idalone, 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.