postgresqlforeign-keyspartitioning

Foreign keys on partitioned tables

What is the best approach to add a foreign key on a partitioned table on Postgresql?

1st approach

Add foreign keys on child tables always as NOT VALID.

ALTER TABLE <partition_name> 
    ADD CONSTRAINT <foreign_key_name> 
        FOREIGN KEY (someId) REFERENCES reftable(someId) NOT VALID;

Validate the foreign key on child tables.

ALTER TABLE <partition_name> VALIDATE CONSTRAINT <foreign_key_name>;

Create the foreign key on the parent table.

ALTER TABLE <parent_table_name> 
    ADD CONSTRAINT <foreign_key_name> 
        FOREIGN KEY (someId) REFERENCES reftable(someId);

2nd approach

Add foreign key on parent table as NOT VALID.

ALTER TABLE <parent_table_name> 
    ADD CONSTRAINT <foreign_key_name> 
        FOREIGN KEY (someId) REFERENCES reftable(someId) NOT VALID;

Validate the foreign key on the parent table:

ALTER TABLE <parent_table_name> VALIDATE CONSTRAINT <foreign_key_name>;
Solution

  • The answer is simple: the second method is not possible and will lead to the error

    ERROR:  cannot add NOT VALID foreign key on partitioned table "<parent_table_name>" referencing relation "reftable"
DETAIL:  This feature is not yet supported on partitioned tables.

    So, by exclusion, the first method is the better one. It minimizes locking as well: only the final statement that creates the constraint on the partitioned table requires a lock that conflicts with data modifications, and that statement will be very fast.