PostgreSQL 11 foreign key on partitioning tables

问题:

In the PostgreSQL 11 Release Notes I found the following improvements to partitioning functionality:

• Add support for PRIMARY KEY, FOREIGN KEY, indexes, and triggers on partitioned tables

I need this feature and tested it.

Create table:

CREATE TABLE public.tbl_test
(
    uuid character varying(32) NOT null,
    registration_date timestamp without time zone NOT NULL    
)
PARTITION BY RANGE (registration_date);

Try to create Primary key:

ALTER TABLE public.tbl_test ADD CONSTRAINT pk_test PRIMARY KEY (uuid);

I get an error SQL Error [0A000]. If use composite PK (uuid, registration_date) then it's work. Because PK contains partitioning column

Conclusion: create PK in partitioning tables work with restrictions (PK need contains partitioning column).

Try to create Foreign key

CREATE TABLE public.tbl_test2
(
    uuid character varying(32) NOT null,
    test_uuid character varying(32) NOT null
);

ALTER TABLE tbl_test2
   ADD CONSTRAINT fk_test FOREIGN KEY (test_uuid)
   REFERENCES tbl_test (uuid);

I get an error SQL Error [42809]. It means FOREIGN KEY on partitioning tables not work.

Maybe i'm doing something wrong. Maybe somebody tried this functionality and know how this work. Maybe somebody know workaround except implement constraint in the application.

回答1:

Postgres 11 only supports foreign keys from a partitioned table to a (non-partitioned) table.

Previously not even that was possible, and that's what the release notes are about.

This limitation is documented in the chapter about partitioning in the manual

While primary keys are supported on partitioned tables, foreign keys referencing partitioned tables are not supported. (Foreign key references from a partitioned table to some other table are supported.

^{(emphasis mine)}

回答2:

PostgreSQL v12.0 will probably support foreign keys that reference partitioned tables. But this is still not guaranteed as v12.0 is still in development.

For v11 and lower versions, you may use triggers as described by depesz in these posts: part1, part2, and part3.

Update: PostgreSQL v12.0 was released on Oct 3, 2019, with this feature included