June 29, 2010 at 11:06 am
I've got a 3rd party application with over 600 untrusted FK constraints that I would like to make trusted, for data integrity and query performance.
Based on some web searching and BOL, I first used DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS to search all FK constraints on the database for orphaned or mismatched rows. DBCC reported no errors.
Next, I went on to make them trusted:
exec sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
This fixed about 400 FK constraints and made them trusted, but I've still got over 200 FK constraints that are untrusted, based on this query:
SELECT object_name(parent_object_id) As TableName, *
FROM sys.foreign_keys
WHERE IS_NOT_TRUSTED = 1
ORDER BY TableName, [name]
What am I missing that DBCC reports no FK violations, but ALTER TABLE won't make the constraints trusted?
Here's an example of the original T-SQL from SSMS that created one of the stubborn hold-outs (doesn't seem different from the ones I was able to convert to trusted). The parent table is TransDoc, and the child table w/ the FK constraint is BillingBatch:
ALTER TABLE [dbo].[BillingBatch] WITH NOCHECK ADD CONSTRAINT [FK_BillingBatch_Transdoc] FOREIGN KEY([TransDOCID])
REFERENCES [dbo].[Transdoc] ([TransDocID])
GO
ALTER TABLE [dbo].[BillingBatch] CHECK CONSTRAINT [FK_BillingBatch_Transdoc]
I did a manual check as a backup to DBCC to look for either NULL values in BillingBatch.TransDocID, or for values in BillingBatch.TransDocID that don't exist in TransDoc.TransDocID. Neither query returned any rows, which is correct and consistent with the results from DBCC.
If I drop and then recreate the FK constraint using "WITH CHECK", then the constraint is successfully created without error, and it shows as trusted.
What am I missing?
Thanks,
Rich
July 8, 2010 at 7:47 am
Well, I've got an answer from outside SQL Server Central, so I'm posting it back here:
The FK constraints that cannot be made trusted using an ALTER TABLE statement were created as NOT FOR REPLICATION. Apparently that keeps the constraint from being set to trusted. Looks like you have to drop/recreate the constraint.
Maybe someone else can provide more info...
Rich
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply