database 主键与唯一约束？

Primary Key versus Unique Constraint?

提问by vIceBerg

I'm currently designing a brand new database. In school, we always learned to put a primary key in each table.

我目前正在设计一个全新的数据库。在学校，我们总是学会在每个表中放置一个主键。

I read a lot of articles/discussions/newsgroups posts saying that it's better to use unique constraint (aka unique index for some db) instead of PK.

我读了很多文章/讨论/新闻组帖子，说最好使用唯一约束（也称为某些数据库的唯一索引）而不是 PK。

What's your point of view?

你的观点是什么？

采纳答案by Chris Cudmore

Can you provide references to these articles?

你能提供这些文章的参考吗？

I see no reason to change the tried and true methods. After all, Primary Keys are a fundamental design feature of relational databases.

我认为没有理由改变久经考验的方法。毕竟，主键是关系数据库的基本设计特征。

Using UNIQUE to serve the same purpose sounds really hackish to me. What is their rationale?

使用 UNIQUE 来达到同样的目的对我来说听起来真的很骇人听闻。他们的理由是什么？

Edit: My attention just got drawn back to this old answer. Perhaps the discussion that you read regarding PK vs. UNIQUE dealt with people making something a PK for the sole purpose of enforcing uniqueness on it. The answer to this is, If it IS a key, then make it key, otherwise make it UNIQUE.

编辑：我的注意力又回到了这个旧答案上。也许您读到的有关 PK 与 UNIQUE 的讨论涉及人们将某物设为 PK 的唯一目的是在其上实施唯一性。对此的答案是，如果它是一个键，则将其设为键，否则设为唯一。

回答by Mark Brackett

A Primary Key is really just a candidate keythat does not allow for NULL. As such, in SQL terms - it's no different than any other unique key.

主键实际上只是一个不允许 NULL的候选键。因此，在 SQL 术语中 - 它与任何其他唯一键没有什么不同。

However, for our non-theoretical RDBMS's, you should have a Primary Key - I've never heard it argued otherwise. If that Primary Key is a surrogate key, then you should alsohave unique constraints on the natural key(s).

然而，对于我们的非理论 RDBMS，你应该有一个主键——我从来没有听说过它另有说法。如果该 Primary Key 是surrogate key，那么您还应该对自然键有唯一的约束。

The important bit to walk away with is that you should have unique constraints on allthe candidate (whether natural or surrogate) keys. You should then pick the one that is easiest to reference in a Foreign Keyto be your Primary Key*.

要离开的重要一点是，您应该对所有候选键（无论是自然键还是代理键）都有唯一的限制。然后，您应该选择外键中最容易引用的那个作为主键*。

You should also have a clustered index*. this couldbe your Primary Key, or a natural key - but it's not required to be either. You should pick your clustered index based on query usage of the table. When in doubt, the Primary Key is not a bad first choice.

您还应该有一个聚集索引*。这可能是您的主键或自然键 - 但两者都不是必需的。您应该根据表的查询使用情况选择聚簇索引。如有疑问，主键是不错的首选。

• Though it's technically only required to refer to a unique key in a foreign key relationship, it's accepted standard practice to greatlyfavor the primary key. In fact, I wouldn't be surprised if some RDBMS only allow primary key references.

• Edit: It's been pointed out that Oracle's term of "clustered table" and "clustered index" are different than Sql Server. The equivalent of what I'm speaking of in Oracle-ese is an Index Ordered Tableand it is recommended for OLTP tables - which, I think, would be the main focus of SO questions. I assume if you're responsible for a large OLAP data warehouse, you should already have your own opinions on database design and optimization.

• 虽然在技术上只需要在外键关系中引用唯一键，但非常有利于主键是公认的标准做法。事实上，如果某些 RDBMS 只允许主键引用，我不会感到惊讶。

• 编辑：有人指出 Oracle 的“聚簇表”和“聚簇索引”术语与 Sql Server 不同。我在 Oracle-ese 中所说的相当于索引有序表，建议用于 OLTP 表 - 我认为这将是 SO 问题的主要焦点。我假设如果您负责大型 OLAP 数据仓库，您应该已经对数据库设计和优化有了自己的看法。

回答by Dave

A primary key is just a candidate key (unique constraint) singled out for special treatment (automatic creation of indexes, etc).

主键只是被挑出来进行特殊处理（自动创建索引等）的候选键（唯一约束）。

I expect that the folks who argue against them see no reason to treat one key differently than another. That's where I stand.

我希望反对他们的人认为没有理由将一把钥匙与另一把钥匙区别对待。那就是我站着的地方。

[Edit] Apparently I can't comment even on my own answer without 50 points.

[编辑] 显然，如果没有 50 分，我什至无法评论我自己的答案。

@chris: I don't think there's any harm. "Primary Key" is really just syntactic sugar. I use them all the time, but I certainly don't think they're required. A unique keyis required, yes, but not necessarily a Primary Key.

@chris：我认为没有任何伤害。“主键”实际上只是语法糖。我一直在使用它们，但我当然不认为它们是必需的。需要唯一键，是的，但不一定是主键。

回答by Scott Alan Miller

It would be very rare denormalization that would make you want to have a table without a primary key. Primary keys have unique constraints automatically just by their nature as the PK.

非常罕见的非规范化会让您想要一个没有主键的表。主键就其作为 PK 的性质而言自动具有唯一约束。

A unique constraint would be used when you want to guarantee uniqueness in a column in ADDITION to the primary key.

当您想保证主键 ADDITION 中列的唯一性时，将使用唯一约束。

The rule of always have a PK is a good one.

总是有一个PK的规则是一个很好的规则。

http://msdn.microsoft.com/en-us/library/ms191166.aspx

http://msdn.microsoft.com/en-us/library/ms191166.aspx

回答by JacquesB

You should alwayshave a primary key.

你应该总是有一个主键。

However I suspect your question is just worded bit misleading, and you actually mean to ask if the primary key should always be an automatically generated number (also known as surrogate key), or some unique field which is actual meaningful data (also known as natural key), like SSN for people, ISBN for books and so on.

但是，我怀疑您的问题只是措辞有点误导，您实际上是想询问主键是否应该始终是自动生成的数字（也称为代理键），还是某些具有实际意义的数据的唯一字段（也称为自然key），比如人的SSN，书的ISBN等等。

This question is an age old religious war in the DB field.

这个问题是 DB 领域的一场古老的宗教War。

My take is that natural keys are preferable if they indeed are unique and never change. However, you should be careful, even something seemingly stable like a persons SSN may change under certain circumstances.

我的看法是，如果自然键确实是唯一且永不改变的，则更可取。但是，您应该小心，即使像个人 SSN 这样看似稳定的东西在某些情况下也可能会发生变化。

回答by Chris OC

Unless the table is a temporary table to stage the data while you work on it, you always want to put a primary key on the table and here's why:

除非该表是在您处理数据时暂存数据的临时表，否则您总是希望在表上放置一个主键，原因如下：

1 - a unique constraint can allow nulls but a primary key neverallows nulls. If you run a query with a join on columns with null values you eliminate those rows from the resulting data set because null is not equal to null. This is how even big companies can make accounting errors and have to restate their profits. Their queries didn't show certain rows that should have been included in the total because there were null values in some of the columns of their unique index. Shoulda used a primary key.

1 - 唯一约束可以允许空值，但主键从不允许空值。如果在具有空值的列上运行带有联接的查询，则会从结果数据集中消除这些行，因为空值不等于空值。这就是即使大公司也会犯会计错误并不得不重述其利润的方式。他们的查询没有显示本应包含在总数中的某些行，因为在其唯一索引的某些列中存在空值。应该使用主键。

2 - a unique index will automatically be placed on the primary key, so you don't have to create one.

2 - 唯一索引将自动放置在主键上，因此您不必创建一个。

3 - most database engines will automatically put a clustered index on the primary key, making queries faster because the rows are stored contiguously in the data blocks. (This can be altered to place the clustered index on a different index if that would speed up the queries.) If a table doesn't have a clustered index, the rows won't be stored contiguously in the data blocks, making the queries slower because the read/write head has to travel all over the disk to pick up the data.

3 - 大多数数据库引擎会自动在主键上放置聚集索引，因为行连续存储在数据块中，所以查询速度更快。（如果这样可以加快查询速度，可以将聚集索引更改为将聚集索引放在不同的索引上。）如果表没有聚集索引，则行将不会连续存储在数据块中，从而导致查询较慢，因为读/写磁头必须在整个磁盘上移动以获取数据。

4 - many front end development environments require a primary key in order to update the table or make deletions.

4 - 许多前端开发环境需要主键才能更新表或进行删除。

回答by Noah Goodrich

Primary keys should be used in situations where you will be establishing relationships from this table to other tables that will reference this value. However, depending on the nature of the table and the data that you're thinking of applying the unique constraint to, you may be able to use that particular field as a natural primary key rather than having to establish a surrogate key. Of course, surrogate vs natural keys are a whole other discussion. :)

在您将建立从此表到将引用此值的其他表的关系的情况下，应使用主键。但是，根据表的性质和您考虑对其应用唯一约束的数据，您可能能够将该特定字段用作自然主键，而不必建立代理键。当然，代理与自然键是另一回事。:)

Unique keys can be used if there will be no relationship established between this table and other tables. For example, a table that contains a list of valid email addresses that will be compared against before inserting a new user record or some such. Or unique keys can be used when you have values in a table that has a primary key but must also be absolutely unique. For example, if you have a users table that has a user name. You wouldn't want to use the user name as the primary key, but it must also be unique in order for it to be used for log in purposes.

如果此表与其他表之间没有建立关系，则可以使用唯一键。例如，一个包含有效电子邮件地址列表的表，在插入新用户记录或类似记录之前将与这些地址进行比较。或者，当您在具有主键但也必须绝对唯一的表中有值时，可以使用唯一键。例如，如果您有一个包含用户名的 users 表。您不想将用户名用作主键，但它也必须是唯一的，以便用于登录目的。

回答by Michael Dorfman

We need to make a distinction here between logical constructs and physical constructs, and similarly between theory and practice.

我们需要在这里区分逻辑结构和物理结构，类似地区分理论和实践。

To begin with: from a theoretical perspective, if you don't have a primary key, you don't have a table. It's just that simple. So, your question isn't whether your table should have a primary key (of course it should) but how you label it within your RDBMS.

首先：从理论上讲，如果没有主键，就没有表。就是这么简单。因此，您的问题不是您的表是否应该有一个主键（当然应该），而是您如何在 RDBMS 中标记它。

At the physical level, most RDBMSs implement the Primary Key constraint as a Unique Index. If your chosen RDBMS is one of these, there's probably not much practical difference, between designating a column as a Primary Key and simply putting a unique constraint on the column. However: one of these options captures your intent, and the other doesn't. So, the decision is a no-brainer.

在物理层面，大多数 RDBMS 将主键约束实现为唯一索引。如果您选择的 RDBMS 是其中之一，那么在将列指定为主键和简单地在列上放置唯一约束之间可能没有太大的实际区别。但是：这些选项之一可以捕捉您的意图，而另一个则不能。所以，这个决定是不费吹灰之力的。

Furthermore, some RDBMSs make additional features available if Primary Keys are properly labelled, such as diagramming, and semi-automated foreign-key-constraint support.

此外，如果主键被正确标记，一些 RDBMS 会提供附加功能，例如图表和半自动外键约束支持。

Anyone who tells you to use Unique Constraints instead of Primary Keys as a general rule should provide a pretty damned good reason.

任何告诉你使用唯一约束而不是主键作为一般规则的人都应该提供一个非常好的理由。

回答by DonOctavioDelFlores

posts saying that it's better to use unique constraint (aka unique index for some db) instead of PK

帖子说最好使用唯一约束（也称为某些数据库的唯一索引）而不是 PK

i guess that the only point here is the same old discussion "natural vs surrogate keys", because unique indexes and pk′s are the same thing.

我想这里唯一的一点是同样的旧讨论“自然与代理键”，因为唯一索引和 pk 是同一回事。

translating:

翻译：

posts saying that it's better to use natural key instead of surrogate key

帖子说最好使用自然键而不是代理键

回答by Stephen Wrighton

the thing is that a primary key can be one or more columns which uniquely identify a single record of a table, where a Unique Constraint is just a constraint on a field which allows only a single instance of any given data element in a table.

问题是主键可以是一个或多个列，它们唯一标识表的单个记录，其中唯一约束只是对字段的约束，它只允许表中任何给定数据元素的单个实例。

PERSONALLY, I use either GUID or auto-incrementing BIGINTS (Identity Insert for SQL SERVER) for unique keys utilized for cross referencing amongst my tables. Then I'll use other data to allow the user to select specific records.

就个人而言，我使用 GUID 或自动递增 BIGINTS（SQL SERVER 的身份插入）作为用于在我的表之间进行交叉引用的唯一键。然后我将使用其他数据来允许用户选择特定记录。

For example, I'll have a list of employees, and have a GUID attached to every record that I use behind the scenes, but when the user selects an employee, they're selecting them based off of the following fields: LastName + FirstName + EmployeeNumber.

例如，我将有一个员工列表，并为我在幕后使用的每条记录附加一个 GUID，但是当用户选择员工时，他们会根据以下字段选择他们：姓氏 + 名字+ 员工编号。

My primary key in this scenario is LastName + FirstName + EmployeeNumber while unique key is the associated GUID.

在这种情况下，我的主键是姓氏 + 名字 + 员工编号，而唯一键是关联的 GUID。