PostgreSQL 11 Administration Cookbook

How to do it…

We have inserted inconsistent data on purpose so that any attempt to check existing rows will be revealed by an error message.

If we attempt to create an ordinary foreign key, we get an error, since the number 3 does not appear in the ft table:

postgres=# ALTER TABLE pt ADD CONSTRAINT pc FOREIGN KEY (pk) REFERENCES ft(fk);ERROR: insert or update on table "pt" violates foreign key constraint pc"DETAIL: Key (pk)=(3) is not present in table "ft".

However, the same constraint can be successfully created as NOT VALID:

postgres=# ALTER TABLE pt ADD CONSTRAINT pc FOREIGN KEY (pk) REFERENCES ft(fk) NOT VALID;ALTER TABLEpostgres=# \d pt      Table "public.pt" Column |  Type   | Modifiers--------+---------+----------- pk     | integer | ps | text ...