Menu
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:
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.)
If the end result is the same, why does Management Studio generate them differently for different databases?
782
So to see which foreign keys are enabled or disabled, you can check the sys. foreign_keys table on 2005. It has a is_disabled column that has the value of 1 if the foreign key is disabled. Similarly you can get the information about whether or not a constraint is trusted.
WITH CHECK | WITH NOCHECK Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.
The syntax for disabling a check constraint in SQL Server (Transact-SQL) is: ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name; table_name. The name of the table that you wish to disable the check constraint.
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.
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.
88
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
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
