What is Trusted Constraint in SQL Server? – Interview Question of the Week #210

Question: What is a trusted constraint in SQL Server?

Answer: For a CHECK or foreign key constraint, “trusted” means SQL Server has validated the existing data against it. Being enabled and being trusted are separate states.

A reader asked this in my SQL Server Performance Tuning Practical Workshop. Rather than start with a definition, I used a CHECK on Column1 > 0. I still dislike the negative name is_not_trusted: zero means trusted, while one means untrusted. The example makes that easier to remember.

-- Run in a disposable user database.
IF OBJECT_ID(N'dbo.SqlaTrust184745', N'U') IS NOT NULL
    THROW 50001, 'The experiment table already exists.', 1;
CREATE TABLE dbo.SqlaTrust184745
 (ID int NOT NULL, Column1 int NOT NULL,
  CONSTRAINT CK_SqlaTrust184745 CHECK (Column1 > 0));
INSERT dbo.SqlaTrust184745 VALUES (1, 5);
DECLARE @States table (StepOrder int, Stage varchar(50), is_disabled bit, is_not_trusted bit);
INSERT @States SELECT 1, 'Created', is_disabled, is_not_trusted
FROM sys.check_constraints WHERE parent_object_id = OBJECT_ID(N'dbo.SqlaTrust184745');

ALTER TABLE dbo.SqlaTrust184745 NOCHECK CONSTRAINT CK_SqlaTrust184745;
INSERT dbo.SqlaTrust184745 VALUES (2, -1);
INSERT @States SELECT 2, 'Disabled; invalid old row inserted', is_disabled, is_not_trusted
FROM sys.check_constraints WHERE parent_object_id = OBJECT_ID(N'dbo.SqlaTrust184745');

ALTER TABLE dbo.SqlaTrust184745 CHECK CONSTRAINT CK_SqlaTrust184745;
INSERT @States SELECT 3, 'Enabled without checking old rows', is_disabled, is_not_trusted
FROM sys.check_constraints WHERE parent_object_id = OBJECT_ID(N'dbo.SqlaTrust184745');
BEGIN TRY
    ALTER TABLE dbo.SqlaTrust184745 WITH CHECK CHECK CONSTRAINT CK_SqlaTrust184745;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

-- Correct the intentionally invalid test row, then validate all existing rows.
DELETE dbo.SqlaTrust184745 WHERE ID = 2;
ALTER TABLE dbo.SqlaTrust184745 WITH CHECK CHECK CONSTRAINT CK_SqlaTrust184745;
INSERT @States SELECT 4, 'Enabled and existing rows checked', is_disabled, is_not_trusted
FROM sys.check_constraints WHERE parent_object_id = OBJECT_ID(N'dbo.SqlaTrust184745');
SELECT Stage, is_disabled, is_not_trusted FROM @States ORDER BY StepOrder;
DROP TABLE dbo.SqlaTrust184745;

The states are: created 0, 0; disabled 1, 1; re-enabled without validating old data 0, 1; and enabled after successful validation 0, 0. The first value is is_disabled, the second is is_not_trusted.

The new example deliberately inserts -1 while the constraint is disabled. CHECK CONSTRAINT enables checking for subsequent changes but does not inspect that old row. WITH CHECK CHECK attempts to validate it and raises error 547. After correcting the test data, the same statement succeeds and restores trust.

The two CHECK keywords are intentional. WITH CHECK asks SQL Server to validate existing rows; CHECK CONSTRAINT enables the rule. An untrusted constraint does not prove the data is bad, but SQL Server cannot assume all existing rows obey it. A trusted constraint can help the optimizer with valid deductions; it is not a promise that every query gets faster.

For a real table, investigate violations and the cost and locking of validation before changing the state. Do not disable a useful rule as a performance shortcut. This disposable example cleans up only its own table.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.