StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat are some of the scenarios that can make foreign keys become untrusted
text
Body
copied!<p>I have an OLTP and an OLAP database where we keep a rolling 13 months of data in the OLAP. Several ETL job keeps the OLAP up to date with nightly jobs and none of the jobs disable foreign key while ETL is being performed. Occasionally, due to reasons I have not yet uncovered, some of my OLTP foreign keys become untrusted. I use the following script below (thanks to Brent Ozar's group) to identify and then fix these keys.</p> <p>My question is: Other than manually disabling and then forgetting to re-enable keys, what scenarios can cause a foreign key to become untrusted? I have scoured our ETL jobs and nothing sticks out.</p> <p><strong>Scripts to identify and then fix untrusted keys:</strong> </p> <p><strong>Identifying Untrusted Keys</strong></p> <p>The following script, compliments of the Brent Ozar group, will help identify untrusted keys. Note that this script must be run while connected to the given database you wish to examine.</p> <pre><code>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 SELECT '[' + s.name + '].[' + o.name + '].[' + i.name + ']' AS keyname from sys.check_constraints 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 AND i.is_disabled = 0 </code></pre> <p><strong>Repairing Untrusted Keys</strong></p> <p>Once you have identified untrusted keys, use the following script to re-enable them:</p> <p><em>ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName</em></p> <p>Large tables will take time so this should be performed during a maintenance window. You may find key values that do not adhere to the constraint rules since the keys were disabled. These values will need to be edited or the rows deleted in order to successfully re-enable the key.</p> <p><em>Alter Table Scripts for PriceGuide and PriceGuideDW</em></p> <p>I have made some modifications to the Brent Ozar scripts specifically for our databases. These scripts will generate the necessary alter table scripts to fix the untrusted keys:</p> <pre><code>Use PriceGuide Go SELECT 'ALTER TABLE [' + o.name + '] WITH CHECK CHECK CONSTRAINT ' + --'[' + 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 SELECT 'ALTER TABLE [' + o.name + '] WITH CHECK CHECK CONSTRAINT ' + --'[' + s.name + '].[' + o.name + ']. + '[' + i.name + '];' AS keyname from sys.check_constraints 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 AND i.is_disabled = 0 Use PriceGuideDW Go SELECT 'ALTER TABLE [' + o.name + '] WITH CHECK CHECK CONSTRAINT ' + --'[' + 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 SELECT 'ALTER TABLE [' + o.name + '] WITH CHECK CHECK CONSTRAINT ' + --'[' + s.name + '].[' + o.name + ']. + '[' + i.name + '];' AS keyname from sys.check_constraints 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 AND i.is_disabled = 0 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload