From: Jim Newsham
Sent: Tuesday, October 24, 2006
To: 'Derby Discussion'
Subject: hierarchical table with
I’d like to create a hierarchical table which
references itself and enforces uniqueness. For example:
create table node (
int not null generated always as identity primary key,
varchar(32) not null,
unique (name, fk_parent_id),
foreign key (fk_parent_id) references node (id) on
Every node has a parent node, with the exception of root
node(s). Allowing fk_parent_id to be null allows for the root node(s) to
exist. By uniqueness, I mean that there should only be one child node
named A for a given parent node; this is why I include the parent node and the
node’s name in the unique constraint.
However, derby fails for the above create statement unless
“not null” is added to the spec for fk_parent_id. It says
that a unique key cannot contain a nullable field. Any ideas on
accomplishing the above?