2

I have several SQL Server 2005 databases with untrusted foreign keys. These keys are marked as "Not for Replication" (is_not_for_replication=1) in the sys.foreign_keys object catalog view.

I have run a script that captures these untrusted keys and runs 

ALTER TABLE [tablename] WITH CHECK 
   CHECK CONSTRAINT [keyname]

against each key. For all but one the results say that these keys have been validated (re-trusted?), but the sys.foreign_keys view still has them marked as untrusted.

Why is this object catalog view not reflecting the "re-trusted" keys as is_not_trusted=0? Is a "Not for Replication" foreign_key always untrusted? If not, how do I get the catalog view to reflect the "trusted" state of the foreign key?

Also, am I correct in assuming that as long as the foreign key is marked as "untrusted" it will not be factored in to any SQL Server execution plan?

Thanks for any comments or suggestions.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chris Zilligen
  • 121
  • 1
  • 4

1 Answers1

1

I have similar issue like you. What I found so far is that setting NOT FOR REPLICATION on a foreign key or a constraint always make it to be not trusted. Just to confirm: https://stackoverflow.com/a/16313703/1513907

If the constraint or the foreign key is not trusted then it is not used by the query optimizer, so it is not reflected in the execution plan. https://sqlserverfast.com/blog/hugo/2007/03/can-you-trust-your-constraints/

In order to make such key trusted again one need to drop it and create without setting NOT FOR REPLICATION.

I think there is a trade-off. NOT FOR REPLICATION allows to skip the constraint when DML operation is performed by the replication agent on the subscriber. So the data modification is faster. From the other side such constraint is not trusted so queries might be slower.

Community
  • 1
  • 1
scar80
  • 1,642
  • 2
  • 18
  • 36