一般来说，数据库中的每个表都应该有一个标识字段用作PK？ [英] in general, should every table in a database have an identity field to use as a PK?

问题描述

这似乎是一个重复，即使我问它，但我搜索，没有找到它。这似乎是一个好问题的SO - 即使我确定我可以找到它在许多博客等。 SO将有更多的辩论比你可以得到一个博客。

我遇到了加入问题：收回的记录太多。我认为这是扩展。我添加了一个表到连接集合和行数展开，方式太多了。通常当发生这种情况时，我添加一个选择的所有参与连接的ID字段。这样，很明显扩展正在发生，我可以改变连接的ON来修复它。除非在这种情况下，我添加的表没有ID字段。对我来说，这是一个问题。但也许我错了。

问题：数据库中的每个表都应该有一个用作PK的IDENTITY字段？在每个表中有一个ID字段有什么缺点吗？如果你合理地确定这个表永远不会用于PK / FK关系怎么办？

相关，但不重复：

显然，这个辩论已经一会儿。已知。

此信息（代理与自然键）也是相关的。

解决方案

有两个概念是接近的但不应该混淆： IDENTITY 和 PRIMARY KEY

条件）应该有 PRIMARY KEY ，这是一个值或一组唯一标识行的值。

请参阅 此处 是 SQL Server 中的列的属性。

<$ c
$ b 这意味着列将自动填充增量值。

由于此属性的性质，此列的值固有地 UNIQUE 。

但是，没有 UNIQUE 约束或<$ c在 IDENTITY 列中自动创建$ c> UNIQUE 索引，并在发出 SET IDENTITY_INSERT ON 可以在 IDENTITY 列中插入重复的值，除非它已被显式性 UNIQUE 约束。

IDENTITY 列不一定是 PRIMARY KEY ，但大多数通常它用于填充代理 PRIMARY KEY s

在任何特定情况下它可能或可能不是有用的。

因此，您的问题的答案：

问题：表格在数据库中有一个用作PK的IDENTITY字段？

是这样：

否。在某些情况下，数据库表不应具有 PRIMARY KEY 的 IDENTITY 字段。

当我们把 IDENTITY 作为 PRIMARY KEY ：

• 如果您的 PRIMARY KEY 多对多链接表）


• 如果您的 PRIMARY KEY 是自然的（如状态代码）


• 如果你的 PRIMARY KEY 在数据库中是唯一的（在这种情况下你使用 GUID code> UUID / NEWID ）

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

当你关心的值不应该有 IDENTITY PRIMARY KEY 并将其显式插入到您的表格中。

更新： / p>

多对多链接表应该有一对 id

这是一个自然的复合键，你必须使用（并使 UNIQUE 没有必要为此生成代理键。

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

在这种情况下，你只是引用链接

此查询：

  CREATE TABLE （a_id，b_id）
 CREATE TABLE business_rule（id，a_id，b_id）
 CREATE TABLE b（id，data）
 CREATE TABLE ab 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 
  

，原因很明显。

This seems like a duplicate even as I ask it, but I searched and didn't find it. It seems like a good question for SO -- even though I'm sure I can find it on many blogs etc. out there. SO will have more of a debate than you can get on a blog.

I'm running into an issue with a join: getting back too many records. I think of this as "expansion". I added a table to the set of joins and the number of rows expanded, way too much. Usually when this happens I add a select of all the ID fields that are involved in the join. That way it's pretty obvious where the expansion is happening and I can change the ON of the join to fix it. Except in this case, the table that I added doesn't have an ID field. To me, this is a problem. But perhaps I'm wrong.

The question: should every table in a database have an IDENTITY field that's used as the PK? Are there any drawbacks to having an ID field in every table? What if you're reasonably sure this table will never be used in a PK/FK relationship?

Related, but not duplicate: When having an identity column is not a good idea?

Apparently this debate has been going on for a while. Shoulda known.

This post (surrogate vs. natural keys) is also relevant.

解决方案

There are two concepts that are close but should not be confused: IDENTITY and PRIMARY KEY

Every table (except for the rare conditions) should have a PRIMARY KEY, that is a value or a set of values that uniquely identify a row.

See here for discussion why.

IDENTITY is a property of a column in SQL Server which means that the column will be filled automatically with incrementing values.

Due to the nature of this property, the values of this column are inherently UNIQUE.

However, no UNIQUE constraint or UNIQUE index is automatically created on IDENTITY column, and after issuing SET IDENTITY_INSERT ON it's possible to insert duplicate values into an IDENTITY column, unless it had been explicity UNIQUE constrained.

The IDENTITY column should not necessarily be a PRIMARY KEY, but most often it's used to fill the surrogate PRIMARY KEYs

It may or may not be useful in any particular case.

Therefore, the answer to your question:

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

is this:

No. There are cases when a database table should NOT have an IDENTITY field as a PRIMARY KEY.

Three cases come into my mind when it's not the best idea to have an IDENTITY as a PRIMARY KEY:

• If your PRIMARY KEY is composite (like in many-to-many link tables)
• If your PRIMARY KEY is natural (like, a state code)
• If your PRIMARY KEY should be unique across databases (in this case you use GUID / UUID / NEWID)

All these cases imply the following condition:

You shouldn't have IDENTITY when you care for the values of your PRIMARY KEY and explicitly insert them into your table.

Update:

Many-to-many link tables should have the pair of id's to the table they link as the composite key.

It's a natural composite key which you already have to use (and make UNIQUE), so there is no point to generate a surrogate key for this.

I don't see why would you want to reference a many-to-many link table from any other table except the tables they link, but let's assume you have such a need.

In this case, you just reference the link table by the composite key.

This query:

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

is much more efficient than this one:

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

, for obvious reasons.

这篇关于一般来说，数据库中的每个表都应该有一个标识字段用作PK？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！