Re: Unique constraint across all partitions?

On 1/26/21 2:33 AM, Laurenz Albe wrote:
> On Mon, 2021-01-25 at 18:09 -0600, Ron wrote:
>>>>> Is it possible to enforce a unique constraint across all partitions for
>>>>> either declarative or inheritance based partitioning? Preferably
>>>>> declarative based.
>>> 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.
> Global indexes are likely more pain than gain.
>
> They will make the best part of partitioning (attaching and detaching
> partitions) painfully slow. Hardly worth it to gain a tiny little bit
> of integrity. Within a partition, you can guarantee uniqueness easily.

Don't make my decisions for me.

When a PK or FK without an embedded partition key are needed, you bite the
bullet and take the 9 hour downtime.  (A legacy RDBMS I occasionally still
maintain has had them for *25 years*. What's just as useful are indices
partitioned on keys which have nothing to do with the table's partition key.)

--
Angular momentum makes the world go 'round.