Menu
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?
698
A primary key is a field assigned to a row with unique values in a database's table, but when a table has more than one unique value, we use a composite primary key for these attributes that will set them apart for distinction. PostgreSQL allows its users to have composite primary keys in their tables.
Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.
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.
You can't have more than one. You can have multiple unique constraints. You can have a primary key that contains multiple columns (a composite primary key). But you can't have more than one primary key for a table.
If you create a composite primary key, on (x, y, z), PostgreSQL implements this with the help of one UNIQUE multi-column btree index on (x, y, z). In addition, all three columns are NOT NULL (implicitly), which is the main difference between a PRIMARY KEY and a UNIQUE INDEX.
Besides obvious restrictions on your data, the multi-column index also has a somewhat different effect on the performance of queries than three individual indexes on x, y and z.
Related discussion on dba.SE:
With examples, benchmarks, discussion and outlook on the new feature of index-only scans in 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.
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. See:
101
No, you get one index for the three-column primary key.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
