PrismitaLEARN · PRACTICE · GROW

What is a CHECK constraint and a DEFAULT constraint?

SQL Server Keys & Constraints Basic Fresher1–2 Yrs Conceptual 1 min read

Quick Answer

A CHECK constraint enforces a Boolean rule on a row's values — CHECK (Price >= 0), CHECK (EndDate > StartDate). A DEFAULT constraint supplies a value when an INSERT doesn't specify the column — DEFAULT (SYSUTCDATETIME()) for a CreatedAt.

A CHECK constraint is a rule on the data itself — price can't be negative, end date has to be after start date — and the engine rejects any insert or update that breaks it. A DEFAULT constraint fills in a value when the insert doesn't provide one, like defaulting a CreatedAt to the current UTC time. One subtlety with CHECK: it lets a row through if the condition is true or unknown, so a CHECK on age still allows a null age unless you also make the column NOT NULL.

  • CHECK can reference multiple columns in the same row but not other rows or tables (use a trigger or an indexed view for that).
  • A CHECK allows a row through if the expression is TRUE or UNKNOWN (NULL) — so CHECK (Age >= 18) still permits NULL age.
  • Constraints are declarative, checked by the engine, visible in metadata, and faster/safer than equivalent trigger logic.
  • Keep validation in both the app (good UX) and the database (last line of defence) for critical invariants.
All SQL Server questions More on Keys & Constraints