Re: On partitioning, PKs and FKs

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.)