db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jim Newsham" <jnews...@referentia.com>
Subject RE: hierarchical table with unique constraint
Date Wed, 25 Oct 2006 00:07:38 GMT

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.






From: Jim Newsham [mailto:jnewsham@referentia.com] 
Sent: Tuesday, October 24, 2006 12:18 PM
To: 'Derby Discussion'
Subject: hierarchical table with unique constraint





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?




View raw message