Re: Unique constraint across all partitions?

On 1/25/21 6:01 PM, David G. Johnston wrote:
> On Mon, Jan 25, 2021 at 4:02 PM Ron <ronljohnsonjr(at)gmail(dot)com
> <mailto:ronljohnsonjr(at)gmail(dot)com>> wrote:
>
> On 1/25/21 4:44 PM, S Bob wrote:
>
> > Is it possible to enforce a unique constraint across all partitions for
> > either declarative or inheritance based partitioning? Preferably
> > declarative based.
>
> You can create unique indexes on parent tables.  Or am I
> misunderstanding you?
>
>
> You seem to misunderstand how partitions and unique constraints work.
>
> Each partition gets its own copy of the unique index/constraint and
> ensures that duplicates do not appear therein.  If the specification for
> the unique constraint is something like "id bigint" then the value 5 can
> appear on multiple partitions just fine - the partition key(s) effectively
> become part of the unique constraint/index even though the values are not
> actually stored in the index.
>
> So, yes, so long as your unique constraint takes into consideration the
> partitioning scheme it will enforce uniqueness across all of the
> partitions.  But the answer to the full, but possibly implied, question is no.

This is why global indices are needed.

--
Angular momentum makes the world go 'round.