Unique name constraint accross siblings in a closure tree

Question

I'm wondering how to enforce a unique constraint for the names of sibling nodes when using a closure table in MySQL to model a hierarchy.

This is my schema:

create table spaces (
  id int not null, 
  name varchar(50) not null,
  parent int not null,
  primary key (id),
  foreign key (parent) references spaces(id),
  unique key (name, parent)
)

create table space_paths (
  ancestor int not null,
  descendant int not null,
  depth int not null,
  primary key (ancestor, descendant),
  foreign key (ancestor) references spaces(id),
  foreign key (descendant) references spaces(id)
)

With this schema I'm using a unique constraint on the spaces table to verify that no sibling has the same name.

The downside of this approach is that it denormalizes the hierarchy metadata encapsulated in the space_paths table. The implication is that I would need to manually manage the consistency of parent field in the spaces table with the paths in the space_paths table.

Is there a way I could redesign the schema to have the database enforce a unique name constraint across siblings without having to denormalize?

Answer 1

Using a closure table isn't really a relevant part of your problem here—you just want a UNIQUE KEY on parent, name, and that's what it seems like you've already got defined, so you should be good.

One thing that may be causing you grief is that you have a NOT NULL constraint on the parent column. How are you going to support "root" nodes? You could make root nodes use a parent value equal to their ID column, but that will require a self-assigned primary key (AFAIK you can't reference the autoincrement value in an insert statement):

INSERT INTO spaces(id, name, parent) values (0, 'root', 0);