:Configure any column in a partitioned table as the primary key or foreign key

Prerequisites

The revision version of the PolarDB for PostgreSQL (Compatible with Oracle) cluster is V1.1.35 or later.

Usage notes

• If the primary key that you specify contains all partition keys, local index is used as the index of the primary key. Otherwise, global index is used as the index of the primary key.

• If the foreign key that you specify contains all partition keys, local index is used as the index of the unique constraint. Otherwise, global index is used as the index of the unique constraint.

• You can use only global index in the ALTER TABLE xxx ADD PRIMARY KEY USING INDEX xxx statement. Because local index may not meet the constraint, you cannot use local index in the statement. Instead, a unique key is added to the statement.

• An error may occur when you update data across partitions in a table that references foreign keys. When you update data across partitions, a delete operation and an insert operation are performed. During the delete operation, the system checks whether the rows are referenced.

Examples

Specify a primary key for a partitioned 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
                    Data table "public.sales"
  Field | Type | Collation | Nullable | Default 
---------+-----------------------+----------+----------+------
 dept_no | numeric               |          | not null | 
 part_no | character varying     |          |          | 
 country | character varying(20) |          |          | 
 date    | date                  |          |          | 
 amount  | numeric               |          |          | 
Partition key value: RANGE (date) NULLS LAST
Index:
    "sales_pkey" PRIMARY KEY, btree (dept_no) GLOBAL
Number of partitions: 4 (you can use the \d command to list the partitions)

Reference a unique constraint of a partitioned table by a foreign key

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
              Data table "public.shipments"
     Field | Type | Collation | Nullable | Default 
---------------+---------+----------+----------+------
 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 | 
Partition key value: RANGE (order_date) NULLS LAST
Index:
    "shipments_pkey" PRIMARY KEY, btree (order_date, order_id, delivery_date)
Foreign key constraints:
    "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)
Number of partitions: 5 (you can use the \d command to list the partitions)