SQL Compare使用教程:在SQL比较中处理系统命名的约束

翻译|使用教程|编辑:杨鹏连|2020-07-03 17:04:38.327|阅读 21 次

概述:如果您的某些数据库约束具有系统生成的名称,则在比较架构和使用SQL Compare或SQL Change Automation生成构建脚本时,它们可能导致“误报”。Phil Factor解释了这些困难,并提供了启用“比较”选项可以避免这些困难。

# 您正在找协同办公软件吗?点击这里站长给您推荐 #

SQL Compare是一款比较和同步SQL Server数据库结构的工具。现有超过150,000的数据库管理员、开发人员和测试人员在使用它。当测试本地数据库,暂存或激活远程服务器的数据库时,SQL Compare将分配数据库的过程自动化。

点击下载SQL Compare试用版

尽管您可以在表中命名键和约束,但这不是强制性的。当您创建表构建脚本并将代码的简写形式用于约束而不给出名称时,SQL Server合并组成一个名称,每次都不同。如果在导入数据时删除并重新创建约束,它们将获得新名称。

在创建或更改表时DEFAULT,很少有人会为UNIQUE,和CHECK约束命名。如果您充分使用这些具有显式,永久名称的出色设备,则表脚本可能会变得混乱。不一定总是指定偶数PRIMARY KEY或FOREIGN KEY约束,而SQL语法允许这样做。所有这一切都没有什么特别的错误,因为在正常使用中,您无需按名称引用约束。

在某些情况下,系统生成的名称可能会带来额外的困难。当使用SQL Compare或SQL Change Automation比较数据库时,您可能会找到一个示例。除非有特别指示,否则这两个工具都将通过一个选项将这两个不断变化的系统生成的名称分配给没有使用的已定义名称的约束或键。

为了证明这一点,让我们做一个快速的实验,以说明为什么我使用的短语“不断变化”会在经验丰富的数据库开发人员中敲响警钟。

识别系统命名的约束

系统命名的约束没有SQL代码的味道,尽管它们可能使某些人不知道。您可以确定数据库中是否包含系统命名的约束。

SELECT name AS System_named_Constraint,
  Object_Schema_Name(parent_object_id) + '.' + Object_Name(parent_object_id) AS TheTable,
  TheType
  FROM
    (
    SELECT name, parent_object_id, 'check constraint'
      FROM sys.check_constraints AS CC
      WHERE is_system_named <> 0
    UNION ALL
    SELECT name, parent_object_id, 'Default constraint'
      FROM sys.default_constraints AS DC
      WHERE is_system_named <> 0
    UNION ALL
    SELECT name, parent_object_id, 'Key constraint'
      FROM sys.key_constraints AS KC
      WHERE is_system_named <> 0
    ) AS f(name, parent_object_id, TheType);

这段代码给出了我们将要用作测试的数据库:

SQL Compare使用教程:在SQL比较中处理系统命名的约束

您会注意到,系统生成的约束名称遵循一个命名约定,该约定提供了足够的信息以在出现提及名称的错误时提供帮助。前两个字符说明约束的类型,其次说明与约束相关联的表,然后,对于列级约束,列名的最左侧字符。简而言之,总是有足够的信息来查找违规约束。

默认情况下,SQL Compare如何处理系统命名的约束

首先,我们将创建一个使用系统命名约束的小数据库:我们将使用旧的Pubs构建脚本。从中我们将显示两个表publishers和titles,仅用于说明(我已经用注释标记了系统命名的约束):

CREATE TABLE publishers
  (
  pub_id CHAR(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED --named constraint
    CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
        OR pub_id LIKE '99[0-9][0-9]'
          ),--system-named constraint
  pub_name VARCHAR(40) NULL,
  city VARCHAR(20) NULL,
  state CHAR(2) NULL,
  country VARCHAR(30) NULL DEFAULT ('USA')--system-named constraint
  );
GO
CREATE TABLE titles
  (
  title_id tid CONSTRAINT UPKCL_titleidind PRIMARY KEY CLUSTERED,
  title VARCHAR(80) NOT NULL,
  type CHAR(12) NOT NULL DEFAULT ('UNDECIDED'),--system-named constraint
  pub_id CHAR(4) NULL REFERENCES publishers (pub_id),--system-named constraint
  price MONEY NULL,
  advance MONEY NULL,
  royalty INT NULL,
  ytd_sales INT NULL,
  notes VARCHAR(200) NULL,
  pubdate DATETIME NOT NULL DEFAULT (GetDate())--system-named constraint
  );
GO
我们可以运行完整的构建脚本,然后在SSMS中使用“ 任务” >“ 生成脚本”从该数据库生成一个新的构建脚本。我们会发现,对于没有用户定义名称的任何约束,SSMS会继续在其生成的生成脚本中继续使用速记“无名称” SQL语法。如果希望它生成包含系统生成的名称的脚本,则必须打开“包括系统约束名称”选项。

ALTER TABLE [dbo].[publishers] ADD  DEFAULT ('USA') FOR [country]
GO
ALTER TABLE [dbo].[titles] ADD  DEFAULT ('UNDECIDED') FOR [type]
GO
ALTER TABLE [dbo].[titles] ADD  DEFAULT (getdate()) FOR [pubdate]
GO
ALTER TABLE [dbo].[titles]  WITH CHECK ADD FOREIGN KEY([pub_id])
REFERENCES [dbo].[publishers] ([pub_id])
GO
ALTER TABLE [dbo].[publishers]  WITH CHECK ADD CHECK  (([pub_id]='1756' OR [pub_id]='1622' OR [pub_id]='0877' OR [pub_id]='0736' OR [pub_id]='1389' OR [pub_id] like '99[0-9][0-9]'))
GO
现在,我们将使用SQL Compare 通过使用所有默认的Compare选项,通过将它与空的目标数据库进行比较,从原始Pubs数据库的同一副本中生成构建脚本。在Publishers表中,DEFAULTfor Country列的突然有了一个名称:
[country] [varchar] (30) NULL CONSTRAINT [DF__publisher__count__3D5E1FD2] DEFAULT ('USA')
在CHECK上约束Pub_ID的Publishers表成为
ALTER TABLE [dbo].[publishers] ADD CONSTRAINT [CK__publisher__pub_i__3C69FB99] CHECK (([pub_id]='1756' OR [pub_id]='1622' OR [pub_id]='0877' OR [pub_id]='0736' OR [pub_id]='1389' OR [pub_id] like '99[0-9][0-9]'))
因此,我们可以看到Publishers表突然出现了名为defaults和key的表。该Titles表也是如此。

[type] [char] (12) COLLATE Latin1_General_CI_AS NOT NULL CONSTRAINT [DF__titles__type__403A8C7D] DEFAULT ('UNDECIDED'),
,
[pubdate] [datetime] NOT NULL CONSTRAINT [DF__titles__pubdate__4222D4EF] DEFAULT (getdate())
后来,它FOREIGN KEY添加了它的约束,因此我们可以看到名称发生了什么。
ALTER TABLE [dbo].[titles] ADD CONSTRAINT [FK__titles__pub_id__412EB0B6] FOREIGN KEY ([pub_id]) REFERENCES [dbo].[publishers] ([pub_id])

SQL Compare已使用系统生成的名称将所有以系统命名的对象转换为用户命名的对象。通过将名称应用于以系统命名的对象,它实际上已经错误地表示了数据库。它不必这样做,但是它是默认的“ Redgate”选项。

如果您总是通过简单地更改现有对象来开发数据库,那么这不太可能。但是,通过使用默认选项,您在随后比较表以查看是否有任何更改时会遇到麻烦。您还限制了SQL Compare生成的任何脚本的值。我会举例说明。

相同的数据库,相同的脚本,相同的服务器

让我们尝试另一个实验。这次,我们将在同一台服务器上使用相同的原始Pubs构建脚本创建Pubs数据库的新副本。然后,我们使用SQL Compare比较两个数据库。令人惊讶的是,它发现它们是相同的,因为SQL Server在执行表DDL代码时在两个数据库中使用相同的算法来生成系统名称。

相同的数据库,相同的脚本,不同的服务器

现在,我们在另一个服务器上使用相同的原始Pubs构建脚本创建一个相同的数据库。这次一切都错了。我们要做的就是使用其他服务器。这两个数据库是相同的,因为它们是由相同的脚本生成的,但是SQL Compare认为表是不同的。

SQL Compare使用教程:在SQL比较中处理系统命名的约束
我们可以看到为什么SQL Compare认为它们不同。在生成两个数据库的内部模型时,它使用了不同系统生成的名称,就好像它们是用户提供的实际名称一样。如果我们对两者都使用了命名语法,那么它本来可以工作,但是您必须对所有约束都强制使用命名约定,以进行进一步的数据库工作。

同一数据库,不同脚本,同一服务器

好吧,当然,我们可以在同一台服务器上开发,然后呢?我们可以通过另一个测试来证明这一点。如前所述,我们使用原始脚本构建来构建Pubs,使用SSMS从中生成构建脚本,然后使用它来创建名为PubsTest的数据库的新构建。

最后,我们使用SQL Compare比较两个数据库Pubs和PubsTest:

SQL Compare使用教程:在SQL比较中处理系统命名的约束
SQL Compare再次认为这些表是不同的,即使它们不应该是不同的。

允许SQL Compare将名称赋予未命名约束的问题

如果将SQL Compare及其默认选项用于检查表差异,则将得到很多误报。当唯一的区别是约束的自动系统名称时,表将被标记为不同。如果两个开发人员使用脚本工作以在开发它们时偶尔构建表,即使它们实际上是相同的,它们也会在版本控制中被标记为不同,因为这是基于字符串的比较,而不是语义比较。

SQL Compare将生成生成脚本,每次您删除并重新创建系统命名的约束或从脚本重新创建表时,这些生成脚本都会更改。如果您使用带有默认选项的SQL Compare,则版本控制系统可以轻松地阻塞微不足道的更改,并且敢于在对表进行更改时重建表。

还有另一种尴尬。在将数据导入表中之前,您通常需要先禁用所有约束,然后重新启用它们。您可以通过执行以下操作禁用表上的所有约束:

ALTER TABLE MyTable NOCHECK CONSTRAINT ALL
然后,您可以使用以下命令打开它们:

ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT ALL
但是,通常,脚本通过按名称分别标识每个约束来禁用此功能,以禁用检查,然后在数据导入后启用检查。有时,它们会掉落并重新创建它们。在SQL中以外科手术精度单独访问约束的最简单方法是按名称引用。如果执行此操作,则方便的数据导入例程将在数据库的一个版本上创建的具有系统生成名称的约束的名称指定,该约束是在数据库的一个版本上创建的,无法在同一服务器上创建的数据库的精确副本上使用使用不同的脚本。

获取SQL Compare以忽略系统命名的约束和索引名称

有一个简单的解决方案,可以在生成脚本时使Compare的行为与SSMS(和SMO)默认行为保持一致。

默认情况下,SQL Compare的选项“ 忽略系统命名的约束和索引名称 ”为OFF。您应该为您的SQL Compare副本启用它并将其设置为默认值。这样做是为了强制SQL Compare认识到系统命名的约束实际上没有名称,因此它必须使用其他方式比较它们。

SQL Compare使用教程:在SQL比较中处理系统命名的约束
如果使用SQL Compare的命令行版本,则也可以将其指定为选项。如果使用SQL更改自动化,你可以到一个调用中指定New-DatabaseReleaseArtifact,Sync-DatabaseSchema或Invoke-DatabaseBuild使用cmdlet的-SQLCompareOptions参数作为IgnoreSystemNamedConstraintAndIndexNames或者其朋友ISCN。

如果使用sqlserver模块从SSMS或SMO为对象或整个数据库生成构建脚本,则它将在脚本中正确表示系统命名的对象。如果打开SQL Compare的' 忽略系统命名的约束和索引名称 '选项,它将执行相同的操作。您可能会想改为选择“忽略约束和索引名称”,但这不能解决此问题。

访问个人约束

有人说,通过使用系统命名的约束,您使由单个约束引起的错误难以理解。实际上,正如我在本文中所展示的那样,系统生成的名称是经过专门设计的,以使其易于确定所涉及的表,约束类型甚至列。

也有人说这使得表格难以比较。当然,这需要一种不同的方法。比较表时,您需要根据其作用而不是其名称来识别和比较以系统命名的约束。如果告诉您,SQL Compare很乐意这样做。

DEFAULT约束很容易,因为一列只能有一个。如果默认值更改为20到40,则它是一个不同的约束。甲CHECK上的列约束是由什么它确实是这样,有效地,它执行列和代码是标识符定义。表级CHECK约束由其代码定义。一个FOREIGN KEY约束是引用表,引用的表和列的列表中标识。

但是,实际上,很少有理由合理地动态需求DELETE或ALTER约束。通常,您唯一需要访问约束的时间就是打开或关闭约束。然后,您几乎总是想一次全部关闭或打开它们。

结论
SQL中未命名约束的想法使一些开发人员和DBA感到震惊,但是如果使人们更容易使用约束,我全力以赴。实际上,我希望有更多的方法来鼓励使用适当的约束。

我总是在SQL Compare和SCA cmdlet中包含选项“ 忽略系统命名的约束和索引名称 ”,这是我的默认设置,因此在没有此设置的情况下使用它时会感到震惊,因为开始发生奇怪的事情。通过包含它,您更有信心使用SQL Compare生成的对象级和数据库级脚本将与SSMS生成的脚本具有相同的工作方式,但具有错误检查和回滚的好处错误,并保存数据。

相关产品推荐:

SQL Prompt:SQL语法提示工具

SQL Toolbelt:Red Gate产品套包

SQL Monitor:SQL Server监控工具


想要购买VARCHART XGantt正版授权,或了解更多产品信息请点击【咨询在线客服】





标签:

本站文章除注明转载外,均为本站原创或翻译。欢迎任何形式的转载,但请务必注明出处、不得修改原文相关链接,尊重他人劳动成果

文章转载自:Red Gate

登录 慧都网发表评论


暂无评论...

为你推荐

  • 推荐视频
  • 推荐活动
  • 推荐产品
  • 推荐文章
  • 慧都慧问
相关厂商
相关产品
SQL Compare

SQL Compar是一款比较和同步SQL Server数据库结构的工具。

在线
客服
咨询
电话
400-700-1020
在线
QQ
购物车 反馈 返回
顶部
在线客服系统
live chat