ChatGPT解决这个技术问题 Extra ChatGPT

主键还是唯一索引?

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

我正在为一个新项目设计新数据库,但我遇到了一个难题:

在 DB 理论中,主键是基本元素,这没关系,但在 REAL 项目中,两者的优缺点是什么?

你在项目中使用什么?

编辑:...MS SQL 服务器上的主键和复制呢?

这里讨论了一些额外的注意事项(尽管有覆盖索引的额外上下文) - dba.stackexchange.com/questions/21554/…
注意:SQLite 的不同之处在于,由于遗留问题,它们确实允许主键为空,这违反了通用标准。 sqlite.org/lang_createtable.html

M
Mark Byers

什么是唯一索引?

列上的唯一索引是该列上的索引,它还强制执行约束,即在该列中的两个不同行中不能有两个相等的值。例子:

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'

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

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

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

主键与唯一索引

相同的东西:

主键意味着唯一索引。

不一样的东西:

主键也意味着 NOT NULL,但唯一索引可以为空。

可以只有一个主键,但可以有多个唯一索引。

如果没有定义聚集索引,则主键将是聚集索引。


请注意,唯一索引是列上的索引并不完全准确,因为一个唯一索引或主键可以包含多个列。
@Alexandre Jasmin:固定谢谢。关于多列的部分在后面提到。
关于空值,ansi 标准允许在一个数据集中有多个空值并对其进行唯一约束,这也是在 Oracle 和 PostgreSQL 上的实现。我相信 SQL Server 只允许一个空值。
但我仍然没有得到它,比如何时使用主键或何时使用唯一索引?或者可能两者都在相同的情况下。
s
shiser

你可以这样看:

主键是唯一的

唯一值不必是元素的表示

意义?;那么主键用于识别元素,如果您有一个“个人”,您希望有一个个人识别号(SSN 或此类),它是您的个人的主要号码。

另一方面,该人可能有一个唯一的电子邮件,但不能识别该人。

我总是有主键,即使在关系表(中间表/连接表)中我也可能有它们。为什么?好吧,我喜欢在编码时遵循一个标准,如果“Person”有一个标识符,那么 Car 有一个标识符,那么 Person -> Car 也应该有一个标识符!


在您的关系表中:您的意思是引入一个带有人工主键(例如整数)的新列,还是使用组合主键(person_id、car_id)?
主键 (person_id, car_id) 将是最好的。但我通常会创建一个新列,肯定会产生一些开销,但我认为它很好。您永远不知道是否要在以后的场景中与特定关系相关。
代理主键为您的复合/连接表所做的另一件事是简化手动任务的维护。
如果你要生孩子,你只需要一个主键。如果值无处出现,如果值没有被使用,为什么还要添加列和序列?这是为了阻止 Access 要求 PK 的制造工作。如果你需要识别一个孩子的记录,那就做一个PK,否则就浪费了。
如果它与关系无关,它与什么有关?你指着一个字段说,这是主要的。和?然后会发生什么?如果没有自然 pk,我添加一个列、一个序列和一个触发器,都是因为____?有些只需要是主要的。我无缘无故地回避规则。
J
Jonas Lincoln

外键与唯一约束以及主键一起使用。来自在线书籍:

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

对于事务复制,您需要主键。来自在线书籍:

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

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


这把我吓坏了(第一句话)。为什么?我有一个带有任意 ID 的人员表,这是我的 PK,但我决定在电话、电子邮件和 SSN 中添加一个英国......所以现在 4 个不同的表在 4 个不同的列上加入到人员?我想我会放弃你为保持一致性而可能获得的任何灵活性。
a
aekeus

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

例如,我有以下表格:

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)
)

我们有两个实体表(toll_boothscars)和一个事务表(drive_through)。 toll_booth 表使用代理键,因为它没有不能保证更改的自然属性(名称很容易更改)。 cars 表使用自然主键,因为它具有不变的唯一标识符 (vin)。 drive_through 事务表使用代理键以便于识别,但对在插入记录时保证唯一的属性也有唯一约束。

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


e
empi

主键没有缺点。

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

更新:

据我所知,在 MSSQL 中复制没有主键的表是不可能的,至少没有问题 (details)。


插入新行或更新该列时会产生开销。
R
Ray Hidayat

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


该表将按聚集索引排序,而不是按主键排序。
碰巧大多数人将他们的主键设置为聚集索引。
我们知道这通常是一个非常糟糕的主意,除非我们喜欢表中的热点和不平衡的索引树,当然......
这并不总是一个非常糟糕的主意。了解您的数据,了解您的 RDBMS,了解选择的含义。很少有选择总是好的或坏的。如果 ALWAYS 是 ALWAYS 之一,则数据库将强制它或禁止它。他们给你选择,因为“视情况而定”。
t
tddmonkey

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


P
Peter Parker

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


MS-SQL 确实允许在具有唯一索引的列中存在多个 NULL 值,每个 RDBMS 也应该如此。这样想:NULL 不是一个值,所以当你插入第二个 NULL 时,它永远不会匹配现有的。表达式 (NULL == NULL) 不计算为真或假,它计算为 NULL。
thanx gregmac,我不确定,如果 MS 遵循这个。我记得一些 MS 怪癖,但是几年前(2000 年之前),也可能是旧的 access-DB 咳嗽
W
Walter Mitty

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

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

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

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


MS SQL Server 中的主键始终是 UNIQUE 和 NOT NULL —— 例如,它实际上只是一个唯一索引,但附加的限制是它不能为 NULL。
Oracle 可以对非唯一索引实施唯一约束。如果 MSSS 不能,我会感到惊讶。说“它实际上只是一个独特的索引”是一种伤害。
“在许多情况下,在外键的列上声明索引将加快连接速度。”在数据仓库世界中,这几乎总是不正确的,如果可用,哈希连接将是首选。
OP没有提到仓库。我不确定哈希腰如何在 sql server 上工作。仓库更新时可以完成多少工作。
N
Nico Bester

聚集索引与唯一索引相比有一些缺点。

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

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

在相对较小的表中,这很好,但是当获取具有 GB 数据价值的表时,插入器/删除器会影响排序,您会遇到问题。


那有什么好处呢?排序查询更快?当您一次(或很少)编写大部分数据并一直查询它时,这对用例是否更好?
H
HLGEM

我几乎从不创建没有数字主键的表。如果还有一个自然键应该是唯一的,我也会在上面放一个唯一索引。整数上的连接比多列自然键更快,数据只需要在一个地方更改(自然键往往需要更新,这在主键 - 外键关系中是一件坏事)。如果您需要复制,请使用 GUID 而不是整数,但在大多数情况下,我更喜欢用户可读的密钥,特别是如果他们需要查看它以区分 John Smith 和 John Smith。

我没有创建代理键的几次是当我有一个涉及多对多关系的连接表时。在这种情况下,我将两个字段都声明为主键。


“我几乎从不创建没有数字主键的表”:为什么总是数字?主键不需要是数字(顺便说一下,它也不需要是 AUTO_INCREMENT)。
@ Hinou57,因为我发现自然键实际上很少是唯一的,而且它们几乎总是可变的。整数上的进一步连接通常比 varcahrr 自然键或更差的复合键上的连接快得多。大多数时候我不会使用它们。这可能因您存储在数据库中的信息类型而异,但根据我的个人经验,我发现自然键随着时间的推移变得极其不可靠。
感谢 HLGEM 的回复。不靠谱是什么意思?表现? (我希望这不是数据完整性意义上的可靠性问题)。我对你的话有点惊讶,因为我虽然使用整数键或更自然的键(如短 VARCHAR),但可能会产生微小的差异,因为即使使用最简单的数据库引擎,散列也随处使用。
它们在许多情况下是不可靠的,因为即使它们应该是唯一的,它们也不是可靠的唯一。它们不可靠,因为它们会发生变化,并且会影响 uopdate 中的数百万条记录。这是我从数百个存储有关许多不同类型信息的数据的数据库中查看和管理或查询数据或导入数据的经验。
H
Hibou57

我的理解是主键和具有非空约束的唯一索引是相同的(*);我想一个人选择一个或另一个取决于规范明确说明或暗示的内容(你想要表达和明确执行的问题)。如果它需要唯一性且不为空,则将其设为主键。如果它只是发生了唯一索引的所有部分都不是空的而没有任何要求,那么只需将其设为唯一索引。

唯一剩下的区别是,您可能有多个非空唯一索引,而您不能有多个主键。

(*) 除了实际差异:主键可以是某些操作的默认唯一键,例如定义外键。前任。如果定义了一个引用表的外键并且没有提供列名,如果被引用的表有一个主键,那么主键将是被引用的列。否则,必须显式命名被引用的列。

这里的其他人提到了数据库复制,但我不知道。


C
Chirag

唯一索引可以有一个 NULL 值。它创建非聚集索引。主键不能包含 NULL 值。它创建集群索引。


M
Markus

在 MSSQL 中,主键应该单调递增以获得聚集索引的最佳性能。因此,具有标识插入的整数比任何可能不会单调递增的自然键要好。


R
Rodney P. Barbati

如果由我来...

您需要满足数据库和应用程序的要求。

为每个表添加一个自动递增的整数或长 id 列作为主键,可以满足数据库的要求。

然后,您将向表中添加至少一个其他唯一索引以供您的应用程序使用。这将是employee_id、account_id 或customer_id 等上的索引。如果可能,该索引不应是复合索引。

与综合指数相比,我更倾向于单独在几个领域使用指数。只要 where 子句包含这些字段,数据库将使用单个字段索引,但仅当您以完全正确的顺序提供字段时,它才会使用复合索引 - 这意味着它不能使用复合索引中的第二个字段,除非您提供where 子句中的第一个和第二个。

我完全赞成使用计算或函数类型的索引 - 并建议在复合索引上使用它们。通过在 where 子句中使用相同的函数,可以很容易地使用函数索引。

这可以满足您的应用需求。

其他非主索引很可能实际上是该索引键值到主键值的映射,而不是 rowid() 的映射。这允许在不必重新创建这些索引的情况下进行物理排序操作和删除。