SQL PRIMARY KEY constraint

Here is a clear, practical, and complete guide to the SQL PRIMARY KEY constraint, including syntax, examples, rules, and differences across major SQL engines.

What the SQL PRIMARY KEY constraint Does

A PRIMARY KEY (PK) uniquely identifies each row in a table.

A PRIMARY KEY:

• Must be unique

• Must be NOT NULL

• Can consist of one or multiple columns (composite key)

• Each table can have only one primary key

Basic Syntax (CREATE TABLE)

Explicitly Naming a PRIMARY KEY

Good practice for production databases:

 Composite PRIMARY KEY (Multiple Columns)

Often used in many-to-many tables.

•  Prevents duplicate entries for the same product in the same order.

Add PRIMARY KEY to Existing Table (ALTER TABLE)

PostgreSQL / SQL Server:

MySQL:

 Drop PRIMARY KEY

MySQL:

PostgreSQL / SQL Server:

 Auto-Incrementing PRIMARY KEYS

MySQL

PostgreSQL

SQL Server

 PRIMARY KEY vs UNIQUE Constraint

 Best Column Types for PRIMARY KEYS

• Integer (INT, BIGINT) — fastest and simplest

• GUID/UUID — good for distributed systems

• Avoid composite keys unless necessary

 Important Behavior Notes

•  Attempting to insert a duplicate PK value causes an error
•  Primary keys create a unique index automatically
•  PKs work best when stable (never change)
•  Avoid using business data (email, username) as PKs

Real-World Examples

1. Customers Table

2. Junction Table (Many-to-Many)

3. Enforcing strong identity on a table

Best Practices

•  Always define a primary key for every table
• Prefer surrogate keys (INT/SERIAL) over natural keys
•  Keep PK columns small and immutable
•  Name your PK constraints (pk_tableName)
• Use composite PKs only when they reflect real logical uniqueness