Cleaning Up Untrusted FKs

Question

Post reply

Cleaning Up Untrusted FKs

Del Lee

SSCrazy

Points: 2781
More actions
March 8, 2016 at 8:04 am

#399820

Hi, there are a number of untrusted foreign keys in one of our test databases, and I'm trying to clean them up. I'm identifying them with the following statement:
SELECT object_name(parent_object_id) as Parent
, object_name(referenced_object_id) as RefObj
, *
FROM sys.foreign_keys AS f Where Is_Not_Trusted = 1
order by object_name(parent_object_id), object_name(referenced_object_id)
Then, I'm issuing the following command:
ALTER TABLE mytablename WITH CHECK CHECK CONSTRAINT myfkconstraintname
Substituting the table name and constraint name as appropriate. The message indicates that the command was successful. However, after doing this, the fk constraint remains untrusted but the modify_date changes in the sys.foreign_keys table. Any idea what is going on?

Del Lee

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Is the constraint marked "NOT FOR REPLICATION"?

In this case I'm afraid you will have to recreate it to make it trusted.

-- Gianluca Sartori

Del Lee SSCrazy Points: 2781 More actions · Answer 2

spaghettidba (3/8/2016)
Is the constraint marked "NOT FOR REPLICATION"?
In this case I'm afraid you will have to recreate it to make it trusted.

Yes, it is. I've attempted to drop and re add, but it's still not trusted. Do you mean to say I can't mark it as "NOT FOR REPLICATION?"

Here's my code:

ALTER TABLE [Pending].[CltAdmUsers] DROP CONSTRAINT [FK_CltAdmUsers_CltData]

ALTER TABLE [Pending].[CltAdmUsers] WITH CHECK ADD CONSTRAINT [FK_CltAdmUsers_CltData] FOREIGN KEY([CltID], [CltTrkID])

REFERENCES [Pending].[CltData] ([CltID], [CltTrkID])

NOT FOR REPLICATION

GO

Del Lee

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Sorry, looking closer at BOL https://msdn.microsoft.com/en-us/library/ms152529(v=sql.105).aspx it looks like when a constraint is NOT FOR REPLICATION it is always not trusted. You can leave it as it is.

-- Gianluca Sartori

Del Lee SSCrazy Points: 2781 More actions · Answer 4

Thanks for the specific link, although it wasn't obvious in the article. They could make that a little bit clearer. I do see now that Brent Ozar's script for identifying untrusted foreign keys filters out those with notforreplication=1.

Thanks very much for your help today! 🙂

Del Lee