db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Piet Blok" <pbh...@wanadoo.nl>
Subject Re: hierarchical table with unique constraint
Date Wed, 25 Oct 2006 08:44:17 GMT
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

Mime
View raw message