快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

SQL Server主键入门

在线QQ客服:1922638

专业的SQL Server、MySQL数据库同步软件

每个数据库开发人员都使用键,但并不总是了解所有分支。它们几乎没有一成不变的规则,但是如果您从一开始就正确地进行数据库设计,那么数据库开发的整个过程将更加简单,并且结果可能会表现更好。我们向菲尔(Phil)征求意见,几乎不知道解释可能要花一些时间。

1906-img546.jpg

  • 什么是钥匙,为什么其中一个是主钥匙?
  • 如何为表定义键?
  • 我应该使用自然键还是替代键?
  • 对于我的主键,是否应该选择聚集索引?
  • 表是否应该始终具有主键?
  • 智能钥匙是个好主意吗?
  • 我可以在临时表中使用键吗?
  • 结论
  • 进一步阅读
  • 词汇表

对于数据库开发人员而言,主键可能是一个盲点。我可以理解和同情,因为关于它们的设计和使用的建议中有些含糊不清,任何试图提供严格规则的尝试都会加剧混乱。在写这篇文章时,我感到渴望为忙碌的开发人员提供清晰的指南。事实证明这是一个雄心勃勃的目标。

主键跨越其他主题,例如索引和外键。为了解释其中的一些问题,我必须讨论其他主题,例如聚簇索引,这些主题值得一读。我将尽可能简洁地给出一个含义,并提供参考以供进一步阅读。(这里有一些代码陪同本文)

什么是钥匙,为什么其中一个是主钥匙?

为了使表具有关系性,必须通过指定包含行的表,列和键值来寻址任何标量值。此键(通常称为候选键)定义一组一个或多个列,这些列可以一起区分表中的各个行。

最初,关系理论只允许一个表的键。一段时间后,这被认为是不必要的限制,因此现在您可以为表使用的键数没有限制(尽管可以说这违反了Boyce-Codd范式BCNF)。对于几个候选键,则问题是“如果未指定特定键,则应使用哪一个来引用各个行”。为了满足此要求,表具有主键的想法应运而生,在候选键中选择单个键作为主键。简而言之,主键仅仅是表的默认键。任何类型的基表,甚至临时表或表变量都可以具有键。

键可以包含几列,但应仅包含足够的列以确保唯一性。当且仅当表中没有两行的唯一列中具有相同的非空值时,才满足键约束。SQL标准包含了一个细微的差别,即在键的参与列中允许空值(但有一些限制),而在主键中则不允许。实际上,我从未在其参与的任何列中找到具有null的键。我的建议是使它们不为null。

尽管关系理论现在不重视主键相对于其他键的重要性,但是SQL Server以这种方式实现它们具有更加特殊的意义。主键对SQL Server很重要,不仅允许进行复制,更改数据捕获,文件表,审核跟踪和数据同步等过程,而且还简化了外键约束的创建。它也用作表的逻辑属性以对数据库进行建模。

如何为表定义键?

1906-img547.jpg

密钥由密钥约束定义和实施。只有两种类型,主键约束和唯一键约束(外键约束实际上是引用约束)。键约束将一个或多个列定义为键。主键约束指示该键为默认键,并且其所有贡献列均为NOT NULL

对于您在CREATE TABLE脚本中定义的任何键约束,如果尝试在组成键的列中尝试存储任何非唯一数据,SQL Server将确保出现错误。在现有表上定义一个键,然后SQL Server将检查重复项,如果找到重复项,则会触发错误回滚表更改。

主键约束要求将键列声明为NOT NULL,并且SQL Server暗示NOT NULL是否在CREATE TABLE语句中声明了该约束而未指定该列的可空性。如果您尝试在允许 NULL值的列上添加主键约束,则即使该列不包含任何 NULL值,该语句也会立即失败。

如前所述,您可以选择具有允许s 的候选键, NULL但是它们可能会执行奇怪的意外操作。除了用于执行键约束之外,SQL中的唯一索引还用于执行非键规则,例如“在值已知的地方必须唯一”,因此应该能够在列为 NULL(SQL Server错误地实现了此功能,因为 NULL该列仅允许一个值)。

SQL Server将在键上创建索引,以有效地强制键的唯一性(,支持键约束的快速验证)。这些索引还支持在联接中使用键以及选择单行或范围。SQL Server为此索引分配与键约束相同的名称。

对于唯一键约束和主键约束,为键选择的列必须具有索引允许的数据类型。这就排除了无法比较的列,例如ntext, text, image, varchar(max), nvarchar(max), varbinary(max), XML,地理,几何和不支持二进制排序的CLR用户定义的数据类型。

SQL Server无法适应现有的合适索引来支持关键约束:即使已经存在定义完全相同的索引,SQL Server也会始终创建一个新的索引。要在单个列上创建键,可以在表创建脚本中使用表约束或列约束。要在多个列上创建键,必须使用表约束。

表或列约束中的主键可以是匿名的,尽管我建议在永久表中显式命名它们。

我应该使用自然键还是替代键?

键只是列(属性)值的组合,提供了一种明显的区分行的方式,而自然或“域”键是在数据库环境之外具有含义的键,例如经度/纬度。许多人主张一个通用规则,即密钥必须是自然的(或者相反,它们不应该是自然的)或必须是不可变的。我什至听说它说所有联接都必须是关键值。与往常一样,这取决于。在此主题上,几乎没有硬性规定,因为有时知识水平和实施要求之间存在冲突的要求。

如果业务实体的主键(例如发票,交货或产品)与当前的业务语言直接相关且属于团队文化的一部分,则通常最好。例如,金融实体往往与日期相关,因为这通常是商务订购商品的方式。因此,与日期相关的列很可能是该表的自然键的一部分,因为它与业务对数据进行排序和分组的自然方式非常吻合。自然键有助于在现实世界和数据库之间架起桥梁

企业通常具有描述个别项目的不可思议的规则,显然是从遥远的部落祖先那里传下来的,但通常会对企业用于分组和报告的数据施加常识性序列。他们讨厌必须更改系统以包含代理密钥,该代理密钥通常是无意义的递增数字。再培训或改变公司文化的成本可能是天文数字。正如Codd在他的论文《 RM / T第4节》中所述,

“如果用户[…]不想,他们不再被迫发明用户控制的钥匙”

这是他们的选择,我们可以任意选择。除此之外,如果出现问题,如果键有意义,则更容易进行分类,并且在损坏完成之前也很容易发现错误。

代理键,通常是通过IDENTITY列执行,是获得一轮的努力来处理自然键是笨拙的或不太符合您的业务规则复杂的正常方式。如果它们在数据库中保持私有状态,则很好;否则,它们是技术债务的一种形式。它们使数据库的编码更加容易,但是簿记员,会计师,零售商或其他需要处理它们的人都不喜欢它们。他们不是人类友好的。有时,如果密钥作为“参考数字”公开,则将密钥“转义”到世界,并具有永久的预期含义,以防止数据库中的任何重构或重新编号。

尽管自然键是一个很好的选择,但总是有一个替代键的位置,例如整数或数字 IDENTITY列所提供的替代键。自然的密钥(例如国际通用的代码或名称)使ETL和数据仓库的维护变得容易得多,但是永远不要紧紧抓住这个想法,因为当您每个人都更容易理解时,您的指关节就白了。使用一个简单的代理。一个简单的递增整数是一种折衷,但是很少会返回来困扰您,这与GUID总是令人恐惧的不一样。

对于我的主键,是否应该选择聚集索引?

使用键约束时,您将定义逻辑规则以确保数据正确。您确定表的访问方式,它们之间的相互关系,并确保强制实施实体完整性(对于引用完整性,我们使用外键约束)。实际上,您将其留给RDBMS来决定如何实现所有实现,并期望这样做。

SQL Server选择为每个键创建索引,这是明智的。但是,SQL Server对数据库和数据一无所知,因此需要帮助选择正确的索引。除非另行指定,否则它将为主键选择聚集索引。但是,如果您先前为唯一约束指定了聚集索引,然后指定了主键约束,则它将是非聚集索引。如果使用ALTER TABLE语句添加或移动主键,则可能已经使用了聚集索引,在这种情况下,SQL Server还将选择唯一索引。如果已经选择了现有的聚集索引,则唯一索引在大多数情况下都可以很好地支持主键。

将聚簇索引分配给主键很少是错误的。如果您选择的主键是没有自然顺序的“胖键”,而该自然键不能很好地用作聚簇索引,或者表中对该聚簇索引有更好的用途,例如支持范围扫描或避免对频繁查询的候选键进行排序。

主键和聚集索引有很大的不同。主键是逻辑结构,聚簇索引是具有特殊物理实现的索引。通过为键指定聚簇索引,可以确定键的实现方式。尽管您选择的主键很可能被证明可以最有效地利用聚簇索引实现,但不一定如此。

聚簇索引的选择会对性能产生巨大影响。具有逻辑意义的候选键作为主键并不一定具有性能良好的聚集索引所需的特征。一个好的聚簇索引是精简的,递增的并且易于进行比较。一个好的主键并不总是那样。

当然,聚集索引具有某些特征,通常使它成为主键的不错选择。例如,一个好的聚簇索引需要值很少改变的列,外键引用的键列也是如此。

聚集索引使您可以有效地访问行中的任何值,因为它会自动“覆盖”,因为到达叶后,您就可以到达行本身。如果您选择了搜索中常用的自然主键,并将其实现为聚簇索引,则无需为列值或谓词进行“查找”。

如果对主键(或任何键)使用非聚集索引,则获取除参与主键或聚集索引键的那些列以外的任何列都比较麻烦。如果您在SELECT语句中指定了无法覆盖的列,那么在非聚集主键索引定位某行之后,将需要对数据行进行单独读取以获取该数据。您无法通过指定要添加到非聚集索引的叶级的非关键列来防止这种情况,因为无法用INCLUDE列指定任何键。

但是,如果您在主表上丢弃每个表的一个聚簇索引,而这对业务没有意义,因此您永远不会在性能争夺中失去最大的武器,因此它永远不会用于搜索,汇总或用于数据排序。您的表中可能有许多备用候选键,您会发现其中之一的用法更适合通过聚簇索引实现。在某些情况下,您可能会发现最好对所有候选键使用非聚集索引 es,并创建非唯一聚集索引。

如果它更适合查询数据的方式,尤其是表如何参与频繁查询,则不要害怕将聚簇索引用于另一个键。您是否会选择主要是单个值,未排序或已排序范围?您是否主要使用除主键以外的一个特定索引?使用引用不属于主键的列的查询时,表的读取次数是否比写入次数多?您通常在特定类别中选择范围吗?您的WHERE子句返回很多行吗?聚集索引可以更好地适应表参与频繁查询的方式,不应基于一些无用的,不断增加的整数来替代代理主键。

对于聚集索引,您可能会选择经济地存储的“窄”索引,因为该值必须保留在每个索引叶级指针中。这可能是一个有趣的折衷方案,因为聚集索引键会自动作为行定位器包含在所有非聚集索引中,因此非聚集索引将覆盖只需要非聚集索引键和聚集索引键的查询。

对于必须支持大量插入的表,由于消除了页面拆分,因此不断增加的主键可以提高性能。但是,如果插入量增加得更多,则索引的“结束”页上的闩锁争用可能会消除这种影响,并且随机分布可能会更好。因此,在某些情况下,与非聚集索引结合使用时,通过非聚集索引实现的主键将表现更好。

表是否应该始终具有主键?

说每个表都需要一个主键是错误的,但是根据定义,任何关系表必须至少具有一个候选键。如果您有一个需要它的SQL Server进程,则可能会发现主键是强制性的:例如,如果参与事务复制,或者您正在使用SQL Azure。但是,您可以故意选择以非关系方式使用SQL Server表,尽管很奇怪,但在SQL Server中具有没有任何键的表是完全合法的(尽管很奇怪)。

更大的问题是表是否必须具有聚集索引,即经常选择正确或错误选择以实现主键的索引。没有聚簇索引的 “表” 实际上是一个堆,当其数据通常以聚合形式或排序顺序返回时,这是一个特别糟糕的主意。然而,自相矛盾的是,它对于实现用于批量插入的日志或“分段”表可能会非常好,因为它很少被读取,并且写入时开销较小。具有非聚集索引的表,但即使没有索引,有时索引也必须通过行标识符而不是更有意义的索引来引用单个行,但有时仍能表现良好。如果该表始终由非聚集索引访问并且没有合适的聚集索引候选对象,则该安排对于不经常更新的表可能是有效的。

智能钥匙是个好主意吗?

智能密钥或连接密钥是一种编码有多个值的密钥。智能密钥的一个常见示例是使用国际公认的代码(例如IBAN,ISBN,电子邮件地址或邮政编码)的示例。尽管使用用户域中已经存在的智能密钥没有问题,但是如果您在数据库中创建新的智能密钥作为主密钥,它将困扰您和您的后继者

尽管在SQL Server中支持由多个列组成的复合键,但偶尔的做法是将多个列的值打包到主键中以形成“智能”或串联键。过去,必须手动维护这种类型的键,但是现在,您可以将计算列用作任何主键或唯一约束的一部分或全部,只要计算列值由确定性表达式和数据定义即可索引列中允许使用结果类型。如果智能密钥更改了值,则还可以使用级联更新来更新外键。

尽管现在很容易实现智能密钥,但还是建议您创建一个不是自然密钥的自己的密钥,因为无论它们有什么优势,它们最终都会陷入麻烦,因为这会使数据库更难要进行重构,它会强加一个难以更改的顺序,并且可能不适用于您的查询,如果智能键包含非数字字符,则需要进行字符串比较,并且在帮助基于范围的聚合方面不如复合键有效。它也违反了基本关系准则,即每一列都应存储原子值(第一范式;信息规则[Codd的第一规则])。

智能钥匙也往往超出了其原始的编码限制:例如,经过多年的权宜之计,美国邮政编码已变得异常难以解码。初始零可以表示七个州,波多黎各或欧洲的军事地址之一。

我可以在临时表中使用键吗?

可以并且应该对临时表和表变量使用键约束,出于相同的原因,应该对永久表和表变量使用键约束,但不能将它们与外键约束结合使用以保持参照完整性。这是因为您可以在本地或全局临时表中创建键约束,但不能创建外键约束。如果在临时表中指定了外键约束,则该语句返回警告,并忽略该约束。

对于临时表,最好使键约束成为匿名。如果临时表是在用户定义的事务范围内创建的,并且具有命名约束,则一次只能有一个用户可以执行创建临时表的语句。

临时表和表变量都可以在表定义中定义键,但是只有临时表可以在后面添加键(带有ALTER TABLE)和在后面添加索引(带有CREATE INDEX)。

您可以将键定义为列约束或表约束。这是可以在表变量上创建索引的唯一方法,但是只能在定义表时进行。就像在表中一样,它可以极大地改变表变量的性能。

结论

尽管键约束是确保数据完整性和保证对单个行的访问的逻辑设备,但是没有提供比提供有效键更简单的方法来增加SQL Server数据库良好性能的机会。如果可以从一开始就做到这一点,那么花几个小时来完善您的选择,并为它们分配最佳属性可以避免无数小时之后创建,维护和修剪额外的索引。

如果未及时纠正错误,则错误判断的密钥可能会产生令人惊讶的后果。尽管许多专家试图为密钥定义严格的规则或“最佳实践”,但经验似乎总是证明它们过于笼统。这项工作不能简化为一些规则。与数据库一样,一切都取决于对数据和可能的查询模式的测试。

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1922638