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_1 etc.
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.
