sql - 一般来说，数据库中的每个表都应该有一个身份字段用作主键吗？

Question

我遇到了连接问题:取回太多记录。我在连接集中添加了一个表，并扩展了行数。通常发生这种情况时，我会选择连接中涉及的所有 ID 字段。这样一来，扩展发生的位置就很明显了，我可以更改连接的 ON 来修复它。除了这种情况，我添加的表没有 ID 字段。这是个问题。但也许我错了。

数据库中的每个表都应该有一个 IDENTITY 字段用作主键吗？在每个表中都有一个 ID 字段有什么缺点吗？如果您有理由确定此表永远不会用于 PK/FK 关系怎么办？

When having an identity column is not a good idea?

Surrogate vs. natural/business keys

Wikipedia Surrogate Key article

Answer 1

有两个概念很接近但不应混淆:IDENTITY和 PRIMARY KEY

每个表(除了极少数情况)都应该有一个 PRIMARY KEY ，即唯一标识行的一个值或一组值。

参见 here讨论原因。

IDENTITY是 SQL Server 中列的属性这意味着该列将自动填充递增的值。

由于此属性的性质，此列的值本质上是 UNIQUE .

然而，没有 UNIQUE约束或 UNIQUE索引在 IDENTITY 上自动创建专栏，并在发布后 SET IDENTITY_INSERT ON可以将重复值插入 IDENTITY列，除非它是明确的UNIQUE约束。

IDENTITY列不一定是 PRIMARY KEY , 但最常用于填充代理项 PRIMARY KEY

在任何特定情况下它可能有用也可能没有用。

因此，您问题的答案:

The question: should every table in a database have an IDENTITY field that's used as the PK?

这是:

没有。在某些情况下，数据库表不应该有 IDENTITY。字段为 PRIMARY KEY .

当 IDENTITY 不是最好的主意时，我想到了三种情况。作为PRIMARY KEY :

• 如果您的 PRIMARY KEY是复合的(就像在多对多链接表中一样)
• 如果您的 PRIMARY KEY是自然的(比如州代码)
• 如果您的 PRIMARY KEY跨数据库应该是唯一的(在这种情况下，您使用 GUID/UUID/NEWID )

所有这些情况都意味着以下条件:

你不应该有IDENTITY当您关心 PRIMARY KEY 的值(value)时并将它们显式插入到您的表中。

更新:

多对多链接表应该有一对 id到它们作为复合键链接的表。

这是一个自然的复合键，您已经必须使用它(并生成 UNIQUE )，因此没有必要为此生成代理键。

我不明白你为什么要引用 many-to-many从除它们链接的表之外的任何其他表链接表，但我们假设您有这样的需求。

在这种情况下，您只需通过组合键引用链接表即可。

这个查询:

CREATE TABLE a (id, data)
CREATE TABLE b (id, data)
CREATE TABLE ab (a_id, b_id, PRIMARY KEY (a_id, b_id))
CREATE TABLE business_rule (id, a_id, b_id, FOREIGN KEY (a_id, b_id) REFERENCES ab)

SELECT  *
FROM    business_rule br
JOIN    a
ON      a.id = br.a_id

比这个更有效率:

CREATE TABLE a (id, data)
CREATE TABLE b (id, data)
CREATE TABLE ab (id, a_id, b_id, PRIMARY KEY (id), UNIQUE KEY (a_id, b_id))
CREATE TABLE business_rule (id, ab_id, FOREIGN KEY (ab_id) REFERENCES ab)

SELECT  *
FROM    business_rule br
JOIN    a_to_b ab
ON      br.ab_id = ab.id
JOIN    a
ON      a.id = ab.a_id

，原因很明显。