Keys are the rules that make tables trustworthy and connectable. Understanding them helps in interviews and, more practically, stops you from writing joins that silently duplicate rows.
Primary key
A primary key uniquely identifies each row in a table.
- Values must be unique.
- Values cannot be NULL.
- A table has one primary key (which may span several columns).
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE
);Foreign key
A foreign key is a column whose values must exist as a primary (or unique) key in another table. It creates the link between tables.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
amount DECIMAL(10, 2)
);With this rule, you cannot insert an order for a customer who does not exist, and by default you cannot delete a customer who still has orders. Options like ON DELETE CASCADE change that behaviour.
Unique key
A unique constraint stops duplicate values in a column, like email above. Differences from a primary key:
| Primary key | Unique key | |
|---|---|---|
| Duplicates | Not allowed | Not allowed |
| NULLs | Not allowed | Usually allowed |
| How many per table | One | Many |
| Purpose | The row's identity | A business rule (no duplicate emails) |
Composite keys
Sometimes no single column is unique, but a combination is. In an enrolments table, one student can take many courses and one course has many students, but each (student, course) pair appears once:
CREATE TABLE enrolments (
student_id INTEGER REFERENCES students(student_id),
course_id INTEGER REFERENCES courses(course_id),
enrolled_on DATE,
PRIMARY KEY (student_id, course_id)
);Natural vs surrogate keys
- A natural key has business meaning: PAN number, email, product SKU.
- A surrogate key is an artificial ID, such as an auto-increment number.
Many systems use surrogate keys as primary keys, because business values can change or be entered wrongly.
Why keys matter for analysts
When you join, ask: "Is the join column unique on the side I'm joining to?"
- Joining orders to customers on
customer_idis safe: each order matches one customer. - Joining two tables on a column that is not unique on either side multiplies rows and inflates totals.
Before an important join, check uniqueness:
SELECT customer_id, COUNT(*)
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;No rows means the key really is unique.
Practise
Our constraints set lets you create keys and see what they block, and the joins set shows what happens when keys are not what you expect.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: