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 ?