Re: Partitioning, Identity and Uniqueness (given pg 16 changes)

On Mon, 19 Feb 2024 at 22:07, Darryl Green <darryl.green@gmail.com> wrote:
>
> On Mon, 19 Feb 2024 at 14:23, David Rowley <dgrowleyml@gmail.com> wrote:
> >
> > On Mon, 19 Feb 2024 at 16:32, Darryl Green <darryl.green@gmail.com> wrote:
> > > 2) It would be nice to be able to specify the id as pk on the table being partitioned (as it was in the
non-partitioneddefinition of the table) once to document and enforce that the partitions simply inherit the id pk. This
wouldseem only to need the "partition by" validation to allow a column not mentioned in partition by clause to be
definedas pk or unique if and only if the pk/unique column is an identity column. Not a big deal but is this
practical/valid?
> >
> > Unfortunately, it's not as easy as you think. It's not a matter of
> > dropping the check that requires all PRIMARY KEY columns are present
> > in the PARTITION BY clause.  For this to work a *single* index (i.e.
> > non-partitioned index) would have to index all partitions.
>
> Now that would be bad. And I do understand that is exactly why in the general case the existing rule is correct. You
snippedmy specific usage/question which was about the uniqueness of a column that was specified as "id int generated
alwaysas identity not null"  and relying on all partitions using the same sequence to populate to guarantee that there
arenever(*) duplicates in other partitions. 

Using such proofs that an index can only ever contain unique values is
just not worth talking about. It's just nowhere near project standard.
We'd just be forever answering questions on this list from people with
unique violation problems. There are just too many reasons to list for
ways this could go wrong.

David