Re: Partitioning and unique key

On Tue, 3 Sept 2024 at 01:14, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

>
> You can keep the primary key defined on both columns if it is good enough
> for you.
> But it will give you lower guarantees of uniqueness: with that primary
> key, there could
> be two rows with a different timestamp, but the same "txn_id", and these
> two rows could
> be in the same partition...
>
> Also, if you need a foreign key pointing *to* the partitioned table, you
> cannot do without
> a primary key. But I recommend that you do *not* define such foreign
> keys: they will make
> it more difficult to detach a partition.
>
> If you partition two tables in the same way, you can use foreign keys
> between the partitions
> instead of foreign keys between the partitioned tables. Such foreign keys
> won't be a problem.
>
>
Thank You so much.
As you rightly said *"they will make it more difficult to detach a
partition." , *we are really seeing a longer time when detaching parent
table partitions.It runs forever sometimes. So do you mean it's because we
have primary key defined table level or it's because we have FK defined in
table level (for multiple child tables which are also partitioned)?

We were thinking it's because we have FK defined on tablelevel , so we
were planning to make the FK on partition level. But as you just pointed
now , even keeping the PK on table level will also make the detach
partition slow? I understand, for detaching partitions , it may be scanning
while child because of the FK defined on the table level. but i am unable
to understand how the table level PK impacts the detach partition from
parent here.

My understanding is PK can only be created on table level but not on the
partition level. On the partition level we only can have a "unique index"
defined. Correct me if my understanding is wrong.