生成的脚本中的 SQL Server Check/NoCheck 差异 [英] SQL Server Check/NoCheck difference in generated scripts

问题描述

我正在尝试同步不同数据库之间的模式.基本上，我在两个数据库上运行任务->使用 SQL Server Management Studio (2005) 生成脚本，并将输出与 diff 工具进行比较.

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.

出于某种原因，一个脚本添加了约束WITH CHECK 和一个WITH NO CHECK，然后重新启用这两个约束.

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

第二个数据库生成为

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

所以我有两个问题:

1. 最终结果是一样的吗?(似乎很多人只关注这两个脚本的第一个语句.我对这两个脚本的最终结果很感兴趣.)

1. Is the end result the same? ( 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.)

如果最终结果相同，为什么 Management Studio 为不同的数据库生成不同的结果?

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

推荐答案

最终结果不一样！

SQL Server 不会信任 FK 的唯一性，因为它没有被检查.这意味着如果您在查询中使用该列，则需要进行额外的处理.
长话短说，您应该让 SQL Server 检查该列，以便将其视为可信.

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.

至于为什么它们与不同的服务器不同，请查看 sys.foreign_keys 中的 isnottrusted 列.这可能会影响 SSMS 生成的内容?

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

有关此的更多咆哮，请查看与 FK & 相关的我的其他答案.无检查/检查选项.

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

这篇关于生成的脚本中的 SQL Server Check/NoCheck 差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！