The three, and what each says
NOT NULL — the value must be known. NULL isn't a value. It's the lack of one. Allowing it means allowing rows where the question was never answered, and each query that touches the column then has to decide what to do about that.
UNIQUE — no other row may hold this value. It's what stops two accounts sharing an email address without needing a read-then-write in the use, which is a race rule even when it looks correct.
CHECK — this formula must be true for each row. It's the general case that the other two are special instances of, and it takes plain SQL:
CHECK (age >= 18)
CHECK (salary > 0)
CHECK (grade IN ('junior', 'mid', 'senior'))
CHECK (end_date IS NULL OR end_date > start_date)
That last one is worth noticing. A constraint can relate two columns of the same row, so "an end date, if present, must come after the start date" is enforceable — a rule that is or else checked nowhere and violated eventually.
Read the error, not the row count
Run each commented statement in turn. The value of doing so is that each one fails differently, and the message names the constraint that stopped it:
| Attempt | Message |
|---|
| Duplicate email | UNIQUE constraint failed: staff.email |
| Age 15 | CHECK constraint failed |
| Negative salary | CHECK constraint failed |
Grade 'boss' | CHECK constraint failed |
| Missing salary | NOT NULL constraint failed: staff.salary |
Naming constraints by name — CONSTRAINT staff_age_adult CHECK (...) — makes those messages far more handy, because "CHECK constraint failed" tells you nothing when a table has six of them.
The NULL subtlety in UNIQUE
UNIQUE allows more than one NULL in almost each engine, and people are surprised by this until they see the thinking.
UNIQUE forbids two rows being *equal*. Checking NULL to NULL doesn't give true. It makes NULL, because two unknown values aren't known to be the same. So two NULLs aren't equal, and the constraint has nothing to object to.
The hands-on consequence is that UNIQUE on a nullable column doesn't ensure one row per real-world thing. It ensures one row per *known* value. If that isn't what you meant, add NOT NULL.
DEFAULT is not a constraint
DEFAULT 'junior' supplies a value when the insert omits the column. It constrains nothing: an insert that by name passes NULL gets NULL, not the default, unless NOT NULL refuses it.
Defaults and constraints are often paired for this reason. The default handles the common case, and the constraint handles the case where someone was clear about a thing they shouldn't have been.
The NULL count that surprises people, measured
UNIQUE allowing many NULLs isn't a quirk to store but a consequence to see. Because NULL = NULL is *unknown* rather than true, two NULLs are never "equal" and the constraint has nothing to reject. In the SQLite this page runs, a UNIQUE column happily accepts row after row of NULL:
CREATE TABLE t (email TEXT UNIQUE);
INSERT INTO t VALUES (NULL), (NULL), (NULL); -- all three succeed
INSERT INTO t VALUES ('a@x.com');
INSERT INTO t VALUES ('a@x.com'); -- THIS one fails
Three unknown emails coexist. The first repeated *known* value is refused. This is not even steady across engines. SQL Server permits just one NULL in a unique column, while PostgreSQL, MySQL and SQLite permit many — so a schema that leans on either action isn't portable. If the rule you meant was "one row per real address", UNIQUE alone doesn't say it. UNIQUE plus NOT NULL does.
Adding one to a large table is not free
A constraint added at CREATE TABLE costs nothing. One added later has to be *proved* against each existing row before the DB will accept it. Adding CHECK (age >= 18) or a NOT NULL to a 50-million-row table means the engine reads all 50 million rows to confirm none already violates it, and in most engines it holds a lock on the table while it does:
| constraint added | what the database must do |
|---|
| at table creation | nothing; no rows exist yet |
| to a 50M-row table | read all 50M rows, usually under a lock |
So the same declaration is instant on an empty table and an outage on a full one. This is why constraints should encode what is *structurally* true — a salary is plus, an end date follows a start date — rather than current policy. A CHECK on this year's VAT rate has to be dropped and re-verified across each row each time the rate changes. Re-verifying a large table is the costly op above, paid on a schedule set by the tax authority rather than by you.
Questions people ask about Constraints
What is the difference between NOT NULL and a CHECK constraint? NOT NULL rejects missing values. CHECK evaluates any condition on the row, such as price greater than zero, and rejects the row if it is false.
Does UNIQUE allow multiple NULLs? In most databases, yes, because NULL is not equal to NULL. SQL Server is a notable exception that allows only one, so check your engine.
Is DEFAULT a constraint? No. DEFAULT only fills in a value when none is given. It does not stop someone inserting a different value or an explicit NULL.
Why not validate everything in application code? Application checks can be bypassed by another service, a script, or a bug. A constraint is enforced for every writer, so the rule holds no matter who connects.
Will adding a constraint to a big table lock it? It can. The database has to check every existing row, and some engines hold a lock while doing so. Many support adding the constraint as not validated first, then validating later.
What error do I get when a constraint fails? The statement is rejected with an error naming the constraint, and no row from that statement is written. Read the message, because it tells you which rule was broken.
Where it goes wrong with Constraints
Unnamed constraints. Six CHECKs on a table and an error that says only "CHECK constraint failed" wastes real time. Name them.
Constraints that encode volatile policy. A CHECK on a VAT rate has to be migrated when the rate changes, and migrating a constraint on a large table is not free. Constrain what is structurally true, not what is currently true.
given UNIQUE means one per thing. With NULLs allowed it means one per known value.
Adding a constraint to a large live table without thinking. The DB has to verify each existing row before it can accept the constraint, and in most engines that takes a lock while it does. Plan the migration during a low-traffic window so it does not block other writes.