有哪些方案可以使外键变得不可信(What are some of the scenarios that can make foreign keys become untrusted)

我有一个OLTP和一个OLAP数据库，我们在OLAP中保存了13个月的数据。多个ETL作业使OLAP与夜间作业保持同步，并且在执行ETL时，没有任何作业禁用外键。偶尔，由于我尚未发现的原因，我的一些OLTP外键变得不可信。我使用下面的脚本（感谢Brent Ozar的小组）来识别并修复这些密钥。

我的问题是：除了手动禁用然后忘记重新启用密钥之外，哪些方案可能导致外键变得不可信？我已经完成了我们的ETL工作，没有任何事情发展。

用于标识然后修复不受信任密钥的脚本：

识别不受信任的密钥

以下脚本，Brent Ozar组的赞美，将有助于识别不受信任的密钥。请注意，必须在连接到要检查的给定数据库时运行此脚本。

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

修复不受信任的密钥

识别出不受信任的密钥后，请使用以下脚本重新启用它们：

ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName

大型表需要时间，因此应在维护窗口期间执行。由于禁用了密钥，因此您可能会发现不符合约束规则的键值。需要编辑这些值或删除行才能成功重新启用密钥。

更改PriceGuide和PriceGuideDW的表脚本

我已经专门为我们的数据库对Brent Ozar脚本做了一些修改。这些脚本将生成必要的alter table脚本以修复不受信任的密钥：

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

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.

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.

Scripts to identify and then fix untrusted keys:

Identifying Untrusted Keys

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.

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

Repairing Untrusted Keys

Once you have identified untrusted keys, use the following script to re-enable them:

ALTER TABLE MyTableName WITH CHECK CHECK CONSTRAINT MyConstraintName

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.

Alter Table Scripts for PriceGuide and PriceGuideDW

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:

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