NULL values, and the common pitfalls every SQL beginner runs into.
What Is a CHECK Constraint?
A CHECK constraint is a rule you attach to a column that says "a value is only valid if this condition is true". It is just a Boolean expression — if the expression returns true, the value is allowed; if it returns false, the row is rejected with an error.
Typical examples — a price must be greater than zero, a stock count must not be negative, an age must be between 0 and 120, a status must be one of 'active' / 'inactive' / 'blocked'. CHECK constraints let the database itself enforce these rules, so bad data simply cannot slip in no matter which application writes to the table.
Why Use CHECK at the Database Level?
You might wonder — can't the application do this validation? Yes, but there are usually many applications, scripts, and admin tools writing to a database over its lifetime. If validation lives only in one app, other apps will still write garbage. A CHECK constraint is the last line of defense, enforced once at the database and trusted by every client.
A Small Example Table
Let's build a simple Products table and protect it with several CHECK rules.
CREATE TABLE Products (
ProductId INT IDENTITY(1, 1) PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Price DECIMAL(10, 2) CONSTRAINT CK_Products_Price CHECK (Price > 0),
Stock INT CONSTRAINT CK_Products_Stock CHECK (Stock >= 0),
Rating INT CONSTRAINT CK_Products_Rating CHECK (Rating BETWEEN 1 AND 5),
Category VARCHAR(20) CONSTRAINT CK_Products_Category CHECK (Category IN ('Electronics','Clothing','Grocery'))
);
Four rules in one table:
1. Price must be greater than 0 (no free or negative-price rows).
2. Stock cannot be negative.
3. Rating must fall in the range 1 to 5.
4. Category must be one of the three allowed values.
How CHECK Blocks Bad Data
-- Valid — all rules pass
INSERT INTO Products (Name, Price, Stock, Rating, Category)
VALUES ('Laptop', 55000, 20, 4, 'Electronics'); -- OK
-- Blocked — Price must be > 0
INSERT INTO Products (Name, Price, Stock, Rating, Category)
VALUES ('Free Sample', 0, 10, 4, 'Grocery');
-- Error: The INSERT statement conflicted with the CHECK constraint "CK_Products_Price".
-- Blocked — Rating out of range
INSERT INTO Products (Name, Price, Stock, Rating, Category)
VALUES ('Mouse', 950, 15, 7, 'Electronics');
-- Error: The INSERT statement conflicted with the CHECK constraint "CK_Products_Rating".
-- Blocked — Category not allowed
INSERT INTO Products (Name, Price, Stock, Rating, Category)
VALUES ('Gym Bag', 1200, 8, 5, 'Fitness');
-- Error: The INSERT statement conflicted with the CHECK constraint "CK_Products_Category".
Notice how clean the errors are — each one names the exact constraint that was violated. That's why naming them (CK_Products_Price etc.) is so important.
Adding a CHECK Constraint to an Existing Table
If your table already exists, use ALTER TABLE ADD CONSTRAINT.
ALTER TABLE Products
ADD CONSTRAINT CK_Products_Stock CHECK (Stock >= 0);
By default, SQL Server checks existing data before creating the constraint. If any existing row fails the rule, the ALTER statement fails — you must clean the bad rows first or use WITH NOCHECK (not recommended in production).
Using CHECK Across Multiple Columns
A CHECK constraint isn't limited to one column — you can write any Boolean expression involving several columns from the same row. This is called a table-level CHECK constraint.
ALTER TABLE Orders
ADD CONSTRAINT CK_Orders_Dates
CHECK (ShipDate >= OrderDate);
Read it as: "An order cannot ship before it was placed." Very handy for business rules that involve more than one column.
The NULL Quirk (Most Important Thing to Remember)
false. If it returns true or UNKNOWN (which happens when NULL is involved), the row is accepted.
Because any comparison with NULL returns UNKNOWN — not false — a nullable column can still hold NULL even if your CHECK constraint seems to forbid it.
-- Rating has: CHECK (Rating BETWEEN 1 AND 5)
INSERT INTO Products (Name, Price, Stock, Rating, Category)
VALUES ('New Arrival', 500, 3, NULL, 'Clothing');
-- Succeeds! Rating = NULL passes because the check returns UNKNOWN.
If you want to forbid NULL too, declare the column NOT NULL in addition to the CHECK constraint.
Dropping a CHECK Constraint
Because you named your constraint, dropping it is a one-liner.
ALTER TABLE Products DROP CONSTRAINT CK_Products_Rating;
CK__Products__Rating__3B75D760. That's painful to remember. Always name your checks — for example CK_Table_Column or CK_Table_Rule.
Useful Operators Inside CHECK
| Operator | Example | Good For |
|---|---|---|
>, <, >=, <= |
CHECK (Price > 0) |
Ranges and thresholds |
BETWEEN |
CHECK (Rating BETWEEN 1 AND 5) |
Inclusive numeric or date range |
IN |
CHECK (Category IN ('A','B','C')) |
Enum-like fixed lists |
LIKE |
CHECK (Email LIKE '%@%') |
Basic string pattern checks |
AND, OR |
CHECK (Age >= 18 AND Age <= 65) |
Combining multiple conditions |
Common Mistakes
Mistake 1: Expecting CHECK to block NULL. It doesn't. Add NOT NULL separately if you want NULLs rejected too.
Mistake 2: Skipping the constraint name. Auto-named constraints are hard to drop or identify in error messages. Always name them.
Mistake 3: Using subqueries inside CHECK. CHECK constraints cannot reference other tables. For rules that involve another table (like "order amount must be less than customer's credit limit"), use a foreign key, a trigger, or application logic.
Mistake 4: Adding a CHECK constraint that fails against existing data. SQL Server will refuse to create the constraint. Clean the data first, or temporarily remove the offending rows.
Mistake 5: Using CHECK for validation that changes often. For example, CHECK (DiscountPercent < 30) is fine today, but if marketing later raises the limit to 40%, you have to alter the constraint. Consider if a lookup table or app rule is more flexible.
CHECK vs Other Constraints
| Constraint | Purpose |
|---|---|
NOT NULL |
Column can't be empty |
UNIQUE |
No duplicates in a column |
PRIMARY KEY |
Unique + not null, identifies each row |
FOREIGN KEY |
Value must match a row in another table |
DEFAULT |
Fill a value when none is provided |
CHECK |
Custom Boolean rule on a value |
The Interview Answer
"A CHECK constraint is a rule on a column that only allows values which make a Boolean expression return true. If the expression returns false, the insert or update is rejected. It can check a single column, a range, a list of allowed values with IN, or even combine multiple columns in the same row. NULLs are accepted by default because comparisons with NULL return UNKNOWN, not false — to also block NULLs you must add NOT NULL separately."
Summary
A CHECK constraint is a simple but powerful way to keep bad data out of your tables. You write a Boolean expression, attach it to a column, and SQL Server automatically rejects any row that would make the expression return false. It is enforced at the database level, so it protects your data no matter which application writes to the table.
Remember the two rules that catch most beginners — NULLs always pass (because a NULL comparison returns UNKNOWN, not false), and CHECK cannot reference other tables. For cross-table rules, use a foreign key or a trigger. For everything else — ranges, enums, patterns, and sanity rules — CHECK is your best friend.
| Concept | Key Takeaway |
|---|---|
| CHECK constraint | Boolean rule that must be true for a row to be saved |
| Adding at creation | CONSTRAINT name CHECK (expression) inside CREATE |
| Adding later | ALTER TABLE ADD CONSTRAINT name CHECK (...) |
| NULL behavior | NULL passes — combine with NOT NULL to block it |
| Single vs multi-column | Can reference any column in the same row |
| Not supported | Subqueries or references to other tables |
| Naming | Always name it, e.g., CK_Products_Price |
| Handy operators | BETWEEN, IN, LIKE, AND, OR |
| Dropping | ALTER TABLE DROP CONSTRAINT name |
| Interview line | "Boolean expression — true passes, false fails, NULL passes" |