Untrustworthy foreign keys that just can't be trusted

• JohnnyDBA

  SSCertifiable

  Points: 6355

  More actions

  September 30, 2010 at 10:05 am

  #157233

  When I check the trustworthiness of a few foreign key constraints, they keep showing not trusted even after I execute a CHECK on the constraint. Two of the keys that continue to show up are in the reporting services "reportserver" database and one is in one of our application databases.

  Example:

  USE [reportserver]

  GO

  SELECT OBJECTPROPERTY([object_id], 'CnstIsNotTrusted') FROM sys.objects WHERE [name] = 'FK_ReportSchedule_Subscriptions' -- table: ReportSchedule

  SELECT OBJECTPROPERTY([object_id], 'CnstIsNotTrusted') FROM sys.objects WHERE [name] = 'FK_Subscriptions_Catalog' -- table: Subscriptions

  The fixes I am running for the examples above are:

  USE [ReportServer] ALTER TABLE [ReportSchedule] WITH CHECK CHECK CONSTRAINT [FK_ReportSchedule_Subscriptions]

  USE [ReportServer] ALTER TABLE [Subscriptions] WITH CHECK CHECK CONSTRAINT [FK_Subscriptions_Catalog]

  We had many others on the servers that had this bit set to 1 and after running the fix they are fine. Only these last few are continuing to be an issue. Are there any conditions that would cause this flag not to be able to be unset?

  Thanks!

  John

• JohnnyDBA

  SSCertifiable

  Points: 6355

  More actions

  September 30, 2010 at 10:35 am

  #1229728

  I found that dropping / re-adding the foreign key constraint will reset the bit.

  If there is a better solution please let me know.