On 7/8/21 3:42 AM, Wiwwo Staff wrote:
Hi!
I have a big table bigTable which I partitioned by hash on field columnX,
by creating bigTable_0, bigTable_1etc.
Since I need a PK on bigTable.id, and table is not partitioned by id, and
columnX is not unique, i added PK on bigTable_0.id, bigTable_1.id etc. So
far, so good.
Now I have anotherTable, which has column bigTable_id referencing bigTable.id.
Creating FK anotherTable__bigTable_FK on anotherTable.bigTable_id-you
guess :D- fails with
[42830] ERROR: there is no unique constraint matching given keys for
referenced table "bigTable"
Also creating a FK pointing to one of the hash tables bigTable_0.id,
bigTable_1.id etc. also fails with
ERROR: 23503: insert or update on table "anotherTable" violates
foreign key constraint "anotherTable__bigTable_FK".
Do I have a way out? :)
https://www.postgresql.org/docs/12/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE
Section "5.11.2.3. Limitations" says, "Unique constraints (and hence primary
keys) on partitioned tables must include all the partition key columns."
Thus, the bigTable PK must be on id, columnX, (No, I don't like it either.)
--
Angular momentum makes the world go 'round.
