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?

Improve this question
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
    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.
    – Laurenz Albe
    Jul 1, 2020 at 7:30

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

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

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