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.