Getting 'WITH CHECK' output in sql script using VS2010 SQL Server Database project?

Question

I have a foreign key constraint I have added to a VS2010 SQL Server Database Project which I have modified to specify 'WITH CHECK' on the constraint.

ALTER TABLE [dbo].[Foo] WITH CHECK
ADD CONSTRAINT [FK_FooBar] 
FOREIGN KEY ([BarKey])
REFERENCES [dbo].[Bar] ([Key])

The .sql output from the generated schema has 'WITH NOCHECK' inserted. This seems to be the default if you don't specify anything at all. The .dbschema file generated for the project does not specify CHECK or NOCHECK so I am a little confused where it is coming from.

Anyone know how to do this?

Cheers

Adam Wenger Adam Wenger · Accepted Answer · 2011-10-10T19:57:22

Rasta,

When you create a new constraint, the WITH CHECK is the default, so the .dbschema should not have to specify that for you. http://msdn.microsoft.com/en-us/library/ms190273.aspx

From looking at the .sql file that is created for my database project, I saw that my foreign keys also have WITH NOCHECK when created. This is to prevent constraint violations until the entire structure is created. The last few lines of the .sql file that is generated for you should be your foreign keys being checked now that the structure is complete. For the example above, they would look like this:

ALTER TABLE [dbo].[Foo] WITH CHECK CHECK CONSTRAINT [FK_FooBar];

These should make the .sql file give you the desired results.

Getting 'WITH CHECK' output in sql script using VS2010 SQL Server Database project?

1 Answers