On Tue, 3 Sept 2024 at 01:14, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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.
