skip to content

Why do databases whose partitioned tables only support local indexes require a PRIMARY KEY or UNIQUE constraint to include the partition key columns, and what are your options when the natural key doesn't contain them?

level: middleimportance: must knowfreq 58%

answer

  1. local index sees only its partition
  2. key ⊇ partition key ⇒ one partition per value
  3. hash-partition by the natural key = legal UNIQUE on it
  4. composite PK (tenant_id, id) ripples into every FK
  5. check-then-insert races; registry table is the portable fix

basics

~20 s

Uniqueness is enforced by a per-partition index that only sees its own rows, so duplicates in different partitions would go undetected. Including the partition key guarantees a given key value can only land in one partition, making local enforcement equal global enforcement. Otherwise: repartition, use a global index engine, or enforce elsewhere.

solid answer

~60 s

With local indexes, a unique index exists once per partition and each copy only sees that partition's rows. Two rows with the same `email` in two different partitions would both pass their own uniqueness check, so the constraint would be a lie. Requiring the partition key in the key removes the possibility: the key value determines the partition, so any duplicate must collide inside the same index. When the natural key doesn't include the partition key, the options are: 1. **Repartition on the key you must enforce** — e.g. hash-partition by `email` (or by `id`), which makes `UNIQUE (email)` legal because the partition key is contained in the key. 2. **Make the key composite** — `PRIMARY KEY (tenant_id, id)` — and accept that uniqueness of `id` alone is not enforced by the database. 3. **Use an engine with global unique indexes** (Oracle, SQL Server nonaligned). 4. **Enforce outside the partitioned table** — a small non-partitioned registry table holding the natural key with a unique constraint, written in the same transaction. 5. **Don't partition that table.** Sometimes that is the honest answer. Never rely on application-level "check then insert" — it races.

code

sql · 13 lines
sql
CREATE TABLE users (
  id        bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id bigint NOT NULL,
  email     text   NOT NULL
) PARTITION BY LIST (tenant_id);

-- ERROR: unique constraint on partitioned table must include
--        all partitioning columns
ALTER TABLE users ADD PRIMARY KEY (id);

-- accepted: contains the partition key
ALTER TABLE users ADD PRIMARY KEY (tenant_id, id);
ALTER TABLE users ADD UNIQUE (tenant_id, email);

go deeper

for a junior

Recall the rule and the reason in one sentence: the unique index is per partition, so it can only be trusted if the key forces all duplicates into one partition.

for a middle

Explain containment (key ⊇ partition key), show the legal and illegal DDL, and name the composite-key workaround with its FK ripple.

for a senior

Cover the engine-specific escapes (global unique index) and the portable registry-table pattern, plus why check-then-insert races.

for a principal

Treat it as a schema-design constraint that must be settled before choosing the partition key: decide which invariants the database must enforce, and let that — not just retention — drive the partitioning scheme.

## The mechanism A unique constraint in a relational engine is normally implemented by a unique index: inserting a row probes the index, and if the key already exists the insert fails. The guarantee is exactly as wide as the index. On a partitioned table with local indexes (PostgreSQL declarative partitioning, MySQL partitioning), there is no single index. There are N indexes, one per partition, each containing only that partition's entries. An insert routes to one partition and checks only that partition's index. It has no way to see a matching key in another partition, and making it look would mean probing all N indexes and taking cross-partition locks on every insert — reintroducing exactly the global structure partitioning was meant to avoid. So the engines take the sound route: they refuse to let you declare a constraint they cannot enforce. PostgreSQL's error is explicit — *unique constraint on partitioned table must include all partitioning columns*. ## Why including the partition key fixes it Partition routing is a function of the partition key: `partition = f(partition_key)`. If the unique key contains all partition-key columns, then any two rows with the same unique-key value necessarily have the same partition-key value, therefore route to the same partition, therefore meet in the same local index, therefore collide. Local enforcement becomes globally sound. The requirement is *containment*, not equality: `UNIQUE (tenant_id, email)` is legal on a table partitioned by `tenant_id` or hash-partitioned by `tenant_id`. A subtle and useful case: if you hash-partition **by the natural key itself** — `PARTITION BY HASH (email)` — then `UNIQUE (email)` is legal and gives you real global uniqueness on `email`, because the hash sends every occurrence of a value to exactly one partition. Partitioning by the column you need unique is the cleanest escape hatch when the workload allows it. ## What the requirement actually costs you The common shape is a multi-tenant or time-partitioned table with a surrogate `id`: - Partitioned by `tenant_id`, you must write `PRIMARY KEY (tenant_id, id)`. If `id` comes from a sequence it is still globally unique in practice, but the *database* only guarantees uniqueness per tenant. Every foreign key referencing this table must now carry `(tenant_id, id)`. - Partitioned by `created_at`, you must write `PRIMARY KEY (created_at, id)` — and now the primary key contains a mutable-ish, semantically odd column, foreign keys must carry the date, and a lookup by `id` alone can't prune. That second case is where teams get hurt, and it is a strong argument for partitioning on a column the keys and lookups already carry. ## The full option list when the natural key can't include the partition key 1. **Change the partition key.** Hash-partition on the natural key, or range/list-partition on a column that is already part of the key. Best outcome when retention requirements don't demand a date key. 2. **Widen the key and accept weaker semantics.** Composite PK including the partition key. Document explicitly that global uniqueness of the natural key is *not* enforced, and make sure the generator (sequence, UUID, snowflake) makes collisions practically impossible. 3. **Global unique index** on Oracle or SQL Server. Real enforcement, at the price of partition-DDL maintenance (drops/exchanges must maintain or rebuild the index). 4. **A separate non-partitioned uniqueness registry.** A narrow table `(natural_key PRIMARY KEY, partition_key, id)` written in the same transaction as the insert. The unique index there is global because that table isn't partitioned. It costs an extra write and an extra row per record, and it must be kept in sync on delete — but it is correct, it works on any engine, and it doubles as a lookup that tells you which partition a natural key lives in, restoring prunability. 5. **Serialize through a single writer or advisory lock** keyed on the natural key. Correct but a throughput bottleneck; usually a last resort. 6. **Do not partition this table.** If the table isn't big enough that partitioning pays for itself, the constraint problem is a signal, not an obstacle to route around. ## The anti-pattern `SELECT ... WHERE email = ?` followed by `INSERT` if nothing was found. Under concurrency, two sessions both read nothing and both insert. Read-committed isolation does not prevent this, and a unique index is precisely the mechanism that would have. Anyone who proposes it as the substitute for the constraint has missed the point of the question. ## Related constraints The same containment rule applies to PostgreSQL exclusion constraints on partitioned tables, and it is the reason a partitioned table historically could not be the target of a foreign key: an FK needs a unique key on the parent, and a unique key on a partitioned parent must include the partition key — so the referencing table must carry the partition key too.

  • Your table is partitioned by month for retention, but the business requires `order_number` to be globally unique. What do you propose?
    On PostgreSQL/MySQL the partitioned table cannot enforce it, so I would add a narrow non-partitioned registry table with `order_number` as its primary key, written in the same transaction as the order insert; it enforces the rule globally and also maps an order number back to its month so lookups can prune. On Oracle or SQL Server I would instead create a global/nonaligned unique index on `order_number` and change the retention scripts to maintain or rebuild it on partition drop. If retention is the only reason for partitioning, I'd also check whether a date-ranged partition key plus periodic deletes is genuinely cheaper than the constraint complexity.
  • Does including the partition key in the primary key hurt anything besides aesthetics?
    Yes. Every foreign key referencing the table must carry the full composite key, which widens child tables and their indexes. ORMs and application code that assume a single-column identifier need changes. And if the added column is a date, a lookup by id alone no longer prunes, so it fans out across all partitions.
  • Can you enforce uniqueness by checking with a SELECT before inserting?
    No. Two concurrent transactions can both see no matching row and both insert, and read-committed or repeatable-read isolation will not stop it — only a unique index, a serializable-isolation conflict, or an explicit lock on the key will. If you must do it in the application, take an advisory/named lock on a hash of the key first, which serialises writers on that value at a throughput cost.

saying these in an interview costs you the question

  • Saying "just add UNIQUE on the column" without realising the engine rejects it on a partitioned table.
  • Believing a per-partition unique index still guarantees table-wide uniqueness.
  • Proposing a SELECT-then-INSERT application check as an equivalent guarantee.
  • Not realising that hash-partitioning by the natural key makes UNIQUE on that key legal.
  • Ignoring the ripple: a composite primary key forces every referencing foreign key to widen.

context

resolving links…