CODEWITHSUNDEEP
postgresqlforeign-keysforeign-key-relationship
simonfi
simonfi

Reputation: 293

Non-unique foreign key in PostgreSQL?

I have a database design question, to which either of the following two would be help I would appreciate:

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

2) Example how to actually implement the desired design in PostgreSQL

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

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 ..
)

The idea is the following; I might have a structure like:

root node
    child node 1
    child node 2

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.

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

I understand that I cannot add a foreign key from Nodes.parent_nodeid to Nodes.node_id 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 Nodes.parent_nodeid references an existing Nodes.node_id value even though it won't be unique.

Any help/ideas would be highly appreciated!

Upvotes: 1

Views: 3067

Answers (1)

klin
klin

Reputation: 121889

You do not need a tree structure, as you always have only one level of dependency. Normalized database design:

create table nodes (
    node_id bigserial primary key,
    description text
)

create table revisions (
    revision_id bigserial primary key,
    node_id bigint references nodes,
    description text
);

You need a trigger on nodes which duplicates old row to descriptions on insert or update and copies a row from revisions instead of delete, implementing undo.

It is also not clear why to keep two nodes identifiers nid and node_id? This seems redundant.

Upvotes: 1

Related Questions