I was sure that I had done this countless times, but I can't find any previous use of me doing this. But it seems to make perfect sense, so I assume that it must be possible? However, the manual says:
A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table.
Emphasis mine. Source: https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
I have a table for bookkeeping, and whenever a transaction "corrects" or "updates" a previous one, I have a column called "corrects id" which is a bigint and, to me, makes perfect sense to have as a FOREIGN KEY as such:
..., FOREIGN KEY ("corrects id") REFERENCES bookkeeping (id)
Note: "corrects id" is a column in the "bookkeeping" table, and id is the primary key (bigserial) of the same table.
Surely this is correct? So why does the manual say that it has to be another table? And why is the name "FOREIGN" keys if you can refer to the same table?
FROM table t1 JOIN table t2- this uses 2 independent copies of the same table.