Non-unique foreign key in PostgreSQL?
<p>I have a database design question, to which either of the following two would be help I would appreciate:</p>

<p>1) Explanation why what I'm doing is a bad design decision, and how to design it better</p>

<p>2) Example how to actually implement the desired design in PostgreSQL</p>

<p>In short, what I'm doing is designing a tree-structure where each node should have a revision history like this:</p>

<pre><code>CREATE TABLE Nodes
(
  nid BIGSERIAL PRIMARY KEY,
  node_id BIGINT NOT NULL,
  parent_nodeid BIGINT,
  revision_id INTEGER NOT NULL,

  .. additional columns with info about this node ..
)
</code></pre>

<p>The idea is the following; I might have a structure like:</p>

<pre><code>root node
    child node 1
    child node 2
</code></pre>

<p>When a user edits the information in the "root node"; instead of just replacing the values in the existing log, I want to keep a log of the previous values so I instead create a new "revision" of the row - so the user sometime far in the future can do an "undo" and return to the previous configuration of the node.</p>

<p>What I want to achieve, is that child nodes automatically refer to the new parent node without having to update <code>parent_nodeid</code> of the children as the new revision of the root node should not change the hierarchy of the node tree.</p>

<p>I understand that I cannot add a foreign key from <code>Nodes.parent_nodeid</code> to <code>Nodes.node_id</code> as PostgreSQL requires foreign keys to reference columns with a unique value - but I'm kind of lost on how to add some kind of constraint that at least guarantees that <code>Nodes.parent_nodeid</code> references an existing <code>Nodes.node_id</code> value even though it won't be unique.</p>

<p>Any help/ideas would be highly appreciated!</p>