Jim,
 
Once I did something similar experimenting with a Preferences implementation backed with a Derby database.
 
I defined the root node as a node with a reference to itself. Therefore I could have the constraint NOT NULL on the reference.
 
Because the root node is allways the first node that is created, it will have an id of 0 and must reference 0.
 
Hope this helps.
 
Please see below the table definitions:

CREATE TABLE USER_NODES

( ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1)

, PARENT_ID BIGINT

, NAME VARCHAR(80) NOT NULL

, PRIMARY KEY (ID))

 

ALTER TABLE USER_NODES

ADD FOREIGN KEY (PARENT_ID) REFERENCES USER_NODES

ON DELETE CASCADE ON UPDATE RESTRICT

 

CREATE TABLE ENTRY_TABLE

(NODE_ID BIGINT NOT NULL

, ENTRY_KEY VARCHAR(80) NOT NULL

, ENTRY_VALUE VARCHAR(8192) NOT NULL

, UNIQUE (NODE_ID, ENTRY_KEY) )

 

ALTER TABLE ENTRY_TABLE

ADD FOREIGN KEY (NODE_ID) REFERENCES USER_NODES

ON DELETE CASCADE ON UPDATE RESTRICT


Kind regards,
 
Piet Blok
----- Original Message -----
From: Jim Newsham
To: 'Derby Discussion'
Sent: Wednesday, October 25, 2006 2:07 AM
Subject: RE: hierarchical table with unique constraint

 

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:jnewsham@referentia.com]
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