PRIMARY KEY
A PRIMARY KEY uniquely identifies every row in a table. It enforces two rules automatically: UNIQUE (no duplicates) and NOT NULL (must have a value). Every table should have a primary key - it is the anchor for foreign key references and JOIN operations.
PRIMARY KEY Rules
- Uniquely identifies each row - no duplicates allowed
- Cannot be NULL - every row must have a value
- Only one PRIMARY KEY per table
- Can be a single column or composite (multiple columns)
- Usually an auto-increment integer (SERIAL/AUTO_INCREMENT) or UUID
- Other tables reference it via FOREIGN KEY
PRIMARY KEY Examples
-- Single column PK (most common)
CREATE TABLE users (
id SERIAL PRIMARY KEY, -- PostgreSQL auto-increment
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE
);
-- AUTO_INCREMENT syntax (MySQL)
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
-- UUID primary key (distributed systems)
CREATE TABLE orders (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id INT REFERENCES users(id),
created_at TIMESTAMP DEFAULT NOW()
);
-- Composite primary key (many-to-many junction table)
CREATE TABLE order_items (
order_id INT REFERENCES orders(id),
product_id INT REFERENCES products(id),
quantity INT NOT NULL DEFAULT 1,
PRIMARY KEY (order_id, product_id) -- composite PK
);
-- Add PK to existing table
ALTER TABLE legacy_table ADD PRIMARY KEY (id);Quick Quiz
Tip
Tip
Practice PRIMARY KEY in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Constraints enforce data integrity at DB level.
Common Mistake
Warning
A common mistake with PRIMARY KEY is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of PRIMARY KEY from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- A PRIMARY KEY uniquely identifies every row in a table.
- Uniquely identifies each row - no duplicates allowed
- Cannot be NULL - every row must have a value
- Only one PRIMARY KEY per table