Schema Compare and Foreign Keys Created WITH NOCHECK

• Ringo-394296

  Old Hand

  Points: 371

  More actions

  February 15, 2012 at 4:55 am

  #256865

  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:

  [font="Courier New"]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;[/font]

  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:

  [font="Courier New"]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;[/font]

  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.