13

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?

3
  • 1
    Sure can. The wording in the documentation is wrong! Commented Jul 7, 2020 at 20:10
  • You might be interested in this. p.s. welcome to the forum!
    – Vérace
    Commented Jul 7, 2020 at 20:16
  • 1
    Of course the FK may refer to the column/expression in the same table. All parent-child structures do this - and no problems. 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? Everything is simple. The table may be the same, but it acts as another one. As another independent copy of this table. This is similar to FROM table t1 JOIN table t2 - this uses 2 independent copies of the same table.
    – Akina
    Commented Jul 8, 2020 at 5:10

1 Answer 1

13

You can indeed do this. If you scroll lower down on the linked postgres documentation, you'll see:

Sometimes it is useful for the “other table” of a foreign key constraint to be the same table; this is called a self-referential foreign key. For example, if you want rows of a table to represent nodes of a tree structure, you could write:


CREATE TABLE tree (
    node_id integer PRIMARY KEY,
    parent_id integer REFERENCES tree,
    name text,
    ...
);
2
  • 2
    I note that if I do a data-only pg_dump I get a warning "there are circular foreign-key constraints on this table" and it goes on to say that I might have to drop the constraints in order to restore the dump. I can see this could be an issue if the order of the rows in the dump has the referencing row before the referenced row.
    – user9645
    Commented Apr 28, 2022 at 17:12
  • 1
    @user9645 The dump basically recreates the database using SQL. As with all foreign keys, an added row can’t reference another row that isn’t there (yet). If the data is dumped in the right order it should be ok, but if foreign keys on older rows were updated to reference newer rows it could get messy.
    – Manngo
    Commented Mar 29, 2023 at 23:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.