SQL 主键还是唯一索引？

Primary key or Unique index?

提问by Cicik

At work we have a big database with unique indexes instead of primary keys and all works fine.

在工作中，我们有一个带有唯一索引而不是主键的大型数据库，并且一切正常。

I'm designing new database for a new project and I have a dilemma:

我正在为一个新项目设计新数据库，但我有一个进退两难的问题：

In DB theory, primary key is fundamental element, that's OK, but in REAL projects what are advantages and disadvantages of both?

在 DB 理论中，主键是基本元素，没关系，但在真正的项目中，两者的优缺点是什么？

What do you use in projects?

你在项目中使用什么？

EDIT:...and what about primary keys and replication on MS SQL server?

编辑：...MS SQL 服务器上的主键和复制怎么样？

回答by Mark Byers

What is a unique index?

什么是唯一索引？

A unique index on a column is an index on that column that also enforces the constraint that you cannot have two equal values in that column in two different rows. Example:

列上的唯一索引是该列上的索引，该索引还强制约束您不能在两个不同行的该列中具有两个相等的值。例子：

CREATE TABLE table1 (foo int, bar int);
CREATE UNIQUE INDEX ux_table1_foo ON table1(foo);  -- Create unique index on foo.

INSERT INTO table1 (foo, bar) VALUES (1, 2); -- OK
INSERT INTO table1 (foo, bar) VALUES (2, 2); -- OK
INSERT INTO table1 (foo, bar) VALUES (3, 1); -- OK
INSERT INTO table1 (foo, bar) VALUES (1, 4); -- Fails!

Duplicate entry '1' for key 'ux_table1_foo'

The last insert fails because it violates the unique index on column foowhen it tries to insert the value 1 into this column for a second time.

最后一次插入失败，因为foo当它第二次尝试将值 1 插入此列时，它违反了列上的唯一索引。

In MySQL a unique constraint allows multiple NULLs.

在 MySQL 中，唯一约束允许多个 NULL。

It is possible to make a unique index on mutiple columns.

可以在多列上创建唯一索引。

Primary key versus unique index

主键与唯一索引

Things that are the same:

相同的事情：

• A primary key implies a unique index.

• 主键意味着唯一索引。

Things that are different:

不同之处：

• A primary key also implies NOT NULL, but a unique index can be nullable.
• There can be only one primary key, but there can be multiple unique indexes.
• If there is no clustered index defined then the primary key will be the clustered index.

• 主键也意味着 NOT NULL，但唯一索引可以为 null。
• 主键只能有一个，但唯一索引可以有多个。
• 如果没有定义聚集索引，那么主键将是聚集索引。

回答by Filip Ekberg

You can see it like this:

你可以这样看：

A Primary Key IS Unique

主键是唯一的

A Unique value doesn't have to be the Representaion of the Element

唯一值不必是元素的表示

Meaning?; Well a primary key is used to identify the element, if you have a "Person" you would like to have a Personal Identification Number ( SSN or such ) which is Primary to your Person.

意义？; 好吧，主键用于标识元素，如果您有一个“人”，您希望拥有一个对您的人来说是主要的个人识别号码（SSN 等）。

On the other hand, the person might have an e-mail which is unique, but doensn't identify the person.

另一方面，此人可能拥有独一无二的电子邮件，但无法识别此人的身份。

I always have Primary Keys, even in relationship tables ( the mid-table / connection table ) I might have them. Why? Well I like to follow a standard when coding, if the "Person" has an identifier, the Car has an identifier, well, then the Person -> Car should have an identifier as well!

我总是有主键，即使在关系表（中间表/连接表）中我也可能有它们。为什么？嗯，我喜欢在编码时遵循一个标准，如果“人”有一个标识符，那么 Car 有一个标识符，那么 Person -> Car 也应该有一个标识符！

回答by Jonas Lincoln

Foreign keys work with unique constraints as well as primary keys. From Books Online:

外键与唯一约束以及主键一起使用。从网上书籍：

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table

FOREIGN KEY 约束不必只链接到另一个表中的 PRIMARY KEY 约束；它也可以定义为引用另一个表中的 UNIQUE 约束的列

For transactional replication, you need the primary key. From Books Online:

对于事务复制，您需要主键。从网上书籍：

Tables published for transactional replication must have a primary key. If a table is in a transactional replication publication, you cannot disable any indexes that are associated with primary key columns. These indexes are required by replication. To disable an index, you must first drop the table from the publication.

为事务复制发布的表必须有一个主键。如果表在事务复制发布中，则不能禁用与主键列关联的任何索引。复制需要这些索引。要禁用索引，您必须首先从发布中删除该表。

Both answers are for SQL Server 2005.

这两个答案都适用于 SQL Server 2005。

回答by aekeus

The choice of when to use a surrogate primary key as opposed to a natural key is tricky. Answers such as, always or never, are rarely useful. I find that it depends on the situation.

选择何时使用代理主键而不是自然键是很棘手的。诸如始终或从不这样的答案很少有用。我发现这取决于情况。

As an example, I have the following tables:

例如，我有以下表格：

CREATE TABLE toll_booths (
    id            INTEGER       NOT NULL PRIMARY KEY,
    name          VARCHAR(255)  NOT NULL,
    ...
    UNIQUE(name)
)

CREATE TABLE cars (
    vin           VARCHAR(17)   NOT NULL PRIMARY KEY,
    license_plate VARCHAR(10)   NOT NULL,
    ...
    UNIQUE(license_plate)
)

CREATE TABLE drive_through (
    id            INTEGER       NOT NULL PRIMARY KEY,
    toll_booth_id INTEGER       NOT NULL REFERENCES toll_booths(id),
    vin           VARCHAR(17)   NOT NULL REFERENCES cars(vin),
    at            TIMESTAMP     DEFAULT CURRENT_TIMESTAMP NOT NULL,
    amount        NUMERIC(10,4) NOT NULL,
    ...
    UNIQUE(toll_booth_id, vin)
)

We have two entity tables (toll_boothsand cars) and a transaction table (drive_through). The toll_boothtable uses a surrogate key because it has no natural attribute that is not guaranteed to change (the name can easily be changed). The carstable uses a natural primary key because it has a non-changing unique identifier (vin). The drive_throughtransaction table uses a surrogate key for easy identification, but also has a unique constraint on the attributes that are guaranteed to be unique at the time the record is inserted.

我们有两个实体表（toll_booths和cars）和一个事务表（drive_through）。该toll_booth表使用代理键，因为它没有不能保证更改的自然属性（名称可以轻松更改）。该cars表使用自然主键，因为它具有不变的唯一标识符 ( vin)。该drive_through交易表使用便于标识的代理键，但也有对保证是在记录插入了时间的独特属性的唯一约束。

http://database-programmer.blogspot.comhas some great articles on this particular subject.

http://database-programmer.blogspot.com有一些关于这个特定主题的很棒的文章。

回答by empi

There are no disadvantages of primary keys.

主键没有缺点。

To add just some information to @MrWiggles and @Peter Parker answers, when table doesn't have primary key for example you won't be able to edit data in some applications (they will end up saying sth like cannot edit / delete data without primary key). Postgresql allows multiple NULL values to be in UNIQUE column, PRIMARY KEY doesn't allow NULLs. Also some ORM that generate code may have some problems with tables without primary keys.

仅向@MrWiggles 和@Peter Parker 的答案添加一些信息，例如，当表没有主键时，您将无法在某些应用程序中编辑数据（他们最终会说不能编辑/删除数据，而没有首要的关键）。Postgresql 允许在 UNIQUE 列中存在多个 NULL 值，PRIMARY KEY 不允许 NULL。此外，一些生成代码的 ORM 可能会在没有主键的表上存在一些问题。

UPDATE:

更新：

As far as I know it is not possible to replicate tables without primary keys in MSSQL, at least without problems (details).

据我所知，不可能在 MSSQL 中复制没有主键的表，至少没有问题（详细信息）。

回答by Ray Hidayat

If something is a primary key, depending on your DB engine, the entire table gets sorted by the primary key. This means that lookups are much faster on the primary key because it doesn't have to do any dereferencing as it has to do with any other kind of index. Besides that, it's just theory.

如果某个东西是主键，根据您的数据库引擎，整个表将按主键排序。这意味着在主键上的查找要快得多，因为它不必像处理任何其他类型的索引那样进行任何取消引用。除此之外，这只是理论。

回答by tddmonkey

In addition to what the other answers have said, some databases and systems may requirea primary to be present. One situation comes to mind; when using enterprise replication with Informix a PK must be present for a table to participate in replication.

除了其他答案所说的内容之外，某些数据库和系统可能需要存在主数据库。我想到了一种情况；在 Informix 中使用企业复制时，必须有一个 PK 表才能参与复制。

回答by Peter Parker

As long as you do not allow NULL for a value, they should be handled the same, but the value NULL is handled differently on databases(AFAIK MS-SQL do not allow more than one(1) NULL value, mySQL and Oracle allow this, if a column is UNIQUE) So you mustdefine this column NOT NULL UNIQUE INDEX

只要您不允许一个值使用 NULL，它们就应该被相同地处理，但是 NULL 值在数据库上的处理方式不同（AFAIK MS-SQL 不允许超过一（1）个 NULL 值，mySQL 和 Oracle 允许这样做, 如果一列是 UNIQUE) 所以你必须定义这个列 NOT NULL UNIQUE INDEX

回答by Walter Mitty

There is no such thing as a primary key in relational data theory, so your question has to be answered on the practical level.

关系数据理论中没有主键这样的东西，所以你的问题必须在实践层面上回答。

Unique indexes are not part of the SQL standard. The particular implementation of a DBMS will determine what are the consequences of declaring a unique index.

唯一索引不是 SQL 标准的一部分。DBMS 的特定实现将决定声明唯一索引的后果。

In Oracle, declaring a primary key will result in a unique index being created on your behalf, so the question is almost moot. I can't tell you about other DBMS products.

在 Oracle 中，声明主键将导致代表您创建唯一索引，因此这个问题几乎没有实际意义。我不能告诉你其他 DBMS 产品。

I favor declaring a primary key. This has the effect of forbidding NULLs in the key column(s) as well as forbidding duplicates. I also favor declaring REFERENCES constraints to enforce entity integrity. In many cases, declaring an index on the coulmn(s) of a foreign key will speed up joins. This kind of index should in general not be unique.

我赞成声明一个主键。这具有禁止键列中的 NULL 以及禁止重复的效果。我也赞成声明 REFERENCES 约束以强制执行实体完整性。在许多情况下，在外键的 column(s) 上声明索引将加速连接。这种索引一般不应该是唯一的。

回答by Nico Bester

There are some disadvantages of CLUSTERED INDEXES vs UNIQUE INDEXES.

集群索引与唯一索引有一些缺点。

As already stated, a CLUSTERED INDEX physically orders the data in the table.

如前所述，CLUSTERED INDEX 对表中的数据进行物理排序。

This mean that when you have a lot if inserts or deletes on a table containing a clustered index, everytime (well, almost, depending on your fill factor) you change the data, the physical table needs to be updated to stay sorted.

这意味着当您对包含聚集索引的表进行大量插入或删除操作时，每次（好吧，几乎，取决于您的填充因子）更改数据时，都需要更新物理表以保持排序。

In relative small tables, this is fine, but when getting to tables that have GB's worth of data, and insertrs/deletes affect the sorting, you will run into problems.

在相对较小的表中，这很好，但是当访问具有 GB 数据量的表并且插入/删除会影响排序时，您会遇到问题。