What is the best approach to add a foreign key on a partitoned 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.