Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Schema Compare and Foreign Keys Created WITH NOCHECK Expand / Collapse
Author
Message
Posted Wednesday, February 15, 2012 4:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 9:29 AM
Points: 7, Visits: 85
Hi All,

We're trying to create a schema synchronization script by comparing a database project in Visual Studio/TFS (source) to a deployed database (destination). The script produced will be used to bring the populated destination database inline with the code checked into Visual Studio/TFS.

In TFS foreign keys are defined in the form:

ALTER TABLE [dbo].[foo]
ADD CONSTRAINT [FK_Foo_Bar] WITH NOCHECK

FOREIGN KEY ([Bar_Id]) REFERENCES [Dbo].[Bar] ([Bar_Id])
ON DELETE NO ACTION ON UPDATE NO ACTION;


In the destination database all foreign keys are inactive and marked as untrusted and the underlying data violates the key's logic, i.e. if we were to try and re-enable the foreign keys with CHECK CHECK, it would fail. For info. the only reason that we have the foreign keys in this database is to enable any design tool to show data relationships, we actively do not want the FKs to enforce integrity for operational/performance reasons.

When we compare the schema, a difference is picked up between the foreign keys and the source foreign key looks like:

ALTER TABLE [dbo].[foo]
ADD CONSTRAINT [FK_Foo_Bar]

FOREIGN KEY ([Bar_Id]) REFERENCES [Dbo].[Bar] ([Bar_Id])
ON DELETE NO ACTION ON UPDATE NO ACTION;


So the schema comparison tool is stripping off/ignoring the WITH NOCHECK clause and attempting to create the foreign key as active, which causes the script to fail when run over the database (due to the data not adhering to it as described above).

Does anyone know of a setting which controls the behavior of scripting FKs, i.e. ignoring this option in the key's script or setting a default of active/inactive at the sync level? Or indeed any information regarding this behavior at all would be very useful?

Thanks in advance

Our environment is as follows:
Destination database is on a SQL Server 2008 Enterprise (Build 10.0.2531.0) server and we're using Visual Studio 2008, 9.0.30729.1.

Post #1252412
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse