pg12 - foreign key to partitions

Previous Topic Next Topic
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view

pg12 - foreign key to partitions

Mariel Cherkassky
I was playing with the new features that were added to pg12 and I wanted to ask something that looked weird for me.

Lets assume I have the following non partitions structure : 

Product(id int PK,vendor int references Vendor(id),price int)
ProductPic(int picId PK,product int references product(id) )
Vendor(id int PK,name text)
.... more tables that has references to the Product(id).

Now, I successfully converted the current Product table into a hash partition based on the vendor column. Now when I tried to recreate the fk column ProductPic(product) - I'm getting the following error: 
ERROR:  there is no unique constraint matching given keys for referenced table "Product"

during the conversion I had to create a PK on (id,vendor) because of the partitioning. So in order to recreate all the references to the product table I need to add to all those tables the vendor column also ? Isnt there any other way to solve it ?