暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片

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

原创 章芋文 2019-10-15
3117

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

评论