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 |
- hierarchical table with unique constraint Jim Newsham
- RE: hierarchical table with unique constraint Jim Newsham
- Re: hierarchical table with unique constraint Piet Blok
- Re: hierarchical table with unique constraint mark boylan