Write SQL Server CHECK constraints that match the rule

A CHECK constraint named PositiveQuantity sounds like a promise that every row contains a positive quantity. If its expression is Quantity > 0 and the column allows NULL, that promise is incomplete. SQL Server rejects FALSE, while UNKNOWN caused by NULL can pass. The name of a constraint is documentation, not its execution semantics.

Describe the allowed states explicitly

The first table below demonstrates the gap: inserting NULL succeeds. If quantity is mandatory, use NOT NULL together with the positive-value check. If unknown quantity is a valid state, keep it nullable and document that meaning instead of pretending the check requires a number.

The second table models a publication state. A draft must have no publication time, and a published row must have one. Status is mandatory and restricted to the two supported values.

CREATE TABLE #WeakRule
(
    ItemId int PRIMARY KEY,
    Quantity int NULL CHECK (Quantity > 0)
);
INSERT #WeakRule VALUES (1, NULL);
SELECT ItemId, Quantity FROM #WeakRule;

CREATE TABLE #PublicationRule
(
    ItemId int PRIMARY KEY,
    Status varchar(10) NOT NULL
        CHECK (Status IN ('Draft', 'Published')),
    PublishedAt datetime2(0) NULL,
    CHECK
    (
        (Status = 'Draft' AND PublishedAt IS NULL)
        OR
        (Status = 'Published' AND PublishedAt IS NOT NULL)
    )
);
INSERT #PublicationRule VALUES
(1, 'Draft', NULL),
(2, 'Published', '20250115T12:00:00');

BEGIN TRY
    INSERT #PublicationRule VALUES (3, 'Published', NULL);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ConstraintError;
END CATCH;
SELECT * FROM #PublicationRule ORDER BY ItemId;
DROP TABLE #PublicationRule;
DROP TABLE #WeakRule;

The weak rule returns its NULL row. The publication table accepts the first two rows and rejects the third with a constraint error, leaving two records. Explicit IS NULL and IS NOT NULL tests avoid an accidental UNKNOWN in the relationship rule.

Write down a small truth table before implementing a multi-column condition. For this example, evaluate Draft with and without a date, Published with and without a date, an unsupported status, and NULL status. Then turn those cases into migration or application integration checks. Testing only valid inserts leaves the most important behavior unexamined.

Keep the expressions understandable. A long chain of negations may be logically correct yet difficult to extend safely. Separate independent domain rules from relationships between columns, and give each persistent constraint a meaningful name so an error can be diagnosed.

Know the boundary of a row constraint

A CHECK is suitable for invariants based on the row, such as a permitted range or an end date after a start date. It is not a scheduler that revisits existing rows as time passes. A condition involving the current clock is checked when relevant data is written, not continuously at midnight.

Likewise, do not build cross-row inventory or minimum-row-count guarantees by hiding queries inside a scalar function used by a CHECK. Other rows can change through paths that do not re-evaluate the original row's expression, and deletes have their own implications. Use the appropriate relational constraints or a transactional design that actually protects the shared invariant.

Foreign keys express membership in another table more directly than a custom lookup function. Unique constraints express uniqueness more reliably than counting apparent duplicates during validation. A business rule such as 'only one current assignment' may need a unique filtered index rather than a CHECK that merely validates each assignment's dates.

Constraints also do not replace authorization. A row can be structurally valid and still belong to a tenant the caller must not modify. Keep access checks and structural validation separate in the write contract.

Deploy the rule without hiding old violations

Adding a checked constraint to an existing table requires existing rows to satisfy it. Audit violations first and decide how to correct or quarantine them. Avoid silently filling missing values with invented defaults just to make the migration pass; that changes business data.

A constraint can be enabled while not trusted if existing rows were not validated. Inspect is_disabled and is_not_trusted in sys.check_constraints. When the data is ready, WITH CHECK CHECK CONSTRAINT is the pattern used to validate existing rows and establish trust for an existing constraint. Plan the validation work and locking on a large table.

Test updates as well as inserts. A transition from Draft to Published must set PublishedAt in the same statement under this rule; changing status first and date later creates an invalid intermediate row and fails. That failure is useful because it forces the operation to express a coherent state.

Finally, decide how the application maps constraint errors to user feedback. Preserve the database rule as the final defense, while offering early validation for convenience. When future states such as Archived are added, update the state model, migration, and transition tests together rather than weakening the constraint until the new code happens to work.

Technical references: Microsoft Learn: CHECK constraints · Microsoft Learn: sys.check_constraints.