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