Re: Question on creating keys on partitioned tables

On Fri, 2023-03-31 at 10:35 -0700, Siddharth Jain wrote:
> Is following correct?
>
> when a PK is created on (X,Y) on the parent table what happens internally is that the
> command is run individually on each of the child tables. nothing more. nothing less.

If you are talking about inheritance, no. Creating a constraint on the parent table
has no effect on a child table.

If you are talking about partitioning: If you create a primary key on the partitioned
table, that will be implemented by a partitioned index. That partitioned index consists
of a unique index for each partition. So I guess that is more or less what you mean.

There are some differences between a primary key on the partitioned table and a unique
index on each partition:
- the primary key is a constraint, so it can be the target of a foreign key
- the primary key will force a unique index on each new partition
- the primary key keeps you from dropping the unique index on the partitions

Yours,
Laurenz Albe