SQL Server lets you add a CHECK or FOREIGN KEY constraint WITH NOCHECK so existing rows are not verified. What state does that leave the constraint in, what does the query optimizer do differently as a result, and how would you find and repair such constraints later?

seniorshould knowfreq 24%

answer

Enabled but untrusted: is_not_trusted = 1
New rows enforced, history unproven
Untrusted → no join elimination, no predicate simplification
Repair: clean data, then WITH CHECK CHECK CONSTRAINT
Same idea as Postgres NOT VALID + VALIDATE

basics

~20 s

The constraint is enforced for new writes but marked untrusted, so the optimizer will not reason from it — losing simplifications like join elimination and predicate-based partition elimination. Fix by cleaning the data, then ALTER TABLE ... WITH CHECK CHECK CONSTRAINT to rescan and re-trust it.

solid answer

~60 s

WITH NOCHECK skips the validation scan, so the ALTER is quick, but the constraint lands enabled and untrusted: is_not_trusted = 1 in sys.check_constraints / sys.foreign_keys. New and modified rows are enforced; existing rows are unproven.

The consequence people miss is in the optimizer. A trusted constraint is a fact the planner can reason from — it can eliminate a join to a parent table when nothing from the parent is selected and a trusted foreign key proves the row exists, and it can use trusted CHECK constraints to skip tables in a partitioned view or filtered scenario. An untrusted constraint proves nothing about existing rows, so none of those simplifications apply. You silently lose plan quality.

To repair: find them with a catalog query, remove or fix the violating rows, then ALTER TABLE t WITH CHECK CHECK CONSTRAINT fk_name. That performs the scan and clears the untrusted flag — so it costs the lock and scan you deferred earlier, and should be scheduled accordingly. It is the same idea as PostgreSQL's NOT VALID plus VALIDATE CONSTRAINT.

`WITH NOCHECK` skips the validation scan, so the ALTER is quick, but the constraint lands **enabled and untrusted**: `is_not_trusted = 1` in `sys.check_constraints` / `sys.foreign_keys`. New and modified rows are enforced; existing rows are unproven. The consequence people miss is in the optimizer. A *trusted* constraint is a fact the planner can reason from — it can eliminate a join to a parent table when nothing from the parent is selected and a trusted foreign key proves the row exists, and it can use trusted CHECK constraints to skip tables in a partitioned view or filtered scenario. An untrusted constraint proves nothing about existing rows, so none of those simplifications apply. You silently lose plan quality. To repair: find them with a catalog query, remove or fix the violating rows, then `ALTER TABLE t WITH CHECK CHECK CONSTRAINT fk_name`. That performs the scan and clears the untrusted flag — so it costs the lock and scan you deferred earlier, and should be scheduled accordingly. It is the same idea as PostgreSQL's `NOT VALID` plus `VALIDATE CONSTRAINT`.

code

sql · 6 lines

sql

ALTER TABLE dbo.orders WITH NOCHECK
  ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
  REFERENCES dbo.customers (id);

-- after the historical rows are repaired
ALTER TABLE dbo.orders WITH CHECK CHECK CONSTRAINT fk_orders_customer;

go deeper

for a junior

Know that WITH NOCHECK skips checking the rows already in the table, and that new rows are still checked.

for a middle

Name the untrusted state and the catalog flag, and know the command that verifies and re-trusts the constraint.

for a senior

Explain the optimizer consequences — lost join elimination and predicate simplification — and describe an audit plus batched repair, noting the deferred scan cost comes due when you re-trust.

for a principal

Treat untrusted constraints as accumulating technical debt with a measurable plan-quality cost, mandate a catalog audit in the health check, and map the pattern to the equivalent staged mechanism on other engines.

What WITH NOCHECK actually produces

The syntax is confusing because two different CHECK words appear in it. ALTER TABLE t WITH NOCHECK ADD CONSTRAINT ... means "add this constraint but do not verify the rows already in the table". The result is a constraint that is:

Enabled — every subsequent INSERT and UPDATE is checked against it.
Untrusted — the server records that it cannot vouch for the existing rows.

The flag lives in the catalog: sys.check_constraints.is_not_trusted and sys.foreign_keys.is_not_trusted. It is also set when a constraint is disabled with NOCHECK CONSTRAINT and later re-enabled without a verifying scan — a very common way for untrusted constraints to appear silently after a bulk load.

Why untrusted matters: the optimizer

Most people can explain that the constraint still enforces new data and stop there. The interesting half is what the query optimizer does with constraints, because a constraint is not only enforcement — it is information.

With a trusted foreign key, the optimizer knows that every non-NULL child value has exactly one matching parent row. That licenses join elimination: if a query joins a child to its parent but selects nothing from the parent and filters nothing on it, the join can be removed entirely, because it cannot change the row count. Views that join a dozen lookup tables become dramatically cheaper. With the same constraint untrusted, the optimizer must keep the join, because for all it knows some child row has no parent and would be dropped by the join.

With a trusted CHECK constraint the optimizer knows every row satisfies a predicate, so it can prove a table contributes nothing to a query whose predicate contradicts it — the basis of partition elimination in partitioned views and of general predicate simplification. Untrusted, it must scan.

The symptom in the field is exactly this: after a bulk load or a migration, a report that used to run in seconds now scans tables it never touched before, and nothing about the query, the statistics or the indexes has changed. The constraints went untrusted.

Finding them

Audit the catalog. Untrusted constraints are invisible in ordinary work and accumulate quietly:

sql

SELECT OBJECT_NAME(parent_object_id) AS table_name, name
FROM sys.foreign_keys WHERE is_not_trusted = 1
UNION ALL
SELECT OBJECT_NAME(parent_object_id), name
FROM sys.check_constraints WHERE is_not_trusted = 1;

This belongs in a scheduled health check, not in an incident. Also worth auditing separately: disabled constraints (is_disabled = 1), which enforce nothing at all and are far worse.

Repairing them

Find the violating rows. For a foreign key, an anti-join against the parent; for a CHECK, the negation of its predicate. There may be none — many constraints are untrusted purely because someone used WITH NOCHECK out of caution, and the data was clean all along.
Fix or remove them. In batches, so no single transaction holds long locks.
Re-trust: ALTER TABLE t WITH CHECK CHECK CONSTRAINT fk_name; — the first CHECK says "verify existing rows", the second CHECK CONSTRAINT names the operation of enabling it. This performs the scan you originally skipped and clears is_not_trusted.

The important operational point: re-trusting costs exactly what WITH NOCHECK deferred — the scan and its locking. You have not avoided the work, you have chosen when to pay it, which is usually the right trade because you can pay it during a quiet window instead of during the release.

The parallel with other engines

This is the same architectural idea as PostgreSQL's ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT: separate "start enforcing now" from "prove the history". Both engines also share the planner consequence — PostgreSQL will not use an unvalidated CHECK for constraint exclusion or partition pruning, exactly as SQL Server will not reason from an untrusted one. If you learn one, you understand the other; only the spelling and the catalog view differ.

When WITH NOCHECK is legitimate

Staged rollout on a large table, where you want new-row enforcement immediately and the scan later.
Restoring or reloading a table where you already know the source is consistent and re-verification is redundant — though "already know" deserves scepticism, and a later re-trust pass costs little.

What is not legitimate is treating it as the permanent end state. An untrusted constraint is a deferred obligation; if nothing tracks it, it becomes a permanent, invisible tax on every plan the optimizer builds against that table.

## What WITH NOCHECK actually produces The syntax is confusing because two different `CHECK` words appear in it. `ALTER TABLE t WITH NOCHECK ADD CONSTRAINT ...` means "add this constraint but do not verify the rows already in the table". The result is a constraint that is: - **Enabled** — every subsequent INSERT and UPDATE is checked against it. - **Untrusted** — the server records that it cannot vouch for the existing rows. The flag lives in the catalog: `sys.check_constraints.is_not_trusted` and `sys.foreign_keys.is_not_trusted`. It is also set when a constraint is disabled with `NOCHECK CONSTRAINT` and later re-enabled without a verifying scan — a very common way for untrusted constraints to appear silently after a bulk load. ## Why untrusted matters: the optimizer Most people can explain that the constraint still enforces new data and stop there. The interesting half is what the query optimizer does with constraints, because a constraint is not only enforcement — it is *information*. With a **trusted** foreign key, the optimizer knows that every non-NULL child value has exactly one matching parent row. That licenses **join elimination**: if a query joins a child to its parent but selects nothing from the parent and filters nothing on it, the join can be removed entirely, because it cannot change the row count. Views that join a dozen lookup tables become dramatically cheaper. With the same constraint untrusted, the optimizer must keep the join, because for all it knows some child row has no parent and would be dropped by the join. With a **trusted** CHECK constraint the optimizer knows every row satisfies a predicate, so it can prove a table contributes nothing to a query whose predicate contradicts it — the basis of partition elimination in partitioned views and of general predicate simplification. Untrusted, it must scan. The symptom in the field is exactly this: after a bulk load or a migration, a report that used to run in seconds now scans tables it never touched before, and nothing about the query, the statistics or the indexes has changed. The constraints went untrusted. ## Finding them Audit the catalog. Untrusted constraints are invisible in ordinary work and accumulate quietly: ```sql SELECT OBJECT_NAME(parent_object_id) AS table_name, name FROM sys.foreign_keys WHERE is_not_trusted = 1 UNION ALL SELECT OBJECT_NAME(parent_object_id), name FROM sys.check_constraints WHERE is_not_trusted = 1; ``` This belongs in a scheduled health check, not in an incident. Also worth auditing separately: *disabled* constraints (`is_disabled = 1`), which enforce nothing at all and are far worse. ## Repairing them 1. **Find the violating rows.** For a foreign key, an anti-join against the parent; for a CHECK, the negation of its predicate. There may be none — many constraints are untrusted purely because someone used `WITH NOCHECK` out of caution, and the data was clean all along. 2. **Fix or remove them.** In batches, so no single transaction holds long locks. 3. **Re-trust:** `ALTER TABLE t WITH CHECK CHECK CONSTRAINT fk_name;` — the first `CHECK` says "verify existing rows", the second `CHECK CONSTRAINT` names the operation of enabling it. This performs the scan you originally skipped and clears `is_not_trusted`. The important operational point: re-trusting costs exactly what `WITH NOCHECK` deferred — the scan and its locking. You have not avoided the work, you have chosen *when* to pay it, which is usually the right trade because you can pay it during a quiet window instead of during the release. ## The parallel with other engines This is the same architectural idea as PostgreSQL's `ADD CONSTRAINT ... NOT VALID` followed by `VALIDATE CONSTRAINT`: separate "start enforcing now" from "prove the history". Both engines also share the planner consequence — PostgreSQL will not use an unvalidated CHECK for constraint exclusion or partition pruning, exactly as SQL Server will not reason from an untrusted one. If you learn one, you understand the other; only the spelling and the catalog view differ. ## When WITH NOCHECK is legitimate - **Staged rollout on a large table**, where you want new-row enforcement immediately and the scan later. - **Restoring or reloading a table** where you already know the source is consistent and re-verification is redundant — though "already know" deserves scepticism, and a later re-trust pass costs little. What is not legitimate is treating it as the permanent end state. An untrusted constraint is a deferred obligation; if nothing tracks it, it becomes a permanent, invisible tax on every plan the optimizer builds against that table.

Give a concrete query optimisation that a trusted foreign key enables and an untrusted one does not.
Join elimination. If a view joins orders to customers but the query selects and filters nothing from customers, a trusted foreign key proves every order has exactly one matching customer, so the join cannot change the result and can be removed. Untrusted, the optimizer must keep the join because an orphan order would legitimately be dropped by it.
How do constraints become untrusted without anyone typing WITH NOCHECK?
The most common route is a bulk load: someone disables the constraint with NOCHECK CONSTRAINT to speed up the load, then re-enables it with CHECK CONSTRAINT — which enables without verifying and leaves it untrusted. Re-enabling with WITH CHECK CHECK CONSTRAINT is what restores trust, and the difference is easy to miss.

saying these in an interview costs you the question

Thinking an untrusted constraint is not enforced at all — it is enforced for new writes
Confusing untrusted with disabled; a disabled constraint enforces nothing
Ignoring the optimizer consequence and describing only the data-integrity side
Believing re-trusting is free — it performs the scan and locking that were deferred
Leaving untrusted constraints permanently with no audit to find them

SQL Server lets you add a CHECK or FOREIGN KEY constraint WITH NOCHECK so existing rows are not verified. What state does that leave the constraint in, what does the query optimizer do differently as a result, and how would you find and repair such constraints later?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What WITH NOCHECK actually produces

Why untrusted matters: the optimizer

Finding them

Repairing them

The parallel with other engines

When WITH NOCHECK is legitimate

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions3 questions

▾related topics3 topics

What WITH NOCHECK actually produces

Why untrusted matters: the optimizer

Finding them

Repairing them

The parallel with other engines

When WITH NOCHECK is legitimate

answer

go deeper

context