May 2018
Intermediate to advanced
576 pages
30h 25m
English
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 TABLE
postgres=# \d pt Table "public.pt" Column | Type ...