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
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?