November 5, 2010 at 9:17 am
I am experiencing a problem where I am adding a FK to a table and every time it is added with the ON UPDATE clause it causes the CnstIsNotTrusted property to return true.
Does anyone know why this is happening? If you need more information about the setup let me know.
It's running on SQL Server 2005 Developer 9.0.4309 (haven't promoted changes off of dev yet due to this issue)
Thanks!
John
November 5, 2010 at 9:22 am
I made a mistake, it seems to be the NOT FOR REPLICATION clause that's doing this.
November 5, 2010 at 9:24 am
Are you doing this WITH NOCHECK? If you create a FK with nocheck, then this flag gets set (as it hasn't validated existing data and therefore can't be trusted).
I assume you're doing ON UPDATE CASCADE? Can you please post the DDL of the FK you're trying to add?
edit: n/m, just saw your update.
November 5, 2010 at 10:06 am
Here is the code sample (replaced table names and key names with bogus names)
This causes CnstIsNotTrusted to be 1
ALTER TABLE [dbo].[TableA] ADD
CONSTRAINT [FK__TableA__TableB__ReportNameS] FOREIGN KEY ([ReportNameS]) REFERENCES [dbo].[TableB] ([ReportNameS])
ON DELETE CASCADE
ON UPDATE CASCADE
NOT FOR REPLICATION
This causes CnstIsNotTrusted to be 0
ALTER TABLE [dbo].[TableA] ADD
CONSTRAINT [FK__TableA__TableB__ReportNameS] FOREIGN KEY ([ReportNameS]) REFERENCES [dbo].[TableB] ([ReportNameS])
ON DELETE CASCADE
ON UPDATE CASCADE
-- NOT FOR REPLICATION
November 5, 2010 at 11:06 am
What happens if you try to force WITH CHECK in the create statement?
November 19, 2010 at 11:43 am
I'm creating a new topic for this issue with a correct title. It will be called "CnstIsNotTrusted Returns True on Key Set with NOT_FOR_REPLICATION"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply