Re: On partitioning, PKs and FKs

On Thu, 8 Jul 2021 at 21:42, Alban Hertroys <alban(dot)hertroys(at)apollotyres(dot)com>
wrote:

> On 2021-07-08 13:30, Ron wrote:
> > Thus, the bigTable PK must be on id, columnX, (No, I don't like it
> > either.)
>
> That's not entirely true. You can keep the PK on id if you additionally
> create a unique constraint on (id, columnX).
>

Uhm, but that means that i have to partition by id *and* columnX, which is
not what I really want...
=> create table bigtable
(
id numeric primary key,
columnX varchar(5),
constraint test_unique unique (id, columnX)
) partition by hash (columnx);
ERROR: 0A000: unique constraint on partitioned table must include all
partitioning columns
DETAIL: PRIMARY KEY constraint on table "bigtable" lacks column "columnx"
which is part of the partition key.
LOCATION: DefineIndex, indexcmds.c:946
Time: 146.254 ms

Also
) partition by hash (id, columnx);
ERROR: 0A000: unique constraint on partitioned table must include all
partitioning columns
DETAIL: PRIMARY KEY constraint on table "bigtable" lacks column "columnx"
which is part of the partition key.
LOCATION: DefineIndex, indexcmds.c:946
Time: 150.646 ms

and
) partition by hash (id);
CREATE TABLE
Time: 152.780 ms
which is not what I want...