SQL Server Check/NoCheck difference in generated scripts

Question

I am trying to sync up the schemas between to different databases. Basically, I ran tasks->Generate Scripts with SQL Server Management Studio (2005) on both databases and am comparing the output with a diff tool.

For some reason, one script adds the constraint WITH CHECK and one WITH NO CHECK, followed by both constraints being re-enabled.

I for the first database I get:

ALTER TABLE [dbo].[Profile]  WITH CHECK ADD  CONSTRAINT [FK_Profile_OrganizationID] FOREIGN KEY([OrganizationID])
REFERENCES [dbo].[Organization] ([OrganizationID])
GO
ALTER TABLE [dbo].[Profile] CHECK CONSTRAINT [FK_Profile_OrganizationID]
GO

The second database generates as

ALTER TABLE [dbo].[Profile]  WITH NOCHECK ADD  CONSTRAINT [FK_Profile_OrganizationID] FOREIGN KEY([OrganizationID])
REFERENCES [dbo].[Organization] ([OrganizationID])
GO
ALTER TABLE [dbo].[Profile] CHECK CONSTRAINT [FK_Profile_OrganizationID]
GO

So I have two questions:

1. Is the end result the same? (Edit: It seems that a lot of people are picking up on only the first statement of the two scripts. I am interested in the end result of the entirety of both scripts.)

2. If the end result is the same, why does Management Studio generate them differently for different databases?

Oxfordshire

Answer 1

The end result is not the same!

SQL Server will not trust the uniqueness of the FK is it is not checked. This means additional processing is required if you use the column in a query.
Long story short is that you should get SQL Server to check the column so it's considered trusted.

As for why they're different from different servers, check the isnottrusted column in sys.foreign_keys. This may affect what SSMS is generating?

For more of a rant on this, check my other answer that relates to FK & NO CHECK/ CHECK options.

Erective

However, if you look at the two scripts scripts, immediately after adding the constraint, there is ALTER TABLE [dbo].[Profile] CHECK CONSTRAINT [FK_Profile_OrganizationID] GO Doesn't that check the constraints at that point? – Oxfordshire

As for the second part of your answer, you are indeed correct that is_not_trusted is set on one of the databases, and not on the other. What affect does this have? – Oxfordshire

Found this article: sqlblog.com/blogs/tibor_karaszi/archive/2008/01/12/… So it appears that the second statement in the batch only ensures that the constraint is enabled, it doesn't actually check the existing data. To actually check the existing data, I need to ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all – Oxfordshire

glad it's sorted! seems that SSMS generates different code so that you end up with the same result in the isnottrusted column. My guess is that this is by design. – Erective

Answer 2

Yes the two scripts are different

WITH CHECK will check existing data against the new constraint.
WITH NOCHECK will not check existing data against the new constraint. This will allow you to have child records without a corresponding parent.

EDIT: As for why SSMS is doing this I have no idea

Iaverne

However, if you look at the two scripts scripts, immediately after adding the constraint, there is ALTER TABLE [dbo].[Profile] CHECK CONSTRAINT [FK_Profile_OrganizationID] GO Doesn't that check the constraints at that point? – Oxfordshire

ALTER TABLE [dbo].[Profile] CHECK CONSTRAINT [FK_Profile_OrganizationID] GO is only enabling the constraint. To enable it with check, it will have to be ALTER TABLE [dbo].[Profile] WITH CHECK CHECK CONSTRAINT [FK_Profile_OrganizationID] GO. Note the extra WITH CHECK. Don't know why SSMS isn't doing that though. – Pacific

Answer 3

Both are SQL Server 2005 servers? As the result is the same, the code generation tool maybe use different routines based in different versions of the product

Nessi

Actually, at this particular point in time, both databases are on the same server - so there's definitely not any Sql Server version differences – Oxfordshire