The application rejects a negative amount until a different import path writes one. Check constraints put that rule beside the data every writer uses.
Choose Check Constraints for Rules That Belong to One Row
A CHECK constraint suits a rule SQL Server can judge from the row itself. Amount at or above zero is one. EndDate after StartDate is another. It protects the table regardless of which application or load writes to it.
I ask whether the rule is stable business truth or a changing workflow decision. A price must be nonnegative can be a table rule. A temporary promotion limit can belong in controlled application logic or reference data. Keep the constraint simple enough that its name explains it.
A CHECK constraint does not replace all validation. It reports the first statement failure, while a staging query can list every bad source row. Use staging checks for diagnosis and the constraint as the last defense.
ALTER TABLE dbo.Invoice
ADD CONSTRAINT CK_Invoice_Amount_Nonnegative
CHECK (Amount >= 0);Remember That UNKNOWN Can Pass
A CHECK expression that evaluates to UNKNOWN because a column is NULL is accepted. If Amount must exist and be nonnegative, declare it NOT NULL as well as adding the CHECK. Treat these as two separate parts of the rule.
I test NULL explicitly when adding a constraint. A developer can read CHECK (Amount greater than zero) and assume it rejects missing values. SQL’s three-valued logic says otherwise. A simple test case prevents a quiet gap.
For optional columns, UNKNOWN can be exactly what you want. A nullable EndDate can be absent until completion, while a non-NULL EndDate must follow StartDate. Write the rule so both cases are deliberate.
ALTER TABLE dbo.Task
ADD CONSTRAINT CK_Task_DateOrder
CHECK (EndDate IS NULL OR EndDate >= StartDate);Name Check Constraints for Operations
A generated name is hard to recognize in an error message or deployment script. Give the constraint a stable name that identifies the table and rule. Then a failed insert tells the operator what boundary was crossed.
I avoid stuffing the whole expression into the name. A short rule name plus a documented definition is easier to maintain. Names should be unique in the schema and consistent across environments, so the same deployment script can find them.
What would your load log show after this constraint fails? Capture the source row identifier and rule category in staging. The database error proves protection. The load log gives the source owner a correction list.
SELECT cc.name, cc.definition, cc.is_disabled,
cc.is_not_trusted
FROM sys.check_constraints AS cc
WHERE cc.parent_object_id = OBJECT_ID(N'dbo.Invoice');
Understand Trusted and Untrusted Status
A constraint can be enabled for future changes while remaining untrusted for existing rows. This happens when it is added or reenabled without checking all current data. The optimizer cannot make the same assumptions from an untrusted rule as it can from a trusted one.
I inspect is_not_trusted after deployments. A green application test does not prove historical rows passed the rule. To establish trust, validate existing data and enable the constraint with the appropriate WITH CHECK operation. Plan the scan and locks for a large table.
Do not mark a constraint trusted by changing metadata directly. Find and correct violating rows, then let SQL Server verify the table. Trust is an evidence state, not a label for a release note.
ALTER TABLE dbo.Invoice
WITH CHECK CHECK CONSTRAINT CK_Invoice_Amount_Nonnegative;Use the Optimizer Benefit Carefully
A trusted constraint can help SQL Server reason about possible values. It can simplify or eliminate work when a query predicate contradicts the rule. That is an additional benefit, not the main reason to enforce business truth.
I compare actual plans before claiming a performance gain. Optimizer choices depend on the query, constraint expression, and other metadata. A simple range rule can be useful, but a complex expression does not necessarily change the plan. Keep the constraint for correctness even if no query gets faster.
Avoid writing a false constraint to coax a plan. SQL Server and every writer will treat it as truth. A misleading rule can reject valid data or support a wrong inference. Business correctness comes first.
Deploy Without Surprising the Load
Before adding a constraint to an existing table, run the expression as a query to find violations. Decide how to correct them. Then schedule the validation operation with an understanding of table size and concurrent writes. A large scan can affect a busy system.
I include the application and ETL owners in the rollout. A previously accepted bad value will now fail. That is the intended protection, but the error path must be clear. A load should retain rejected rows and alert the right owner rather than retrying the same invalid batch.
Keep a rollback plan for deployment problems, but do not disable the rule as a permanent response to one bad source file. Fix the source or define a legitimate exception through a reviewed change.
Review Check Constraints as the Model Evolves
A CHECK rule can become stale when new business states are introduced. Review it alongside schema and reference data changes. A code list in a CHECK expression can be reasonable for a tiny stable domain, but a lookup table can be easier when values change under business ownership.
I run a catalog query during schema reviews to find disabled and untrusted check constraints. Those states deserve explanation. A constraint nobody trusts is a poor place to put a critical promise.
The database is the final shared boundary for row validity. Keep rules simple, name them, validate existing data, and test NULL cases. Then every writer meets the same rule, including the one you have not built yet.
What happens to existing rows when the constraint is added? SQL Server can validate them, and a failed validation means the table already violates the rule. I inspect the violating rows first and agree on a repair with the data owner. Adding a constraint without validating old data leaves a trust gap that can also limit optimization. A business rule earns its place in the schema when it protects both new writes and the existing table.
Related reading on this blog: CHECK CONSTRAINT to Allow Only Digits in Column and How to Add Constraint With No Validation? Interview Question of the Week #299.
A CHECK constraint is not an application hint, it is a rule the database can enforce for every writer.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
