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
| Feature | PRIMARY KEY | UNIQUE |
|---|---|---|
| Uniqueness enforced | ✔ Yes | ✔ Yes |
| Allows NULL | ❌ No | ✔ Yes (DB-dependent) |
| Number per table | 1 | Many |
| Implies NOT NULL | ✔ Yes | ❌ No |
| Often auto-increment | ✔ Common | ❌ Rare |
📌 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
