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