Sql-server – is_not_trusted = 1 after Error-Free WITH CHECK CHECK CONSTRAINT

constraintforeign keysql serversql-server-2005

I'd like SQL Server 2005 SP4 to trust a certain foreign key constraint. Executing an ALTER TABLE statement that enables + checks the constraint returns Command(s) completed successfully yet sys.foreign_keys is_not_trusted stays set at 1.

Any ideas why this could be? Does ALTER TABLE sometimes silently swallow constraint errors?

ALTER TABLE {table} WITH CHECK CHECK CONSTRAINT {constraint_name}
-- returns: Command(s) completed successfully.

SELECT is_not_trusted, is_disabled
FROM sys.foreign_keys
WHERE name = {constraint_name}
-- returns 1, 0

Dropping and re-creating the constraint also completes successfully but the re-created constraint also isn't trusted.

ALTER TABLE {table} DROP CONSTRAINT {constraint_name};  
-- returns: Command(s) completed successfully.

ALTER TABLE {table} ADD CONSTRAINT {constraint_name}
FOREIGN KEY ({column_name})
REFERENCES {referenced_table_name} ({referenced_column})
ON DELETE CASCADE
ON UPDATE NO ACTION
NOT FOR REPLICATION;
-- returns: Command(s) completed successfully.

SELECT is_not_trusted, is_disabled
FROM sys.foreign_keys
WHERE name = {constraint_name}
-- returns 1, 0

For reference, the key column is SMALLINT NOT NULL.

Best Answer

Thanks to a How to make foreign key constraints trusted on StackOverflow, I figured out that the constraint was untrusted because it was marked NOT FOR REPLICATION. After re-creating the constraint without NOT FOR REPLICATION, all were trusted!

This behavior makes sense. If the constraint isn't replicated, other replication participants won't be enforcing it. When changes made by those other participants make their way back to the database I'm working with, there's no guarantee that the foreign key constraint's condition is satisfied—so NOT FOR REPLICATION forces the constraint to be marked is_not_trusted.

Related Question