On Mon, Jan 25, 2021 at 4:02 PM Ron <ronljohnsonjr@xxxxxxxxx> 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.
Angular momentum makes the world go 'round.
