3

I create a foreign key constraint and know that there are no violations, so I mark it as NOT VALID to skip the check.

From the documentation:

But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.

It doesn't mention the consequences – not even in the notes –, if there are any at all.

Does it matter if a constraint is not marked as valid forever?

1
  • Is this old question answered properly? Commented Jan 29, 2023 at 12:25

3 Answers 3

7
+50

To your actual question:

Does it matter if a constraint is not marked as valid forever?

Not for running the database. The only effect is that Postgres sets the flag pg_constraint.convalidated for the constraint to false. Then it treats the FK constraint like any other, validating any newly written data. Just, if you later run VALIDATE CONSTRAINT - that is:

ALTER TABLE tbl VALIDATE CONSTRAINT tbl_col_fkey;

... referential integrity is only actually validated if it was marked NOT VALID before. And then it's marked valid.

A quick check on the source code revealed no effects, either, as expected.

If you are absolutely positive that referential integrity is intact, you could theoretically exploit that and set the flag in the system catalog manually as superuser:

UPDATE pg_constraint
SET    convalidated = true
WHERE  conrelid = 'public.tbl'::regclass
AND    conname = 'tbl_col_fkey';

But I wouldn't do that if you point a gun to my head.

Firstly, you should never mess with system catalogs to begin with. Stick to well-documented DDL commands to manipulate system catalogs or you may break your database (cluster).

Secondly, you should never make assumptions like that and rather put in the work to be sure.

Thirdly, while my answer may be correct for the current version of Postgres, that may change for the next. We are in undocumented territory here.

And finally, if you really can't get around to properly validate the constraint, just keep operating with the NOT VALID version. Everything works just fine.

Related blog article:

5
  • I've heard that foreign key properties, like Cascade delete, are not applied if the row hasn't been validated. Is that true?
    – Joe
    Commented Sep 10, 2021 at 6:41
  • 2
    @Joe: You've heard wrong. ON DELETE CASCADE works with a NOT VALID constraint, I just tested. See: db<>fiddle here Commented Sep 10, 2021 at 15:22
  • @ErwinBrandstetter Hi, Erwin, I do have a question about constraint. stackoverflow.com/questions/71746611/… . If my question is stupid, please let me know....
    – jian
    Commented Apr 5, 2022 at 11:33
  • @ErwinBrandstetter Hi Erwin, this post seems to say there could be negative impacts on SELECT statements: stackoverflow.com/a/61929325/12094598 Do you know anything about this? Commented Oct 19, 2022 at 11:44
  • 2
    @KillianHuyghe: I am not aware of any optimization that the Postgres query planner / optimizer would apply based on a VALID or NOT VALID FK constraint. The referenced answer seems to merely assume that based on a theoretical possibility. Commented Oct 20, 2022 at 9:26
2

I create a foreign key constraint and know that there are no violations, so I mark it as NOT VALID to skip the check.

Why?

The constraint will be applied to all new data as it is added.

The "not valid" option allows the constraint to exist whilst ignoring any historically-entered, bad data.

If you have no such bad data, then the "not valid" option serves no useful purpose and, indeed, is misleading to anyone else looking at the constraint, who will assume that it's been configured this way to allow for bad data (that isn't there!).

2
  • Why? Performance: "Normally, this form will cause a scan of the table to verify that all existing rows in the table satisfy the new constraint. But if the NOT VALID option is used, this potentially-lengthy scan is skipped."
    – xehpuk
    Commented Nov 19, 2020 at 14:44
  • Also, if you know your data is valid then this can dramatically speed things up. Think shuffling partitions around or moving standard tables to partitions, dropping and re-adding constraints on the partitions and master table can be a terrible hit on the DB, but with some NOT VALID trickery it will be all bish bash done. Commented Feb 27, 2023 at 16:29
2

If you truly know that current data doesn't not require validation or applying this constraint can produce concurrency issues, or you have designed a procedure to ensure consistency, go ahead.

Quoted from docs, bold is mine

Scanning a large table to verify a new foreign key or check constraint can take a long time, and other updates to the table are locked out until the ALTER TABLE ADD CONSTRAINT command is committed. The main purpose of the NOT VALID constraint option is to reduce the impact of adding a constraint on concurrent updates. With NOT VALID, the ADD CONSTRAINT command does not scan the table and can be committed immediately. After that, a VALIDATE CONSTRAINT command can be issued to verify that existing rows satisfy the constraint. The validation step does not need to lock out concurrent updates, since it knows that other transactions will be enforcing the constraint for rows that they insert or update; only pre-existing rows need to be checked. Hence, validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered. (If the constraint is a foreign key then a ROW SHARE lock is also required on the table referenced by the constraint.) In addition to improving concurrency, it can be useful to use NOT VALID and VALIDATE CONSTRAINT in cases where the table is known to contain pre-existing violations

And yes, constraint will be applied to the new rows:

Once the constraint is in place, no new violations can be inserted, and the existing problems can be corrected at leisure until VALIDATE CONSTRAINT finally succeeds.

NOTE

Having said that, you should validate it later, at weekend or holiday, just to be sure there is no inconsistent values (ie: null values) in your current data.

3
  • So it doesn't have any implications when a constraint remains not valid? I was irritated by the quote I posted, as if the database would treat the existing rows differently in the future.
    – xehpuk
    Commented Nov 19, 2020 at 14:56
  • Existing rows won't be validated. If you join both tables using an inner join, you could get no results.
    – McNets
    Commented Nov 19, 2020 at 15:02
  • @McNets depends, not so if you know your data is valid, then it has no added value. Commented Feb 27, 2023 at 16:25

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.