All 758 foreign keys said they were enforced. None of them were.

We run the scanner against real databases rather than fixtures, because a fixture only contains the problems somebody thought to put in it. One of those is a MusicBrainz copy: 344.3M rows across 374 tables.

The entity map came back with 758 declared foreign keys, and every single one of them was marked NOT VALID. Not a sample. All of them.

PostgreSQL lets you add a constraint without checking the rows already in the table. It is a genuinely useful feature: validating a foreign key on a large table takes a lock you may not be able to afford during business hours, so you add the constraint as NOT VALID and validate it later, when there is a window.

From that moment the constraint is enforced for new and changed rows, but the existing rows were never checked. And "later" is a maintenance task, which means it is a task that frequently never happens.

The result is a database where the catalogue lists a relationship, every tool that reads the catalogue reports a relationship, and the rows underneath may not honour it at all.

SELECT conrelid::regclass AS "table",
       conname            AS constraint_name
FROM   pg_constraint
WHERE  contype = 'f'
  AND  NOT convalidated
ORDER  BY 1, 2;

An empty result means every foreign key in the database has been checked against the rows that exist. Anything else is a list of relationships you may have been treating as enforced.

To fix one, validate it. The lock this takes is far lighter than the one adding the constraint would have taken, but it is not free — run it the way you would run any migration: ALTER TABLE t VALIDATE CONSTRAINT c;

The entity map has three tiers: declared, measured, guessed. The obvious design is to treat a declared key as the strongest evidence available and move on.

This measurement is the reason it does not. A tier answers who says so, not is it true. A declared link now carries the enforcement state with it, so a route walked across an unvalidated constraint says so on screen instead of looking identical to one walked across a checked constraint.

On the other test database the same check found two, and both were faults deliberately planted in the fixture — which is the outcome you want from a check: it reports the known plants and it found something nobody planted. How the map is built.