在工作中,我们有一个带有唯一索引而不是主键的大型数据库,并且一切正常。
我正在为一个新项目设计新数据库,但我遇到了一个难题:
在 DB 理论中,主键是基本元素,这没关系,但在 REAL 项目中,两者的优缺点是什么?
你在项目中使用什么?
编辑:...MS SQL 服务器上的主键和复制呢?
什么是唯一索引?
列上的唯一索引是该列上的索引,它还强制执行约束,即在该列中的两个不同行中不能有两个相等的值。例子:
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,但唯一索引可以为空。
可以只有一个主键,但可以有多个唯一索引。
如果没有定义聚集索引,则主键将是聚集索引。
你可以这样看:
主键是唯一的
唯一值不必是元素的表示
意义?;那么主键用于识别元素,如果您有一个“个人”,您希望有一个个人识别号(SSN 或此类),它是您的个人的主要号码。
另一方面,该人可能有一个唯一的电子邮件,但不能识别该人。
我总是有主键,即使在关系表(中间表/连接表)中我也可能有它们。为什么?好吧,我喜欢在编码时遵循一个标准,如果“Person”有一个标识符,那么 Car 有一个标识符,那么 Person -> Car 也应该有一个标识符!
外键与唯一约束以及主键一起使用。来自在线书籍:
FOREIGN KEY 约束不必只链接到另一个表中的 PRIMARY KEY 约束;它也可以定义为引用另一个表中唯一约束的列
对于事务复制,您需要主键。来自在线书籍:
为事务复制发布的表必须具有主键。如果表在事务复制发布中,则不能禁用与主键列关联的任何索引。复制需要这些索引。要禁用索引,您必须首先从发布中删除表。
这两个答案都适用于 SQL Server 2005。
选择何时使用代理主键而不是自然键是很棘手的。诸如总是或从不之类的答案很少有用。我发现这取决于情况。
例如,我有以下表格:
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_booths
和 cars
)和一个事务表(drive_through
)。 toll_booth
表使用代理键,因为它没有不能保证更改的自然属性(名称很容易更改)。 cars
表使用自然主键,因为它具有不变的唯一标识符 (vin
)。 drive_through
事务表使用代理键以便于识别,但对在插入记录时保证唯一的属性也有唯一约束。
http://database-programmer.blogspot.com 有一些关于这个特定主题的精彩文章。
主键没有缺点。
要向@MrWiggles 和@Peter Parker 答案添加一些信息,例如,当表没有主键时,您将无法在某些应用程序中编辑数据(他们最终会说无法编辑/删除数据)首要的关键)。 Postgresql 允许多个 NULL 值在 UNIQUE 列中,PRIMARY KEY 不允许 NULL。此外,一些生成代码的 ORM 可能对没有主键的表有一些问题。
更新:
据我所知,在 MSSQL 中复制没有主键的表是不可能的,至少没有问题 (details)。
如果某物是主键,则取决于您的数据库引擎,整个表将按主键排序。这意味着在主键上查找要快得多,因为它不必像与任何其他类型的索引一样进行任何取消引用。除此之外,这只是理论。
除了其他答案所说的之外,某些数据库和系统可能需要存在主数据库。想到一种情况;将企业复制与 Informix 一起使用时,必须存在 PK 表才能参与复制。
只要您不允许 NULL 值,它们应该被处理相同,但值 NULL 在数据库上的处理方式不同(AFAIK MS-SQL 不允许超过一(1)个 NULL 值,mySQL 和 Oracle 允许这个, 如果一列是唯一的) 所以你必须定义这个列 NOT NULL UNIQUE INDEX
关系数据理论中没有主键之类的东西,所以你的问题必须在实践层面上回答。
唯一索引不是 SQL 标准的一部分。 DBMS 的特定实现将决定声明唯一索引的后果。
在 Oracle 中,声明主键将导致代表您创建唯一索引,因此这个问题几乎没有实际意义。我不能告诉你其他 DBMS 产品。
我赞成声明一个主键。这具有禁止键列中的 NULL 以及禁止重复的效果。我也赞成声明 REFERENCES 约束来强制实体完整性。在许多情况下,在外键的列上声明索引将加快连接速度。这种索引通常不应该是唯一的。
聚集索引与唯一索引相比有一些缺点。
如前所述,CLUSTERED INDEX 对表中的数据进行物理排序。
这意味着当您对包含聚集索引的表进行大量插入或删除操作时,每次(嗯,几乎,取决于您的填充因子)您更改数据时,都需要更新物理表以保持排序。
在相对较小的表中,这很好,但是当获取具有 GB 数据价值的表时,插入器/删除器会影响排序,您会遇到问题。
我几乎从不创建没有数字主键的表。如果还有一个自然键应该是唯一的,我也会在上面放一个唯一索引。整数上的连接比多列自然键更快,数据只需要在一个地方更改(自然键往往需要更新,这在主键 - 外键关系中是一件坏事)。如果您需要复制,请使用 GUID 而不是整数,但在大多数情况下,我更喜欢用户可读的密钥,特别是如果他们需要查看它以区分 John Smith 和 John Smith。
我没有创建代理键的几次是当我有一个涉及多对多关系的连接表时。在这种情况下,我将两个字段都声明为主键。
我的理解是主键和具有非空约束的唯一索引是相同的(*);我想一个人选择一个或另一个取决于规范明确说明或暗示的内容(你想要表达和明确执行的问题)。如果它需要唯一性且不为空,则将其设为主键。如果它只是发生了唯一索引的所有部分都不是空的而没有任何要求,那么只需将其设为唯一索引。
唯一剩下的区别是,您可能有多个非空唯一索引,而您不能有多个主键。
(*) 除了实际差异:主键可以是某些操作的默认唯一键,例如定义外键。前任。如果定义了一个引用表的外键并且没有提供列名,如果被引用的表有一个主键,那么主键将是被引用的列。否则,必须显式命名被引用的列。
这里的其他人提到了数据库复制,但我不知道。
唯一索引可以有一个 NULL 值。它创建非聚集索引。主键不能包含 NULL 值。它创建集群索引。
在 MSSQL 中,主键应该单调递增以获得聚集索引的最佳性能。因此,具有标识插入的整数比任何可能不会单调递增的自然键要好。
如果由我来...
您需要满足数据库和应用程序的要求。
为每个表添加一个自动递增的整数或长 id 列作为主键,可以满足数据库的要求。
然后,您将向表中添加至少一个其他唯一索引以供您的应用程序使用。这将是employee_id、account_id 或customer_id 等上的索引。如果可能,该索引不应是复合索引。
与综合指数相比,我更倾向于单独在几个领域使用指数。只要 where 子句包含这些字段,数据库将使用单个字段索引,但仅当您以完全正确的顺序提供字段时,它才会使用复合索引 - 这意味着它不能使用复合索引中的第二个字段,除非您提供where 子句中的第一个和第二个。
我完全赞成使用计算或函数类型的索引 - 并建议在复合索引上使用它们。通过在 where 子句中使用相同的函数,可以很容易地使用函数索引。
这可以满足您的应用需求。
其他非主索引很可能实际上是该索引键值到主键值的映射,而不是 rowid() 的映射。这允许在不必重新创建这些索引的情况下进行物理排序操作和删除。