Unique constraint on partitioned table

Question

PostgreSQL has a limitation on unique constraints on partitioned tables:

Unique constraints (and hence primary keys) on partitioned tables must include all the partition key columns. This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure itself must guarantee that there are not duplicates in different partitions.

This is sad, because, let's say you have a table of EMPloyees and they are partitioned by field office to which they are assigned. You want the EMP_ID to be the primary key regardless of the assigned field office.

My question is:

• Is this just PosgtgreSQL? In other words, do Oracle or SQL Server or DB2 or whatever other major RDBMS allow this?
• Any idea if PostgreSQL might add this?

In my view, it should not be too hard to do. An index is anyway just another relation (in PostgrSQL) so it should be maintainable regardless of partitioning key.

Answer 1

I know that Oracle has "global indexes" and hence can do this; I cannot say anything about the others.

Adding this feature would be not easy at all, because it requires a global index: a single index that indexes data from all partitions. This has actually been proposed on the developer's mailing list, and you can look at the thread to get an idea of the problems involved.

My view is that such a global index is a mixed blessing at best. Once you have such a thing on your table to support your primary key, you can no longer efficiently attach and detach partitions, and for most use cases of partitioning that is the key feature. Usually it is better to live with reduced integrity guarantees.

Note that there is no such restriction for primary keys on the individual partitions. Such constraints already go a long way. All that is missing is that uniqueness cannot be enforced across partitions. If you generate your "primary key" values with an identity column, you can already be pretty certain that there are no duplicates.