分区表支持任意列作为主键/外键_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

PolarDB PostgreSQL版（兼容Oracle）支持分区表使用任意列作为主键或者外键引用。

前提条件

PolarDB PostgreSQL版（兼容Oracle）的内核小版本需为20230930（v1.1.35）版本及以上。

注意事项

指定主键时，如果包含所有分区键，则默认使用Local Index作为主键，否则使用Global Index作为主键。
指定约束时，如果包含所有分区键，则默认使用Local Index作为唯一约束，否则使用Global Index作为唯一约束。
alter table xxx add primary key using index xxx语句中只能使用Global Index，不能使用Local Index，因为Local index不一定能满足约束，alter table增加唯一约束也是如此。
外键引用的表如果发生跨分区更新，可能会报错阻止，因为跨分区更新本质上是一次删除和一次插入，在执行删除时会检测此行是否被外键引用。

示例

为分区表指定主键

CREATE TABLE sale
(
  dept_no     number primary key,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number unique 
)
PARTITION BY RANGE(date)
  SUBPARTITION BY LIST(country)
  (
    PARTITION q1_2012
      VALUES LESS THAN('2012-Apr-01')
      (
        SUBPARTITION q1_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q1_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q1_americas VALUES ('US', 'CANADA')
      ),
  PARTITION q2_2012
    VALUES LESS THAN('2012-Jul-01')
      (
        SUBPARTITION q2_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q2_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q2_americas VALUES ('US', 'CANADA')
      ),
  PARTITION q3_2012
    VALUES LESS THAN('2012-Oct-01')
      (
        SUBPARTITION q3_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q3_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q3_americas VALUES ('US', 'CANADA')
      ),
  PARTITION q4_2012
    VALUES LESS THAN('2013-Jan-01')
      (
        SUBPARTITION q4_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q4_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q4_americas VALUES ('US', 'CANADA')
      )
);

\d sales
                    数据表 "public.sales"
  栏位   |         类型          | 校对规则 |  可空的  | 预设 
---------+-----------------------+----------+----------+------
 dept_no | numeric               |          | not null | 
 part_no | character varying     |          |          | 
 country | character varying(20) |          |          | 
 date    | date                  |          |          | 
 amount  | numeric               |          |          | 
分区键值: RANGE (date) NULLS LAST
索引：
    "sales_pkey" PRIMARY KEY, btree (dept_no) GLOBAL
分区的数量：4(可以使用 \d+ 来列出它们)

引用分区表的唯一约束为外键

CREATE TABLE shipments
( order_id      NUMBER NOT NULL
, order_date    DATE NOT NULL
, delivery_date DATE NOT NULL
, customer_id   NUMBER NOT NULL
, sales_amount  NUMBER NOT NULL REFERENCES sales(amount)
, PRIMARY KEY (order_date, order_id, delivery_date)
)
PARTITION BY RANGE (order_date) 
(
  PARTITION p_2006_aug VALUES LESS THAN (TO_DATE('01-SEP-2006','dd-MON-yyyy'))
, PARTITION p_2006_sep VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
, PARTITION p_2006_oct VALUES LESS THAN (TO_DATE('01-NOV-2006','dd-MON-yyyy'))
, PARTITION p_2006_nov VALUES LESS THAN (TO_DATE('01-DEC-2006','dd-MON-yyyy'))
, PARTITION p_2006_dec VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
);

\d shipments
              数据表 "public.shipments"
     栏位      |  类型   | 校对规则 |  可空的  | 预设 
---------------+---------+----------+----------+------
 order_id      | numeric |          | not null | 
 order_date    | date    |          | not null | 
 delivery_date | date    |          | not null | 
 customer_id   | numeric |          | not null | 
 sales_amount  | numeric |          | not null | 
分区键值: RANGE (order_date) NULLS LAST
索引：
    "shipments_pkey" PRIMARY KEY, btree (order_date, order_id, delivery_date)
外部键(FK)限制：
    "shipments_sales_amount_fkey" FOREIGN KEY (sales_amount) REFERENCES sales(amount)
    "shipments_sales_amount_fkey1" FOREIGN KEY (sales_amount) REFERENCES sales_q1_2012(amount)
    "shipments_sales_amount_fkey10" FOREIGN KEY (sales_amount) REFERENCES sales_q3_americas(amount)
    "shipments_sales_amount_fkey11" FOREIGN KEY (sales_amount) REFERENCES sales_q3_europe(amount)
    "shipments_sales_amount_fkey12" FOREIGN KEY (sales_amount) REFERENCES sales_q3_asia(amount)
    "shipments_sales_amount_fkey13" FOREIGN KEY (sales_amount) REFERENCES sales_q4_2012(amount)
    "shipments_sales_amount_fkey14" FOREIGN KEY (sales_amount) REFERENCES sales_q4_americas(amount)
    "shipments_sales_amount_fkey15" FOREIGN KEY (sales_amount) REFERENCES sales_q4_europe(amount)
    "shipments_sales_amount_fkey16" FOREIGN KEY (sales_amount) REFERENCES sales_q4_asia(amount)
    "shipments_sales_amount_fkey2" FOREIGN KEY (sales_amount) REFERENCES sales_q1_americas(amount)
    "shipments_sales_amount_fkey3" FOREIGN KEY (sales_amount) REFERENCES sales_q1_europe(amount)
    "shipments_sales_amount_fkey4" FOREIGN KEY (sales_amount) REFERENCES sales_q1_asia(amount)
    "shipments_sales_amount_fkey5" FOREIGN KEY (sales_amount) REFERENCES sales_q2_2012(amount)
    "shipments_sales_amount_fkey6" FOREIGN KEY (sales_amount) REFERENCES sales_q2_americas(amount)
    "shipments_sales_amount_fkey7" FOREIGN KEY (sales_amount) REFERENCES sales_q2_europe(amount)
    "shipments_sales_amount_fkey8" FOREIGN KEY (sales_amount) REFERENCES sales_q2_asia(amount)
    "shipments_sales_amount_fkey9" FOREIGN KEY (sales_amount) REFERENCES sales_q3_2012(amount)
分区的数量：5(可以使用 \d+ 来列出它们)