You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Jim Newsham <jn...@referentia.com> on 2006/10/25 00:17:43 UTC
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
Re: hierarchical table with unique constraint
Posted by mark boylan <ma...@hotmail.com>.
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
Re: hierarchical table with unique constraint
Posted by Piet Blok <pb...@wanadoo.nl>.
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
RE: hierarchical table with unique constraint
Posted by Jim Newsham <jn...@referentia.com>.
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