0

I need to add a foreign key definition to a very large table in Postgres. (Let's assume for the moment that I'm positive that every FK value is actually valid.)

I know that I can specify NOT VALID in order to prevent Postgres from taking aggressive locks on the table while validating the foreign key. Then I can go back "at leisure" and run VALIDATE CONSTRAINT as a cleanup.

What if I never bothered going back to run VALIDATE CONSTRAINT? Is there any practical effect of not doing so?

2
  • I validate FK when creating table with "job_co int references co(co_id)". I am not sure if this has anything to do with your question though.
    – sibert
    Commented Jul 1, 2020 at 6:16
  • Well, the practical effect obviously is that you could have some data in your database that violate the constraint. Usually the point of a constraint is to avoid that. Commented Jul 1, 2020 at 7:30

1 Answer 1

1

It is conceivable that the planner could use valid foreign key constraints to optimize query plans. I don't think that it does that today, though. I know people had been working on such things, but I don't think anything has been accepted into released code, or is likely to be in the near future.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.