Hierarchical data using closure table, how to ensure that a column is unique within the parent node

database-designhierarchyunique-constraint

I store hierarchical data in a Node-table, combined with a closure table (NodeClosure). Each node has a column "name" that should be unique among the sibling nodes (link to parent-Node + name should be unique).

This is definition of the tables:

  • Table "Node"
    • Column "nodeID" (int not null, autoincrement, primary key)
    • Column "name" (text not null)
  • Table "NodeClosure"
    • Column "ancestorID" (int not null, primary key, foreign key to Node)
    • Column "descendantID" (int not null, primary key, foreign key to Node)
    • Column "depth" (int not null)

For each parent node, I want the names of its direct child nodes to be unique for that parent node.

Do I have to add a parentID-column to Node (and duplicate the parent/child information) to be able to add a unique index on parentID + name, or is there a different solution?

Best Answer

I'm assuming (from the fact you considered adding a parentId column) that each Node has a single parent.

Two things to do:

  • Add parent column to Node, with a self-referencing foreign key to the nodeID column. This ensures that each Node links to a single parent Node. This should be null for the root node.
  • Get rid of NodeClosure table. Seriously, you don't need it.

So your design becomes simply:

Node

  • nodeID (int not null, autoincrement, primary key)
  • parentID (int null, foreign key to Node(nodeID))
  • name (text not null)

Now you can indeed enforce unique names among siblings by adding a unique constraint on parent + name.

You can derive the ancestor/descendent relationship and level that was previously in NodeClosure using queries against the Node table. Syntax for this varies depending on database.

Related Question