Monday, December 29, 2014

SQL Server 2008 R2 sys.foreign_keys, is_not_trusted column

Hi, We are using SQL Server 2008 R2 database and we have different environments like, DEV, QA, UAT, Pre-Prod & Prod. We are TFS 2012 as our source code repository and when I tried to publish my database project to DEV it worked fine but to publish it to QA I got the below error.


"The ALTER TABLE statement conflicted with the FOREIGN KEY constraint"


When I tried to drop & create this constraint in DEV it was working absolutely fine, but in QA it threw that error message. After further investigation when I ran the below query in DEV, QA.



SELECT [name], type_desc, is_disabled, is_not_trusted
FROM sys.foreign_keys

In DEV is_not_trusted = 0 & QA is_not_trusted = 1


Is this affected by "NOT FOR REPLICATION" (We were modifying few tables to work for Replication very recently and this was the first time we were doing a deploy to QA server after this change) on the table & is_not_for_replication column in the sys.foreign_keys (but is_not_for_replication = 0 for this table)?


How does it get set?


Thanks in advance......




Ione


No comments:

Post a Comment