4 Replies Latest reply on Mar 6, 2015 5:43 AM by Halef

    Orion SQL database has non trusted foreign keys

    Seashore

      Hi

       

      A question about the database for Orion.

       

      I saw that in the SQL database for orion there are a few untrusted Foreign Keys, FK. (I have that in a new installed labb with 11.5RC1 and SAM 6.2R1 and in an installation that has been up for several years)

       

      If you run the following yeary you can see all untrusted foreign keys:

      SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname

      from sys.foreign_keys i

      INNER JOIN sys.objects o ON i.parent_object_id = o.object_id

      INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

      WHERE i.is_not_trusted = 1 AND i.is_not_for_replication = 0


      In my new installation I had these:
      [dbo].[NotificationTypePermissions].[FK_NotificationTypePermissions_NotificationTypeID]

      [dbo].[NotificationItems].[FK_NotificationItems_Type]

      [dbo].[NotificationMaintenanceRenewals].[FK_NotificationMaintenanceRenewals_RenewalID]

      [dbo].[NotificationBlogs].[FK_NotificationBlogs_BlogID]


      In my old installation I had some more:

      [dbo].[Migration_NPM_NV_WL_APS_DETAIL].[FK_NPM_NV_WL_APS_DETAIL_NPM_NV_WL_CONTROLLERS_DETAIL]

      [dbo].[Migration_NPM_NV_WL_INTERFACES_DETAIL].[FK_NPM_NV_WL_INTERFACES_DETAIL_NPM_NV_WL_APS_DETAIL]

      [dbo].[Migration_NPM_NV_WL_CLIENTS_DETAIL].[FK_NPM_NV_WL_CLIENTS_DETAIL_NPM_NV_WL_INTERFACES_DETAIL]

      [dbo].[VoipOperationResults_Detail].[FK_VoipOperationResults_Detail_VoipOperationInstanceID]

      [dbo].[VoipOperationResults_Detail].[FK_VoipOperationResults_Detail_VoipOperationResultTypeID]

      [dbo].[VoipOperationResults_Detail].[FK_VoipOperationResults_Detail_VoipOperationStatusID]

      [dbo].[NotificationTypePermissions].[FK_NotificationTypePermissions_NotificationTypeID]

      [dbo].[NotificationItems].[FK_NotificationItems_Type]

      [dbo].[NotificationMaintenanceRenewals].[FK_NotificationMaintenanceRenewals_RenewalID]

      [dbo].[NotificationBlogs].[FK_NotificationBlogs_BlogID]

       

      Non trusted FK's make SQL queries take longer time. So I tried to enable them with this query:

      ALTER TABLE [NotificationTypePermissions] WITH CHECK CHECK CONSTRAINT [FK_NotificationTypePermissions_NotificationTypeID]


      Most of them work but not the above one. Not in prod and not in new installed labb. I get:

      The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_NotificationTypePermissions_NotificationTypeID". The conflict occurred in database "Orion", table "dbo.NotificationItemTypes", column 'TypeID'


      Same thing at a third installation.


      So Solarwinds, what to do with them?


      /Thomas