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);
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。