CHECK Constraint

Hook: A CHECK constraint is the database version of a bouncer with a rule card: it stops bad rows before they ever get a seat.

Question: What is a CHECK constraint in SQL?

Answer: A CHECK constraint is a rule on a table that rejects rows when a column value, or a combination of column values, does not meet a condition. For example, it can enforce that price is non-negative, or that discount is not bigger than subtotal. It is enforced by the database on INSERT and UPDATE, so bad data is blocked at the storage layer.

Interview-Ready Answer: I use a CHECK constraint when I want the database to protect a business rule, like age must be at least 18 or discount must not exceed subtotal. It evaluates each row when data is inserted or updated, and it rejects the row if the condition is false. One detail I like to mention is that NULL can still pass a CHECK in many databases unless I also add NOT NULL, because NULL makes the expression unknown instead of false.

What it is

Detailed Explanation: A CHECK constraint is a boolean rule attached to a table. A boolean expression is a condition that evaluates to true or false, such as price >= 0 or status IN ('draft', 'active'). You can attach it to one column or to several columns together.

How it works under the hood

1. The database receives an INSERT or UPDATE.
2. It builds the new row values, including defaults if needed.
3. It evaluates the CHECK expression for that row.
4. If the result is TRUE, the row is accepted.
5. If the result is FALSE, the statement fails for that row.
6. If the result is UNKNOWN because of NULL, most SQL engines allow the row, unless another rule such as NOT NULL rejects it.

This is why CHECK is a data-quality guardrail, not a query filter. It does not help your SELECT speed; it only protects writes.

When to use it

• Simple ranges: quantity > 0, rating BETWEEN 1 AND 5
• Allowed sets: status IN ('pending', 'paid', 'shipped')
• Cross-column rules: discount_cents <= subtotal_cents
• Format-like rules when they are simple enough to express safely

What it compares to

Performance and limits

For a simple predicate, CHECK is usually O(1) per row because it just evaluates a small expression. For n inserted rows, the total cost is O(n). There is no index lookup for CHECK itself, so it is usually cheap, but a complex expression or function call can add CPU cost during bulk loads. In real systems, millions of rows can still be fine, but if your predicate does expensive string parsing or custom functions, the write path will slow down.

Important edge cases

• NULL gotcha: CHECK (age >= 18) does not forbid NULL by itself in many engines.
• Old MySQL gotcha: MySQL versions before 8.0.16 parsed CHECK but did not enforce it.
• Cross-table rules: CHECK should stay row-local; if you need to look at another table, use a foreign key, trigger, or application logic.
• UPDATE matters: the rule is re-checked when a row is updated, not only when it is first inserted.
• No query magic: the optimizer does not use CHECK to filter SELECT results.

Memory tip while whiteboarding: say, CHECK guards the door on the way in; it does not patrol the room after the guest is inside.

Real-World Example: Imagine a checkout service in an e-commerce app. The application calculates subtotal_cents, discount_cents, and final_cents, then writes the order to the database. A CHECK constraint like discount_cents <= subtotal_cents and subtotal_cents >= 0 makes sure a buggy client cannot save a negative subtotal or a discount larger than the order total.

What goes wrong without it: a frontend bug or a broken integration sends discount_cents = 5000 for a 3000-cent order, and the database happily stores a negative final total. The symptom may not be an immediate crash; instead, finance reports drift, refund logic gets confused, and support sees customers complaining about strange receipts. In logs, you might see no obvious SQL error because the bad row was accepted, which is exactly why database-level rules matter.

In production, CHECK constraints are especially useful for protecting money fields, inventory counts, and status values. They reduce the chance that one bad service, one malformed import, or one overlooked UI path corrupts the source of truth.

Follow-up & Tricky Questions:

• Can a CHECK constraint reference another table? Usually no in standard SQL. CHECK is meant for row-local rules; for cross-table validation, a foreign key or trigger is the safer tool.
• Does CHECK allow NULL values? Often yes, because NULL makes the expression unknown rather than false. If you need the column required, combine CHECK with NOT NULL.
• When is CHECK enforced? On each INSERT and UPDATE of the row. It protects writes, not reads.
• Can I name a CHECK constraint? Yes, and that is helpful because error messages become clearer and maintenance is easier.
• Is CHECK enough for all validation? No. It is a strong last line of defense, but applications still need validation for user-friendly errors and better UX.
• Does CHECK help SELECT performance? No. It does not create an index and does not speed up queries.
• Was CHECK always supported in MySQL? No. Older MySQL versions accepted the syntax but ignored it; modern versions enforce it.

• Tricky: Is CHECK the same as WHERE? No. WHERE filters query results, while CHECK blocks invalid data from being stored.
• Tricky: If the expression is unknown, does it fail? Usually no. Unknown is not false, so the row may pass unless another constraint stops it.
• Tricky: Can CHECK replace business logic in code? It can enforce part of the rule, but complex workflows and cross-row checks still need code, triggers, or transactions.

Common Mistakes

• Forgetting NOT NULL: CHECK (age >= 18) does not necessarily block NULL. Add NOT NULL when the column must exist.
• Using CHECK for cross-table logic: if the rule depends on another table, CHECK is the wrong tool. Use a foreign key, trigger, or transaction logic.
• Assuming CHECK helps reads: it protects writes only. It does not make selects faster.
• Relying on old behavior assumptions: some engines, especially older MySQL releases, did not enforce CHECK correctly.

Memory Hook

Memory Hook: Think of CHECK as a bouncer at the door with a simple rule sheet. If the row breaks the rule, it never enters the table. If the value is NULL, the bouncer may shrug unless NOT NULL is also on the list.

Cheat Sheet

• CHECK enforces a boolean rule on inserted or updated rows.
• Use it for ranges, sets, and simple multi-column business rules.
• TRUE passes, FALSE fails, and NULL often passes unless blocked elsewhere.
• It is a write-time guard, not a query-time optimization.
• Combine it with NOT NULL, UNIQUE, and FOREIGN KEY for stronger data safety.
• Prefer named constraints so errors are easier to debug.

Practice Tasks

• Create a products table with price_cents >= 0 and status IN (...).
• Add a table-level CHECK that ensures end_date >= start_date.
• Test one row that passes, one that fails, and one with NULL to see why NOT NULL matters.