I recently had a puzzler, which involved this sort of accidental parser error:
CREATE TABLE foo (a int, b text, PRIMARY KEY (a, b));
CREATE TABLE bar (x int, y text, FOREIGN KEY (q, r) REFERENCES foo (m, n));
ERROR: column "q" referenced in foreign key constraint does not exist
versus
CREATE TABLE bar (x int, y text, FOREIGN KEY (x, y) REFERENCES foo (m, n));
ERROR: column "m" referenced in foreign key constraint does not exist
This example has been simplified for clarity, but the original case involved a
bunch of "id" columns everywhere. What's confusing is that "q" is not
actually referenced by the foreign key constraint, but referenced in the
statement that attempts to define the foreign key constraint, so I was looking
on the wrong side of the constraint there.
Attached is a small patch that separates those error messages into:
ERROR: column "q" specified as a constrained column in foreign key constraint
does not exist
ERROR: column "m" specified as a referenced column in foreign key constraint
does not exist
Details may be debatable. Comments?