PostgreSQL 11 新特性之分區(qū)表外鍵

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

對(duì)于 PostgreSQL 10 中的分區(qū)表，無法創(chuàng)建引用其他表的外鍵約束。

-- 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 解決了這個(gè)限制，可以創(chuàng)建分區(qū)表上的外鍵。

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


目前，還不支持引用分區(qū)表的外鍵，也就是說分區(qū)表不能作為外鍵引用中的父表。

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"

    
通常來說，分區(qū)表都是數(shù)據(jù)量很大的表，不建議創(chuàng)建引用分區(qū)表的外鍵。如果有必要，可以創(chuàng)建引用分區(qū)的外鍵。

官方文檔：Table Partitioning