cancel
Showing results for 
Search instead for 
Did you mean: 

Foreign key issue in partitioning in Postgres 12

Highlighted
Level 3 Adventurer

Foreign key issue in partitioning in Postgres 12

We are testing referencing partitioned tables and have run into an issue.  In a nutshell, how can we reference a partitioned table in which the partitioned column is different then the referenced column?  When trying to create the FK to the partitioned table

 

alter table table1 add foreign key (column) references partitioned (id);

 

we get  "there is no unique constraint matching given keys for referenced table"

 

However, since the partitioned table is partitioned using a different column the unique constraint is

(id,partitionedColumn)

 

Also, pg12 does not allow us to create a seperate unique constriant without including the partitioned column.

 

Does this mean that we can only partition by columns that will also be FK in other tables?  Or, do we need to add the partitioned column to the referring tables?

 

Thanks

Dave