RE: hierarchical table with unique constraint

[Jim Newsham]

As a workaround, I changed “generated” from “always” to “by default”, and insert the root node with a reference to itself.  I’ll detect and treat the self-referential case as root node in my code.  But if anyone has any other useful ideas, I’d be interested to hear them.   Thanks, Jim   From: Jim Newsham [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 24, 2006 12:18 PM To: 'Derby Discussion' Subject: hierarchical table with unique constraint     Hi,   I’d like to create a hierarchical table which references itself and enforces uniqueness.  For example:   create table node (   id                  int not null generated always as identity primary key,   name                varchar(32) not null,   fk_parent_id        int,     unique (name, fk_parent_id),   foreign key (fk_parent_id) references node (id) on delete cascade );   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?   Thanks, Jim