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:[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
