PostgreSQL 12 外键和分区表完全兼容

PostgreSQL 12近期发布，新版本中外键和分区表已经完全兼容，可以和普通表一样使用外键约束。

为什么指出这一点：首先分区表首次在PostgreSQL 10 中引入，但是不支持在分区表上创建外键FK；其次，早期表继承功能也不真正支持外键。这就意味着，在PostgreSQL中首次可以在维护引用约束完整性的同时维护大量数据。

如下是一个简单的例子：

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);

这里有两个外键，一个指向未分区的warehouses表，一个指向分区的items表，直接在建表语句中指定REFERENCES即可。

外键约束：

1、向stock表中插入一条数据，而父表items或者warehouses表中不存在，则需要报错；

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".

--正常情况
INSERT INTO items VALUES (1, 'item 1');
INSERT INTO warehouses VALUES (1, 'The moon');
INSERT INTO stock VALUES (1, 1, 10);

2、如果删除父表items或者warehouses数据，而子表stock存在，那么报错。

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".

3、同样，update子表后的数据必须是父表中的数据，update父表会去检查子表中是否存在数据

这些示例看起来很简单，因为这是普通表的最基本的需求，对于分区表PG在12版本中已经完全兼容。

另外，说起外键，一个不得不提的问题就是外键列无索引的问题，特别是update时，如果没有索引，数据量大的情况下会严重影响性能，所以通常情况下会给外键列创建索引如：

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