db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "mark boylan" <markboy...@hotmail.com>
Subject Re: hierarchical table with unique constraint
Date Wed, 25 Oct 2006 14:35:49 GMT
You can store the node relationship as an entity separate from the node. 
Then, records that have no parent will simply have no corresponding 
relationship record. Joining across these tables should give you what you 
want.

create table node (
  node_id int not null primary key
    generated always as identity,
  node_name varchar(32) not null,
);

create table node_relationship (
  child_node_id int not null primary key
    references node(node_id),
  parent_node_id int not null references node(node_id),
);

create view node_hierarchy (
  select n.node_id, r.parent_node_id, n.node_name
  from node as n
  left outer join node_relationship as r
  where r.child_node_id = n.node_id
);



To find your roots:

select * from node_hierarchy
where r.parent_node_id is null;



- mark




>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