Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK)
Constraints are the rules your database enforces so bad data can never get in — no duplicate ids, no negative prices, no orders pointing at customers who don't exist. They're the difference between a database you can trust and a pile of unreliable rows.
Learn Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice…
Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.
By the end you'll wield all the core constraints — PRIMARY KEY , UNIQUE , NOT NULL , CHECK , and FOREIGN KEY — and understand referential integrity and ON DELETE behaviour.
What You'll Learn
The Five Core Constraints
Each constraint enforces a different guarantee. Keep this map in mind as you read.
1. PRIMARY KEY — Identity
A PRIMARY KEY uniquely identifies every row. It's automatically UNIQUE and NOT NULL , and a table can have exactly one (though it may span multiple columns — a "composite" key).
A primary key is like a passport number: exactly one per person, never blank, and never shared. It's how the system tells one row apart from every other.
2. UNIQUE — No Duplicates
UNIQUE forbids two rows from sharing a value, like an email address. Unlike a primary key, you can have several UNIQUE columns, and they usually permit NULL .
3. NOT NULL & CHECK — Value Rules
NOT NULL requires a value; CHECK enforces a custom condition on every insert and update. Together they keep impossible data — a missing name, a negative price — out of the table.
4. FOREIGN KEY — Linking Tables
A FOREIGN KEY ties a column to a key in another table, so an order's customer_id must match a real customer. This is referential integrity — the database refuses to create orphaned rows.
Your Turn: a reviews table
Fill in the constraint keywords so ratings stay between 1 and 5 and each review links to a real product.
5. ON DELETE — Cascades and Cleanup
What happens to an order when its customer is deleted? ON DELETE decides: CASCADE deletes the children too, SET NULL orphans them gracefully, and RESTRICT blocks the delete while children exist.
6. Naming Your Constraints
Give constraints explicit names with CONSTRAINT name . Error messages become readable ( chk_balance instead of products_check1 ), and you can drop them by name later.
Reorder Challenge
You're inserting data into two related tables. Order these so the foreign key never fails:
Quick Recall
1) How many PRIMARY KEYs can a table have? How many UNIQUE constraints?
Exactly one PRIMARY KEY, but as many UNIQUE constraints as you like.
2) You insert an order with customer_id = 99 but no such customer exists. What happens?
The FOREIGN KEY rejects the insert — referential integrity won't allow an order pointing at a non-existent customer.
3) Which constraint stops a rating of 7 in a 1–5 scale?
A CHECK (rating BETWEEN 1 AND 5) constraint rejects any value outside the range.
Common Errors (and the fix)
- "foreign key constraint fails": the referenced parent row doesn't exist. Insert the parent first.
- "duplicate key value": you inserted a value that violates a PRIMARY KEY or UNIQUE rule.
- Dropping a referenced parent row: blocked unless you use ON DELETE CASCADE / SET NULL .
- CHECK ignored: very old MySQL parsed but didn't enforce CHECK — use 8.0.16+ or another engine.
- Two primary keys: you can't declare PRIMARY KEY twice; combine columns into one composite key.
📘 Quick Reference
Syntax
Purpose
Frequently Asked Questions
Mini-Challenge: An Enrollments Table
Use all four constraint types in one table. Write it, then run it to confirm it's valid.
🎉 Lesson Complete
- ✅ PRIMARY KEY = unique + NOT NULL identity (one per table)
- ✅ UNIQUE forbids duplicates; NOT NULL forbids blanks
- ✅ CHECK enforces custom rules like price >= 0
- ✅ FOREIGN KEY links tables and guarantees referential integrity
- ✅ ON DELETE CASCADE / SET NULL / RESTRICT controls cleanup
- ✅ Next: DROP vs TRUNCATE vs DELETE — removing data and tables
Practice quiz
How many PRIMARY KEY constraints can a single table have?
- As many as you like
- At most two
- Exactly one
- None — it is optional and forbidden
Answer: Exactly one. A table can have exactly one PRIMARY KEY (it may span multiple columns as a composite key).
A PRIMARY KEY is automatically equivalent to which combination?
- UNIQUE + NOT NULL
- CHECK + DEFAULT
- FOREIGN KEY + UNIQUE
- NOT NULL only
Answer: UNIQUE + NOT NULL. PRIMARY KEY = UNIQUE + NOT NULL, giving each row a never-blank, never-duplicated identity.
How does a UNIQUE constraint differ from a PRIMARY KEY?
- UNIQUE forbids NULLs more strictly than PRIMARY KEY
- UNIQUE can only be used on numeric columns
- There is no difference at all
- UNIQUE usually allows NULLs and you can have many per table
Answer: UNIQUE usually allows NULLs and you can have many per table. UNIQUE typically permits NULLs and a table can have several UNIQUE columns, unlike the single PRIMARY KEY.
What does a CHECK constraint do?
- Links a column to another table
- Enforces a custom condition on every INSERT and UPDATE
- Automatically fills in a missing value
- Speeds up queries on the column
Answer: Enforces a custom condition on every INSERT and UPDATE. CHECK enforces a custom rule such as price >= 0, rejecting rows that violate it.
What does a FOREIGN KEY guarantee?
- A value must match an existing key in the referenced table
- A value is never NULL
- A value is unique across the table
- A column auto-increments
Answer: A value must match an existing key in the referenced table. A FOREIGN KEY enforces referential integrity: the value must exist in the parent table — no orphan rows.
You insert an order with customer_id = 99 but no customer 99 exists. What happens?
- The customer is created automatically
- The order inserts with a NULL customer
- The FOREIGN KEY rejects the insert
- Nothing — foreign keys are not checked on insert
Answer: The FOREIGN KEY rejects the insert. Referential integrity blocks an order that points at a non-existent customer.
What does ON DELETE CASCADE do when a parent row is deleted?
- Blocks the delete while children exist
- Automatically deletes the child rows too
- Sets the child foreign keys to NULL
- Throws an error every time
Answer: Automatically deletes the child rows too. CASCADE removes the children along with the parent. RESTRICT blocks it; SET NULL nulls the FK.
Which constraint stops a rating of 7 in a 1-to-5 scale?
- UNIQUE (rating)
- NOT NULL
- PRIMARY KEY
- CHECK (rating BETWEEN 1 AND 5)
Answer: CHECK (rating BETWEEN 1 AND 5). A CHECK constraint rejects any value outside the allowed range.
What is the benefit of naming a constraint with CONSTRAINT name?
- It makes inserts faster
- Readable error messages and the ability to drop it by name later
- It allows duplicate values
- It disables the constraint
Answer: Readable error messages and the ability to drop it by name later. Named constraints report a clear name on violation and can be dropped with ALTER TABLE ... DROP CONSTRAINT name.
What does the NOT NULL constraint forbid?
- Storing the number zero
- Storing duplicate values
- Leaving the column with no value (empty)
- Storing text values
Answer: Leaving the column with no value (empty). NOT NULL requires a value to always be present; it is unrelated to zero or duplicates.