PostgreSQL 复合主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11352056/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL composite primary key
提问by user1467855
In MySQL, when I create a composite primary key, say with columns X, Y, Z, then all three columns become indexes automatically. Does the same happen for Postgres?
在 MySQL 中，当我创建一个复合主键时，比如 column X, Y, Z，那么所有三列都会自动成为索引。Postgres 也会发生同样的情况吗？
回答by Erwin Brandstetter
If you create a composite primary key, on (x, y, z), PostgreSQL implements this with the help of one UNIQUEmulti-column btree index on (x, y, z). In addition, all three columns are NOT NULL(implicitly), which is the main difference between a PRIMARY KEYand a UNIQUE INDEX.
如果你创建了一个复合主键 on (x, y, z)，PostgreSQL 会在一个UNIQUE多列 btree 索引的帮助下实现这一点(x, y, z)。此外，所有三列都是NOT NULL（隐式），这是 aPRIMARY KEY和 a之间的主要区别UNIQUE INDEX。
Besides obvious restrictions on your data, the multi-column indexalso has a somewhat different effect on the performance of queries than three individual indexes on x, yand z.
除了对数据的明显限制外，多列索引对查询性能的影响也与x、y和上的三个单独索引有所不同z。
Related discussion on dba.SE:
dba.SE 相关讨论：
With examples, benchmarks, discussion and outlook on the new feature of index-only scansin Postgres 9.2.
通过示例、基准测试、讨论和展望Postgres 9.2 中仅索引扫描的新功能。
In particular, a primary key on (x, y, z)will speed up queries with conditions on x, (x,y)or (x,y,z)optimally. It will also help with queries on y, z, (y,z)or (x,z)but to a far lesser extent.
特别是，主键 on(x, y, z)将加速条件为 on 的查询x，(x,y)或者以(x,y,z)最佳方式。这也将有助于使用查询上y，z，(y,z)或(x,z)但远程度较轻。
If you need to speed up queries on the latter combinations, you may want to change the order of column in your PK constraint and/or create one or more additional indexes.
如果您需要加快对后一种组合的查询，您可能需要更改 PK 约束中的列顺序和/或创建一个或多个附加索引。
回答by Milen A. Radev
Yes:
是：
PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index covers the columns that make up the primary key or unique constraint (a multicolumn index, if appropriate), and is the mechanism that enforces the constraint.
当为表定义唯一约束或主键时，PostgreSQL 会自动创建唯一索引。索引涵盖构成主键或唯一约束（多列索引，如果合适）的列，并且是强制执行约束的机制。
回答by Dondi Michael Stroma
No, you get one index for the three-column primary key.
不，你会得到一个三列主键的索引。
