Hierarchical data using closure table, how to ensure that a column is unique within the parent node? I store hierarchical data in a Node-table, combined with a closure table (NodeClosure). Each node has a column &quot;name&quot; that should be unique among the sibling nodes (link to parent-Node + name should be unique).
This is definition of the tables:

Table &quot;Node&quot;

Column &quot;nodeID&quot; (int not null, autoincrement, primary key)
Column &quot;name&quot; (text not null)


Table &quot;NodeClosure&quot;

Column &quot;ancestorID&quot; (int not null, primary key, foreign key to Node)
Column &quot;descendantID&quot; (int not null, primary key, foreign key to Node)
Column &quot;depth&quot; (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?
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.