Cannot get Foreign Key back to is_trusted

Question

Cannot get Foreign Key back to is_trusted

Mike Byrd

Default port

Points: 1458
More actions
September 3, 2010 at 9:53 am

#228657

As I understand the syntax the following code should change the is_not_trusted column in sys.foreign_keys to 0 (or error out if orphaned record, etc exists):
ALTER TABLE [Adapter].[ServiceConfiguration] WITH CHECK CHECK CONSTRAINT [FK_ServiceConfiguration_SysComponent]
When I run this statement I get no error msgs, but then when I then run following:
select fk.name, is_disabled,is_not_for_replication,is_not_trusted
FROM sys.foreign_keys fk
WHERE fk.name = 'FK_ServiceConfiguration_SysComponent'
I get:
name is_disabled is_not_for_replicationis_not_trusted
FK_ServiceConfiguration_SysComponent01 1
What am I doing wrong? I want the is_not_trusted value to go back to zero (0).
Mike Byrd

Viewing 9 posts - 1 through 8 (of 8 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you disable FK's, then insert, then renable, a FK is is_not_trusted = 1 if there is data in the child table that violates the FK constraint right (like inserting a zero in the FK child table's columns when all keys are > 0)

untill you clear the offending records, i don't think you can clear the flag;

if you were tor drop and recreate the key, you'd just get an error cannot create constraint...

can you modify and run this and see what the "bad" records are?

select *

FROM SysComponent

WHERE [foreignkeycolumnName] NOT IN

( SELECT [foreignkeycolumnName]

FROM ServiceConfiguration

WHERE [foreignkeycolumnName] IS NOT NULL

)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 2

Is this the add of a constraint and not a re-enable? Did you disable it for some reason? What is the constraint?

I don't think you are supposed to be able to add it if there are issues with rows. I see that here: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/907e1139-6b9d-4707-a279-d8330e238a8c

Steve Jones - SSC Editor SSC Guru Points: 736150 More actions · Answer 3

OK, Lowell's theory works for me. I can re-enable a constraint, with CHECK, and it succeeds, even if I have data in the table that conflicts with the FK.

That seems like a problem/bug for me, but I'll need to look through docs and see if it's expected behavior.

Mike Byrd Default port Points: 1458 More actions · Answer 4

This is after a re-enable of the constraint. Now I want to get the constraint back to a is trusted state.

Mike Byrd

Lowell SSC Guru Points: 323518 More actions · Answer 5

Mike Byrd (9/3/2010)
This is after a re-enable of the constraint. Now I want to get the constraint back to a is trusted state.

i thought re-enabling the constraint prevents future inserts or updatesfrom violating the constraint once it is re-enabled, but existing, invalid data prevents the constraint from returning to the trusted state.

are you sure there no invalid data exists that would violate the FK? i could build an example demonstrating that this is the issue, but it's aweful easy to overlook.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mike Byrd Default port Points: 1458 More actions · Answer 6

And I thought that re-enabling the FK constraint also only applied to future updates. And you can see from my example that the FK constraint is_disables is set to zero. I thought the WITH CHECK CHECK option would also reset the is_not_trusted column in sys.foreign_keys. This is what my question is about -- why does the is_not_trusted column reset to zero?

Mike Byrd

Mike Byrd Default port Points: 1458 More actions · Answer 7

Data is valid between the 2 tables:

The following query returns a empty result set:

select * from Adapter.ServiceConfiguration

where CreateComponentID NOT IN (select SysComponentID from SysComponent where SysComponentID IS Not NULL)

Mike Byrd

Mike Byrd Default port Points: 1458 More actions · Answer 8

OK, I think I've figured it out. The original definition of the FK included a "NOT FOR REPLICATION" clause. If I delete the clause and recreate the FK I then get a zero value for is_trusted with the CHECK option. Sure does seem like a bug to me, but guess that must be some rationale for it somewhere.

Mike Byrd