Primary Key
Two customers named Ravi Kumar live in Delhi. When one of them asks to change his email, which row do you update? If the only way to tell them apart is their name and city, you cannot. A primary key gives every row an identity that nothing else can share.
The rule
A primary key is unique, never NULL, and names exactly one row.
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
city VARCHAR(60)
);Declaring customer_id as the primary key makes the database enforce two rules on every insert and update:
- Unique. No two rows may have the same
customer_id. - Not NULL. Every row must have one.
A table has at most one primary key. It usually also gets an index automatically, so looking up a row by its key is fast. And other tables use it as the target of their foreign keys, which is how tables link.
A primary key can span several columns — a composite key — when it is the combination that is unique:
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER NOT NULL,
PRIMARY KEY (order_id, product_id)
);Here one order may contain many products and one product may appear in many orders, but each product appears at most once per order.
Many teams let the database generate keys: customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY in PostgreSQL, or AUTO_INCREMENT in MySQL. These are surrogate keys — meaningless numbers that exist only to identify. The alternative, a natural key such as an email or a national id, carries real-world meaning, which sounds appealing until that real-world value changes or turns out not to be unique.
The shortcut
Test any candidate key with three questions — unique? always present? never changes? — and reject it at the first "no".
Candidate: name
Unique? No — two Ravi Kumars. Rejected.
Drills
Answer fast — trust the rule.
customer_id is the primary key and a row with id 3 exists. What happens on INSERT INTO customers (customer_id, name) VALUES (3, 'Tara');?
How many primary keys can one table have?
Why is email a risky primary key for customers?
In order_items with PRIMARY KEY (order_id, product_id), which pair of rows is NOT allowed together?
Remember
- A primary key is unique, never NULL, and names exactly one row.
- Test candidates: unique? always present? never changes?
- One primary key per table — possibly made of several columns.