Re: On partitioning, PKs and FKs

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Thursday, July 8, 2021, Wiwwo Staff <wiwwo@xxxxxxxxx> wrote:

On Thu, 8 Jul 2021 at 21:42, Alban Hertroys <alban.hertroys@apollotyres.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...

Those examples show that Alban’s comment that you can keep the PK on id is incorrect.

All you can do is ensure that a given non-partition value is unique on any given partition.  You cannot setup a constraint that definitionally requires the entire partition tree to coordinate and ensure none of the partitions have duplicates among them.

David J.