PostgreSQL 11 新特性之分区表外键
作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net
文章目录
对于 PostgreSQL 10 中的分区表,无法创建引用其他表的外键约束。
-- PostgreSQL 10
CREATE TABLE cities (
city_id int not null PRIMARY KEY,
name text not null
);
CREATE TABLE measurement (
city_id int not null REFERENCES cities(city_id),
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
ERROR: foreign key constraints are not supported on partitioned tables
LINE 2: city_id int not null REFERENCES cities(city_id),
^
PostgreSQL 11 解决了这个限制,可以创建分区表上的外键。
-- PostgreSQL 11
CREATE TABLE cities (
city_id int not null PRIMARY KEY,
name text not null
);
CREATE TABLE measurement (
city_id int not null REFERENCES cities(city_id),
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
\d measurement
Table "public.measurement"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
city_id | integer | | not null |
logdate | date | | not null |
peaktemp | integer | | |
unitsales | integer | | |
Partition key: RANGE (logdate)
Foreign-key constraints:
"measurement_city_id_fkey" FOREIGN KEY (city_id) REFERENCES cities(city_id)
Number of partitions: 0
目前,还不支持引用分区表的外键,也就是说分区表不能作为外键引用中的父表。
CREATE TABLE orders (
order_id int not null,
order_date date not null,
PRIMARY KEY (order_id, order_date)
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_detail (
order_id int not null,
order_date date not null,
order_item varchar(50) not null,
FOREIGN KEY (order_id, order_date) REFERENCES orders(order_id, order_date)
) PARTITION BY RANGE (order_date);
ERROR: cannot reference partitioned table "orders"
通常来说,分区表都是数据量很大的表,不建议创建引用分区表的外键。如果有必要,可以创建引用分区的外键。
官方文档:Table Partitioning