PostgreSQL 12: Foreign Keys and Partitioned Tables

Now that PostgreSQL 12 is out, we consider foreign keys to be fully compatible with partitioned tables. You can have a partitioned table on either side of a foreign key constraint, and everything will work correctly.

Why do I point this out? Two reasons: first, when partitioned tables were first introduced in PostgreSQL 10, they didn’t support foreign keys at all; you couldn’t create FKs on partitioned tables, nor create FKs that referenced a partitioned table. Second, because the (early days) table inheritance feature didn’t really support foreign keys either. All this means that for the first time it is possible in PostgreSQL to maintain large volumes of data while maintaining referential integrity.  Now that this feature is complete, some new use cases are open to PostgreSQL that previously weren’t.

Here’s a pretty trivial example.

CREATE TABLE items (
    item_id integer PRIMARY KEY,
    description text NOT NULL
) PARTITION BY hash (item_id);
CREATE TABLE items_0 PARTITION OF items FOR VALUES WITH (modulus 3, remainder 0);
CREATE TABLE items_1 PARTITION OF items FOR VALUES WITH (modulus 3, remainder 1);
CREATE TABLE items_2 PARTITION OF items FOR VALUES WITH (modulus 3, remainder 2);

CREATE TABLE warehouses (warehouse_id integer primary key, location text not null);

CREATE TABLE stock (
    item_id integer not null REFERENCES items,
    warehouse_id integer not null REFERENCES warehouses,
    amount int not null
) partition by hash (warehouse_id);
CREATE TABLE stock_0 PARTITION OF stock FOR VALUES WITH (modulus 5, remainder 0);
CREATE TABLE stock_1 PARTITION OF stock FOR VALUES WITH (modulus 5, remainder 1);
CREATE TABLE stock_2 PARTITION OF stock FOR VALUES WITH (modulus 5, remainder 2);
CREATE TABLE stock_3 PARTITION OF stock FOR VALUES WITH (modulus 5, remainder 3);
CREATE TABLE stock_4 PARTITION OF stock FOR VALUES WITH (modulus 5, remainder 4);

You can see there are two foreign keys here. One points to to a regular (not partitioned) table warehouses, the other points to partitioned table items. Did you notice that each foreign key is only declared once?

There are two basic operations that you want the foreign key to provide. First, if you insert a row in stock (the referencing table) that doesn’t have a corresponding row in items or warehouses (the referenced table), an error must be raised. Second, if you delete a row in either referenced table and there are matching rows in stock, that operation must be rejected too.

Both are easily verified:

INSERT INTO stock values (1, 1, 10);
ERROR:  insert or update on table "stock_0" violates foreign key constraint "stock_item_id_fkey"
DETAIL:  Key (item_id)=(1) is not present in table "items".

Good. Then you can insert matching rows in both referenced tables and a referencing row.  After that, a delete in either of the referenced tables will fail, as expected.

INSERT INTO items VALUES (1, 'item 1');
INSERT INTO warehouses VALUES (1, 'The moon');
INSERT INTO stock VALUES (1, 1, 10);

DELETE FROM warehouses;
ERROR:  update or delete on table "warehouses" violates foreign key constraint "stock_warehouse_id_fkey" on table "stock"
DETAIL:  Key (warehouse_id)=(1) is still referenced from table "stock".

DELETE FROM items;
ERROR:  update or delete on table "items_2" violates foreign key constraint "stock_item_id_fkey3" on table "stock"
DETAIL:  Key (item_id)=(1) is still referenced from table "stock".

(Of course, an UPDATE operation is for the former operation the same as an INSERT, and for the latter operation the same as a DELETE — meaning that both the original tuple and the modified tuple must be checked, if the UPDATE modifies the columns involved in the foreign key.)

If these examples seem lame to experienced users, it’s because these things work exactly the same for regular (not partitioned) tables since time immemorial.

In real usage, you would need indexes in the referencing columns in the stock table, if you ever modify the referenced tables. This is because the server needs to locate those referencing rows in order to know to throw an error or such. You can do that with the partitioned referencing table easily enough:

CREATE INDEX ON stock (item_id);
CREATE INDEX ON stock (warehouse_id);

In this post I have shown the basics of foreign keys, and how they can be used on partitioned tables just like they can on regular tables. In a subsequent post I’ll cover a couple of additional features of those. Let me know in a comment if you like this PostgreSQL 12 improvement!