NOT NULL checks for a missing value. UNIQUE and PRIMARY KEY check for duplicates. None of those can stop someone from inserting an employee with a salary of -50000, a value that's present, unique, and syntactically a perfectly valid number, just business-nonsense. CHECK is the constraint for exactly that: validating a row's values against a real expression.
Learning Objectives
- Write a CHECK constraint validating a single column.
- Write a CHECK constraint comparing two columns of the same row.
- Know which database in this course historically ignored CHECK, and since when that changed.
A Basic CHECK Constraint
Any INSERT or UPDATE that would leave salary at 0 or below is rejected, immediately, by the database itself. The expression inside CHECK() can use any of the comparison and logical operators from Module 2, it's evaluated per row at write time.
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);
Comparing Two Columns in One CHECK
A CHECK expression isn't limited to comparing a column against a fixed literal, it can reference any column in that same row, or a function like CURRENT_DATE. This one specifically blocks a hire_date set in the future, which would otherwise be a silently-accepted but nonsensical value.
ALTER TABLE employees ADD CONSTRAINT chk_hire_date CHECK (hire_date <= CURRENT_DATE);
MySQL and MariaDB's History With CHECK
| Database | CHECK Constraint Support |
|---|---|
| MySQL | Parsed but silently ignored before 8.0.16 (2019). Genuinely enforced from 8.0.16 onward. |
| MariaDB | Genuinely enforced since 10.2.1 (2016). |
| PostgreSQL / SQL Server / Oracle / SQLite | Enforced since long before this course's time frame. |
This is a real, frequently-tested gotcha: for years, MySQL would happily accept CREATE TABLE ... CHECK (...) with no syntax error at all, and then silently let violating rows through anyway, the constraint was parsed and stored but never actually checked. Any MySQL instance older than 8.0.16 needs this verified directly before trusting CHECK to do anything there.
Try It Yourself
Write a CHECK constraint on projects ensuring name is never an empty string, named chk_project_name, on PostgreSQL.
ALTER TABLE projects ADD CONSTRAINT chk_project_name CHECK (name <> '');
Frequently Asked Questions
Can a CHECK constraint reference another table?
No, a CHECK expression can only use columns from the same row it's validating, plus constants and functions, it can't query another table. Rules that depend on another table's data, like a foreign key's referential check, need an actual FOREIGN KEY, not CHECK.
Does CHECK run on every row, or only new ones?
Only on rows being inserted or updated going forward. Adding a CHECK constraint to a table that already has violating data generally fails outright (the database validates existing rows against the new rule first), so existing bad data needs cleaning up before the ALTER TABLE will succeed.
Common Beginner Mistakes
Trusting CHECK on an older MySQL instance without verifying the version
Before MySQL 8.0.16, CHECK constraints were accepted syntactically but never actually enforced, invalid data could slip through with no error at all. Always confirm the actual MySQL version (or use MariaDB 10.2.1+) before relying on CHECK there.
Writing a CHECK expression that references a different table
This isn't valid, CHECK only sees the row it's validating. A rule that depends on another table's current state needs a FOREIGN KEY, or application-level logic, not CHECK.
Best Practices
- Use CHECK for business rules about a single row's own values, salary > 0, hire_date <= CURRENT_DATE, and similar.
- Verify the actual MySQL/MariaDB version before relying on CHECK being enforced, rather than assuming it.
- Keep CHECK expressions simple and row-local, anything needing another table's data belongs in a FOREIGN KEY instead.
Interview Questions
What's a well-known historical gotcha with MySQL's CHECK constraint support?
Before MySQL 8.0.16 (2019), CHECK constraints were parsed successfully with no error, stored in the schema, and then never actually enforced, invalid rows could be inserted freely despite a CHECK clause appearing to forbid it. MariaDB enforced CHECK correctly much earlier, since 10.2.1 (2016). Any pre-8.0.16 MySQL instance needs this verified directly, not assumed.
Write a CHECK constraint ensuring an employee's hire_date is never in the future, on SQL Server.
ALTER TABLE employees ADD CONSTRAINT chk_hire_date CHECK (hire_date <= GETDATE()); — SQL Server uses GETDATE() rather than CURRENT_DATE for the current date/time, the rest of the CHECK syntax is identical across databases.
Summary
CHECK validates a row's own values against a boolean expression at write time, catching business-rule violations that NOT NULL, UNIQUE, and PRIMARY KEY can't. It can reference multiple columns of the same row, but never another table. MySQL's CHECK support is a real, worth-remembering gotcha: silently unenforced before 8.0.16.
What's Next?
DEFAULT is next, the constraint that fills in a value automatically rather than rejecting a bad one.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.