What Is a CHECK Constraint and When Would You Use One?
Learn what a CHECK constraint does, how it validates rows on write, and common use cases for enforcing data rules in SQL.
Expected Interview Answer
A CHECK constraint is a rule attached to a column or table that the database evaluates on every insert or update, rejecting any row whose values do not satisfy a boolean expression, such as price greater than zero or age within a valid range.
CHECK constraints push simple business rules down into the schema itself so invalid data can never be written, regardless of which application, script, or user issues the write. Common uses include bounding numeric ranges, restricting a column to a fixed set of values when an enum type is unavailable, and enforcing relationships between two columns in the same row, such as requiring an end date to be later than a start date. Because the check runs inside the database engine, it is enforced consistently across every client, unlike validation logic duplicated in application code.
- Rejects invalid data at write time, not read time
- Enforced consistently across every client and tool
- Documents business rules directly in the schema
- Cheaper than catching bad data after the fact
AI Mentor Explanation
A ground curator sets a hard rule that the pitch length must measure exactly 22 yards before any match is certified to start, and the certifying inspector rejects the ground outright if the measurement is off. No match gets scheduled on an uncertified pitch, no matter how good the ground otherwise looks. A CHECK constraint works the same way: the database inspects every incoming row against its rule and refuses the write outright if the rule fails.
Step-by-Step Explanation
Step 1
Identify the business rule
Decide the exact boolean condition a column or row must always satisfy, such as price > 0.
Step 2
Attach the constraint
Add CHECK (condition) to the column or table definition via CREATE TABLE or ALTER TABLE.
Step 3
Database validates every write
On each INSERT or UPDATE the engine evaluates the expression and rejects the statement if it is false.
Step 4
Existing data is validated too
Adding a CHECK to an existing table typically validates current rows unless explicitly created as NOT VALID/NOT ENFORCED.
What Interviewer Expects
- Correct definition of CHECK as a row-level boolean validation rule
- A concrete example such as a price or age range check
- Understanding that it runs inside the database, not the application
- Awareness of how it differs from NOT NULL and UNIQUE constraints
Common Mistakes
- Confusing CHECK with a foreign key relationship
- Assuming CHECK constraints can reference other tables
- Forgetting that some engines historically ignored CHECK silently
- Not mentioning that existing rows can violate a newly added CHECK unless validated
Best Answer (HR Friendly)
“A CHECK constraint is a rule I attach to a column, like requiring price to be greater than zero, and the database automatically blocks any insert or update that breaks that rule. I like using them because the rule is enforced everywhere data enters the table, not just in one application, so bad data simply cannot get in.”
Code Example
CREATE TABLE Products (
product_id INT PRIMARY KEY,
price NUMERIC(10,2) CHECK (price > 0),
discount_pct INT CHECK (discount_pct BETWEEN 0 AND 100),
status VARCHAR(20) CHECK (status IN ('active', 'discontinued', 'draft'))
);
-- Adding a CHECK to an existing table
ALTER TABLE Products
ADD CONSTRAINT chk_price_positive CHECK (price > 0);Follow-up Questions
- Can a CHECK constraint reference values in another table?
- What happens if you add a CHECK constraint to a table that already has violating rows?
- How is a CHECK constraint different from a NOT NULL constraint?
- Can a CHECK constraint span multiple columns in the same row?
MCQ Practice
1. What does a CHECK constraint primarily enforce?
A CHECK constraint evaluates a boolean expression on every insert or update and rejects rows that do not satisfy it.
2. Which of these is a typical use case for a CHECK constraint?
Bounding a numeric range like a percentage is a classic CHECK constraint use case, since it is a self-contained row rule.
3. Where is a CHECK constraint enforced?
CHECK constraints are evaluated by the database engine itself on every write, regardless of which client issued it.
Flash Cards
What is a CHECK constraint? — A rule that a column or row must satisfy a boolean condition, enforced on every insert or update.
Give a common CHECK example. — CHECK (price > 0) ensures a price column never stores a zero or negative value.
Can CHECK reference another table? — No, standard CHECK constraints can only reference columns within the same row.
Why prefer CHECK over app-side validation? — It is enforced consistently for every client and cannot be bypassed by a direct SQL write.