On page 372 of the book it says "a particular category can't have the same name as any of its siblings".

In order to enforce this a unique index is defined across (CAT_NAME,PARENT_CATEGORY_ID).

This constraint is only properly checked by the database for non-root categories. This is because root categories have PARENT_CATEGORY_ID=NULL, and you cannot enforce uniqueness on NULLable columns.

I am trying to implement a basic content management system which has the same business constraint on uniquely-named siblings. The only way I can see to enforce this is either:

a) a database trigger
b) an Event listener

I prefer b) since database triggers tie me to one SQL dialect.

Is there any other way of doing this?

I would like to be able to declare PARENT_CATEGORY_ID as NOT NULL.
Then I would reserve the CATEGORY_ID=0 for one special root folder, which would have no parent property and use a discriminator column to load the correct concrete class. But I haven't managed to make this work because I always run up against the problem that the PARENT_CATEGORY_ID column needs to be NULLable.


Duncan

The "really correct" way to represent a tree data is with a NODE and a LINK table. That means there is no nullable PARENT_ID column. Of course the "unique sibling names" constraint would also require a more complicated construct on most SQL databases (trigger, etc.).

Really, there is no perfect and easy solution for this problem. However, on the DBMSs I've tried, a composite unique constraint which spans a NULLable column works fine and also enforces a "unique root names" constraint.

Not sure what happened, but my database does indeed support the uniqueness constraint across a nullable column. Apologies for a bogus bug-report!