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

Archived Discussions


Effective March 31st, we will no longer engage on PostgresRocks.


How to engage with us further?


  • Thought Leadership: EDB Blogs

  • Tips and Tricks: Postgres Tutorials

  • Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket

  • Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.

© 2019 EnterpriseDB Corporation. All rights reserved.   |   Privacy Policy   |  Terms of Use   |   Trademarks