What are the gotchas with foreign keys when a partitioned table is involved — both a foreign key declared on a partitioned table and a foreign key from another table that references a partitioned table?

## Setting up the two directions A foreign key has a *referencing* side (the child, which stores the value) and a *referenced* side (the parent, which must contain a matching unique row). Partitioning either side raises different issues. ## Direction 1: the partitioned table is the referencing child `orders` is partitioned by month and has `FOREIGN KEY (customer_id) REFERENCES customers(id)`, where `customers` is an ordinary table. This is the easy direction, and most engines that support declarative partitioning support it. The constraint is declared on the parent table and materialised on each partition; the check on insert is a lookup into the ordinary parent's unique index — unaffected by partitioning. The operational catch is **attach-time validation**. When you attach a prepared table as a new partition, the engine must prove that every existing row satisfies the partitioned table's constraints, including foreign keys and the partition bound. If the table being attached does not already carry an equivalent, validated constraint, that proof is a full scan while a lock is held. The standard pattern is therefore: create the standalone table with the same CHECK constraint matching the partition bound, the same foreign keys, and all matching indexes; load and validate it offline; then attach, which becomes a metadata operation. ## Direction 2: the partitioned table is the referenced parent `users` is partitioned by `tenant_id`; `orders` wants `FOREIGN KEY (user_id) REFERENCES users(id)`. Three problems stack up. **(a) Engine support.** PostgreSQL could not reference a partitioned table at all until version 12; before that, teams referenced individual partitions (which breaks the moment rows move or partitions rotate) or dropped the FK. MySQL's InnoDB partitioning does not support foreign keys in either direction — a partitioned InnoDB table can neither declare nor be the target of one. Oracle and SQL Server support it, with SQL Server's usual requirement that the referenced key be backed by a unique index. **(b) The key must contain the partition key.** A foreign key must reference a `PRIMARY KEY` or `UNIQUE` constraint. On a locally-indexed partitioned table, that constraint must include every partition-key column. So `users` has `PRIMARY KEY (tenant_id, id)`, and the only legal reference is `FOREIGN KEY (tenant_id, user_id) REFERENCES users (tenant_id, id)`. Every referencing table must now carry `tenant_id` — which is often fine in a multi-tenant schema and awful when the partition key is a date, because `orders` would have to store the user's *creation month* forever, denormalised and meaningless. **(c) Check cost and pruning.** Each insert or update on the child does a lookup into the parent's key. If the FK carries the partition key, the lookup prunes to one partition and is as cheap as any index probe. If your engine allowed a reference that omits the partition key (only possible with a global unique index), the check either uses that global index or fans out. ## Referential actions `ON DELETE CASCADE` from a partitioned parent means a delete inside one partition can trigger arbitrary work in child tables; on high-volume partitioned data this turns retention deletes into long transactions. It is one more reason people prefer `DROP PARTITION` for retention — but note that dropping a partition **bypasses** referential integrity semantics: the rows vanish without any cascade or restrict check firing in some engines, and in PostgreSQL a `DETACH`/`DROP` of a partition whose rows are referenced by an incoming FK is refused or requires the constraint to be handled first. Design retention so that child data ages out on the same boundary as parent data, ideally by partitioning both on the same key so partitions can be dropped in matched pairs. ## Practical guidance 1. Prefer partitioning the parent on a column that the children already carry naturally — `tenant_id` yes, `created_at` usually no. 2. If the natural FK cannot carry the partition key, consider not partitioning the parent, or moving the FK enforcement into the application with a documented, tested invariant plus periodic reconciliation. 3. Always pre-build indexes, CHECK constraints matching the partition bound, and foreign keys on a staging table before attaching it, so attach is metadata-only. 4. When both parent and child are partitioned, align their partition keys so retention drops match up and referential checks prune on both sides. 5. Write down which invariants the database still enforces after partitioning. It is common to lose one silently and discover it years later as orphan rows.