Untrusted foreign keys

This metric returns the number of foreign keys that have their is_not_trusted flag set to 1 in the sys.foreign_keys table. An untrusted foreign key may threaten a database's referential and data integrity.

Install metric...

Metrics install automatically if you have SQL Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, SQL Monitor, you can instantly install and run this metric on your servers.

This metric returns the number of foreign keys that have their is_not_trusted flag set to 1 in the sys.foreign_keys table.

A foreign key points to a primary key that must exist in another table, for example, column X in Table 1 must also be present in Table 2. The key protects this link, and only valid data can be inserted in the foreign key column. An untrusted foreign key may threaten a database’s referential and data integrity.

If the metric and alert tells you that untrusted foreign keys exist, find out which ones they are by running:

[sql]SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1 AND is_not_for_replication = 0[/sql]

For details of how to fix them, see Brent Ozar’s article: Blitz Result: Foreign Keys or Check Constraints Not Trusted.

Untrusted foreign keys, 5.0 out of 5 based on 3 ratings

Metric definition

Name

Untrusted foreign keys

Description

The number of foreign keys that have their is_not_trusted flag set to 1 in the sys.foreign_keys table. If foreign keys exist, find out which ones they are by running:

SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1 AND is_not_for_replication = 0
For details of how to fix them, see Brent Ozar's article: Blitz Result: Foreign Keys or Check Constraints Not Trusted.

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

0

Collection frequency

86400

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Untrusted foreign keys

Description

This alert is raised when the number of foreign keys detected exceeds the thresholds set. If foreign keys exist, find out which ones they are by running:
SELECT * FROM sys.foreign_keys WHERE is_not_trusted = 1 AND is_not_for_replication = 0
For details of how to fix them, see Brent Ozar's article: Blitz Result: Foreign Keys or Check Constraints Not Trusted.

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:0
Medium:Disabled
Low:Disabled

Note: These threshold settings are intended as guideline values. You may consider replacing them with values more suited to your environment.

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled