Re: Foreign key validation failure in 18beta1

On Thu, May 29, 2025 at 8:58 PM Amul Sul <sulamul@gmail.com> wrote:
> >
> > I just realized we have the same problem with ALTER FOREIGN KEY ENFORCED.
> > for example:
>
> Yeah, I think adding a "currcon->confrelid == pkrelid" check before
> enqueueing validation in ATExecAlterConstrEnforceability() would
> address the issue, though I still need to test it thoroughly.
>

adding a "currcon->confrelid == pkrelid"  will fix the problem.
I have used the attached scripts to test foreign key functionality:
ALTER TABLE VALIDATE CONSTRAINT and
ALTER TABLE ALTER CONSTRAINT ENFORCED.


v3-0001-foreign_key_validation-WIP also works fine.
but, I have one minor issue with the comment.

+ /*
+ * When the referenced table is partitioned, the referencing table
+ * may have multiple foreign key constraints -- one for each child
+ * table. These individual constraints do not require separate
+ * validation, as validating the constraint on the root partitioned
+ * table is sufficient.

The last sentence doesn't seem to explain why.
The following is what I came up with (my understanding):
""
If the primary key (PK) is partitioned, we *can't* queue validation (add a
NewConstraint on PK partition and validate it in phase3) for its partitions.
Validating foreign key constraints between primary key (PK) partitions and
foreign keys (FK) can fail. This is because some PK partitions may legitimately
lack corresponding FK values, which is acceptable but causes validation errors.
""