请原谅这里的任何定义的松散,但我试图探索一个简单的概念。
主键唯一标识一行。表中可能还有其他列具有唯一值,因此它们也可以唯一标识一行(候选键),但主键是为任务指定的。
使主键更有用的属性包括:
- 保证唯一;随着表的增长,其他一些唯一的列值可能会重复
- 不太可能改变;虽然外键约束可以包括级联,但最好不要这样
- 不会被回收;由于某种原因被删除的行不应释放旧的 PK 值以重用
由于这些原因,我通常建议主键没有内在价值,因此永远没有理由更改或回收价值。也就是说,它应该是没有意义的。
我见过包含某种代码的主键,例如基于名称的客户端代码。明显的问题是(a)如果客户端名称更改,那么 PK 也应该更改,以及(b)与具有相似名称的客户端发生冲突的风险太大。
一个例外是使用自动递增的数字,它具有序列号的次要含义。但是,它仍然是稳定的。
问题是,在什么情况下(如果有的话)最好使用具有其他实际含义的主键?也就是说,PK应该是任意的,你通常可以通过序列号的建议有什么问题吗?
鉴于这个问题的重点是“可取”而不是“可接受”,并且接受这仍然是一个高度主观的话题,我会说我想不出一个最好的情况,即系统最好有一个真正自然的出于各种原因的关键(其中大部分已经在之前保罗在对该问题的评论中链接到的其他答案中说过):
我强调“真实”,因为在两种情况下我更喜欢没有新的代理键:
桥牌桌
(或者任何你喜欢调用的表,仅用于或主要用于表示多对多关系)
在对桥接表(逻辑模型中不存在但物理模型中需要的表)建模时,PK 应该存在通过该表关联的表的现有主键列。这允许强制执行值的正确唯一性和非空性,而无需单独的唯一索引/约束。在这种关系需要外键的极少数情况下,它将:
我在一个系统上工作过,这些桥表有自己的自动递增代理键 PK,桥表的单列代理键通过 FK 在其他表中引用:
这是一个可怕的、令人困惑的混乱,我们在调试等方面浪费了太多时间。
同级表
这些表是真正的单个实体,因此具有一对一的关系。出于性能原因,它们仅分为两个(或更多,根据需要)表。我已经对具有 100 万(或更多)行的表进行了此操作,这些行要么非常宽,要么中等宽,并且有些列要么不经常使用,要么是超过 50 个字节的字符串。像这样的东西。这将实体的核心属性保留在一个更窄的表中,该表适合每个数据页上的更多行。
在这些情况下,“兄弟”表与初始表处于完全相同的级别,并且应该具有相同的
PK
. 给它一个自动递增的代理键是没有用的,因为每一行都有一个来自初始表的自然键。需要明确的是,我说的是物理模型,而不是概念模型。我假设这个问题的重点是物理模型,因为它是在概念上不存在的问题的背景下构建的:代理键、处理主键值使用的问题等。考虑到这一点,我不是这意味着不应存储自然密钥并将其用于识别。相反,自然键是很好的“备用键”,应该在它们上面放置唯一的约束/索引。然而,概念模型的理想主义并不总是直接转化为物理模型。数据完整性(即数据模型的稳定性和可靠性)是最重要的,如果不是top,物理模型的优先级。因此,必须进行实际考虑,例如使用代理键,以确保实现这一目标并且不受影响。这意味着,如果您有 SSN 或 SKU 等,则绝对将它们存储在具有唯一约束的列中,并让系统对该值进行查找,因为无论如何都不应该在外部使用自动生成的数字。用户不需要知道记录的自动生成的 ID 号:他们应该传递他们知道的值(例如,电子邮件地址作为 UserID / CustomerID 的查找,航班确认代码与航班日期相结合等)并且系统应该将其转换为从那时起使用的自动生成的值。
是的,在使用自然键作为备用键时,此答案开头提到的问题仍然是潜在问题。但是,不同之处在于问题(通常)仅与一张表隔离。如果有人犯了错误并在“航班定位器”上创建了唯一索引,那么他们可能需要一段时间才能获得违规。但是一旦他们这样做了,就很容易删除该唯一索引并重新创建它以包含航班日期。或者,如果您更改系统上的电子邮件地址(通常用作登录名)并因为几年前(合法)被其他人使用而出现错误,这很可能由支持部门处理,而不会产生任何影响/风险现有的相关记录。在这两种情况下,数据模型的其余部分都没有进行必要的更改。
同样,这是一种务实的方法:
我不知道有多少系统使用 SSN(美国的社会安全号码)作为 PK,但对于任何这样做的系统,其中一些(可能很多)可能已经避免了它们没有像应有的那样独特的问题。但是,这些系统中没有一个能够避免多年来关于需要更安全地处理它们的变化。将 SSN 视为备用密钥的系统需要很少的开发时间来切换到加密这些值,并且系统需要很少的停机时间(或没有停机时间)来在数据层进行更改。鉴于我们都有可能永远无法完成的项目积压,企业往往更喜欢这些烦人但不可避免的变化将花费他们 5 小时而不是 20 到 40 小时(不要忘记更改需要测试,
明确地说,在某些情况下使用自然键是“可以接受的”,尽管我认为我不会说“首选”。
INT
如果使用二进制排序规则(以_BIN2
或什至 结尾_BIN
,但_BIN2
首选),那么它应该比较快。对此类代码具有相对有意义的值可以使支持/调试更容易。但是,您仍然会遇到这样的情况,随着时间的推移,部门名称等可能会发生变化,并且代码可能不再有意义。我的意见是“几乎从来没有”。如果某事有意义,那么如果现实与数据有差异,那么就有可能发生变化,然后你必须处理级联更新,尽管有外键和其他类似的混乱。可能至少 99% 的时间,我选择添加代理标识列作为主键。其余 1% 的时间通常由相关表组成,这些表具有由来自其他表和/或时间戳的代理键的某种组合组成的组合键(在适当的极少数情况下,可能还有自己的标识列)。
如果您有自然键,但仍希望强制唯一性并将语义传达给使用数据的人员,请考虑创建其他唯一的备用键索引。我通常在索引名称前加上 AK_ 以使意图显而易见。该索引将有利于查找查询,维护数据完整性,并清楚地表明对特定键值的查询应该只期望一行。
同时,您所有的销售文件都可以基于无意义的代理键加入,如果销售中的一位女士结婚了,您不必处理整个地方的级联名称更改(多次走这条路) .
表的所有键标识该表中的行。DBMS 将对键(AKA 候选键)强制执行唯一性约束,以保证它们的唯一性,从而保证行可以通过它们的键属性来识别。您的第二段暗示了一种误解,即只有主键才能发挥识别作用。
为了有效利用数据库,其用户通常需要某种方式将数据库中的信息与现实世界的对象或概念相关联。通过确保可以单独识别数据库中的事实并将其与他们描述的真实世界事物相关联,密钥使他们能够做到这一点。为了让数据库准确地对现实世界进行建模,所有或几乎所有表都需要“自然”键——有意义的键,因为它们在数据库之外而不是在数据库内部被发现和使用并且具有相关性。
主键不是特殊类型的键。它是表的键之一。如果表只有一个键,则不会出现主键的选择。如果一张表有多个键,则通常出于约定或偏好的原因指定“主”键,但不同的个人选择主键的原因是多种多样的、主观的,有时甚至是矛盾的。在所有情况下,键的选择都是最重要的;您如何使用密钥很重要;“主”键的选择通常远没有那么重要。