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?