On partitioning, PKs and FKs

From: Wiwwo Staff <wiwwo(at)wiwwo(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: On partitioning, PKs and FKs
Date: 2021-07-08 08:42:54
Message-ID: CAD1W9HW+u8uouPfV9=UED4-TyXGL_3ffbhMU53iPUUGkpg9ETA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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? :)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Atul Kumar 2021-07-08 08:51:28 optimization issue
Previous Message Avi Weinberg 2021-07-08 08:35:41 Logical Replication Configuration for 11 sites Bi-directional logical replication