0

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.

  • What happens if you drop and re-create the constraint WITH CHECK? I wonder if the is_not_trusted bit is somehow tied to how the FK was originally created, much like is_policy_checked works for sql auth logins (in fact all that does is tell you whether check_policy was on at the time the password is set, it reflects 1 even if the policy was not, in fact, checked). – Aaron Bertrand Nov 9 '15 at 16:24
  • @AaronBertrand, after dropping + recreating, the constraint is still reported as is_not_trusted. :-( Strange! – Ben Gribaudo Nov 10 '15 at 21:51
  • You'll have to provide more specific details - it may be something about the constraint or the data type that is causing it to be untrusted. – Aaron Bertrand Nov 10 '15 at 21:57
  • @AaronBertrand, question edited to provide key column data type and constraint definition. – Ben Gribaudo Nov 11 '15 at 17:14
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.