Primary and Foreign Keys
By the end of this lesson
Identify rows uniquely and link tables reliably.
Two questions need answering before a schema is usable. Which column tells one row apart from every other row? And how does a row in one table point at a row in another?
The answers are the primary key and the foreign key. They are not paperwork. Without them the database cannot stop you storing the same customer twice, or attaching an order to a customer who does not exist.
The two kinds of key, and the guarantee each one buys:
- Primary key
- A column, or small set of columns, whose value is unique across the table and never missing. It answers "which row do you mean?" with no ambiguity.
- Foreign key
- A column that holds the primary key value of a row in another table. orders.customer_id holds a customers.customer_id, which is how an order knows whose it is.
- Referential integrity
- The rule the database enforces on your behalf: a foreign key must point at a row that actually exists. It rejects an order for customer 9999 when there is no customer 9999.
- Surrogate key
- A key with no business meaning, generated by the database purely to identify the row — customer_id 1, 2, 3.
- Natural key
- A key made from real data that is already unique, such as an ISBN or a national insurance number.
CREATE TABLE customers (
customer_id INT IDENTITY(1,1) NOT NULL,
company_name NVARCHAR(120) NOT NULL,
email NVARCHAR(200) NULL,
country NVARCHAR(80) NOT NULL,
credit_limit DECIMAL(12,2) NOT NULL,
CONSTRAINT pk_customers PRIMARY KEY (customer_id),
CONSTRAINT uq_customers_email UNIQUE (email)
);
CREATE TABLE orders (
order_id INT IDENTITY(1,1) NOT NULL,
customer_id INT NOT NULL,
employee_id INT NULL,
order_date DATE NOT NULL,
status NVARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL,
CONSTRAINT pk_orders PRIMARY KEY (order_id),
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id),
CONSTRAINT fk_orders_employee FOREIGN KEY (employee_id)
REFERENCES employees (employee_id)
);- IDENTITY(1,1) asks SQL Server to generate the next number for you, starting at 1 and increasing by 1. PostgreSQL writes this as GENERATED ALWAYS AS IDENTITY; MySQL writes AUTO_INCREMENT.
- Naming constraints (pk_customers, fk_orders_customer) means error messages name the rule that was broken instead of a generated string.
- UNIQUE on email is a second uniqueness rule that is not the primary key. A table has one primary key but may have several unique constraints.
- orders.employee_id is nullable and still has a foreign key. A NULL foreign key means "no related row", which the database permits; a non-null value must point at a real employee.
What the foreign key actually stops
-- Rejected: there is no customer 9999
INSERT INTO orders (customer_id, order_date, status, shipping_fee)
VALUES (9999, '2026-03-01', 'pending', 4.95);
-- Rejected: customer 42 still has orders pointing at them
DELETE FROM customers WHERE customer_id = 42;- The first write would create an order belonging to nobody. Those rows are called orphans, and they are painful precisely because nothing looks broken until a report quietly undercounts.
- The second write would turn every one of customer 42's orders into an orphan, so the database refuses the delete until you decide what should happen to the orders.
You can tell the database what to do instead of refusing, with ON DELETE CASCADE (delete the orders too) or ON DELETE SET NULL (keep them, unlinked). Cascade is convenient and worth treating carefully: one delete can remove far more than you expected, and the rows are gone.
For anything that represents a real business event, most teams prefer a status column — set the customer to inactive and keep the history. Deleting an order that was invoiced destroys the record of something that happened.
Surrogate or natural key
Both are defensible. The question is what happens when the real world changes.
| Surrogate key (generated number) | Natural key (real data) | |
|---|---|---|
| Stability | Never changes, because it means nothing | Changes when the business changes — a product code gets reissued |
| If it changes | It does not | Every foreign key referencing it must change too |
| Readability | customer_id 4172 tells you nothing on its own | An ISBN is meaningful without a join |
| Width in child tables | One small integer | Can be long text, repeated in every referencing row |
| Duplicate risk | None, generated per row | Real-world identifiers are reused and mistyped more often than expected |
This course uses surrogate keys for every table, and a UNIQUE constraint wherever a natural key genuinely must not repeat. That combination gives you a stable identifier for joins and still refuses duplicate emails or product codes.
Summary
- A primary key identifies one row unambiguously; a foreign key points at a row in another table
- Referential integrity is the database refusing to create rows that reference nothing
- Surrogate keys stay stable because they carry no business meaning; add UNIQUE constraints for real-world uniqueness
- Cascading deletes remove more than they appear to — for business records, prefer a status column over deletion
Practice
Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.
Try it yourself
Try it yourself
Write the CREATE TABLE for order_items. It needs its own identifier, a link to orders, a link to products, a quantity and the unit price charged. The same product must not appear twice on the same order.
Show solution
Two foreign keys, because an order item is meaningless without both the order and the product it refers to.
The UNIQUE constraint on (order_id, product_id) is the part most people miss. Without it, the same product can appear on one order as three separate lines, and then "how many did they buy?" has more than one answer.
A defensible alternative is to make (order_id, product_id) the primary key and drop order_item_id entirely. That is a composite key: unique, meaningful, and one fewer column. The cost is that anything referencing an order item must carry both columns.
CREATE TABLE order_items (
order_item_id INT IDENTITY(1,1) NOT NULL,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
CONSTRAINT pk_order_items PRIMARY KEY (order_item_id),
CONSTRAINT fk_order_items_order FOREIGN KEY (order_id)
REFERENCES orders (order_id),
CONSTRAINT fk_order_items_product FOREIGN KEY (product_id)
REFERENCES products (product_id),
CONSTRAINT uq_order_items_order_product UNIQUE (order_id, product_id)
);Think about it
Think about it
A customer asks to be removed from your system. Their customer row has 200 orders pointing at it. What are your options, and which one would you defend to a colleague?
Show solution
Cascading the delete removes the customer and all 200 orders. The financial history goes with them, which usually breaks accounting and any report covering last year.
Setting orders.customer_id to NULL keeps the orders but detaches them, so you can still total revenue but can no longer group it by customer. That also requires the column to be nullable, which weakens the rule for every other row.
The usual answer is neither: keep the row, mark it inactive, and overwrite the personal fields — name, email, address — with anonymised values. The order history stays intact and the identifying data is gone. Retention rules vary by jurisdiction and business, so this is a decision to make with whoever owns that policy, not alone in a migration.
Knowledge check
Nothing is recorded and there is no score. The explanation appears either way.
Saved in this browser only.