From derby-user-return-5430-apmail-db-derby-user-archive=db.apache.org@db.apache.org Thu Oct 26 06:52:02 2006 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 43218 invoked from network); 26 Oct 2006 06:33:57 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 26 Oct 2006 06:33:55 -0000 Received: (qmail 37230 invoked by uid 500); 24 Oct 2006 22:18:33 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 37126 invoked by uid 500); 24 Oct 2006 22:18:33 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 37057 invoked by uid 99); 24 Oct 2006 22:18:32 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 24 Oct 2006 15:18:32 -0700 X-ASF-Spam-Status: No, hits=2.0 required=10.0 tests=HTML_MESSAGE X-Spam-Check-By: apache.org Received-SPF: neutral (herse.apache.org: local policy) Received: from [64.224.219.75] (HELO mail1.atl.registeredsite.com) (64.224.219.75) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 24 Oct 2006 15:18:17 -0700 Received: from mail2.referentia.com (mail2.referentia.com [216.122.129.145]) by mail1.atl.registeredsite.com (8.12.11.20060308/8.12.11) with ESMTP id k9OMHsUh030589 for ; Tue, 24 Oct 2006 18:17:54 -0400 Received: from EETHKOTH (corp1.referentia.com [64.128.15.194]) by mail2.referentia.com (8.11.6/8.11.0) with ESMTP id k9OMHrr52368 for ; Tue, 24 Oct 2006 12:17:53 -1000 (HST) (envelope-from jnewsham@referentia.com) From: "Jim Newsham" To: "'Derby Discussion'" Subject: hierarchical table with unique constraint Date: Tue, 24 Oct 2006 12:17:43 -1000 Message-ID: <00dc01c6f7ba$3daaf6b0$8700a8c0@referentia.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00DD_01C6F766.6C0612A0" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 Thread-Index: Acb3ujlKM1hPi+yLT/26vEm3RtvPdA== X-Virus-Checked: Checked by ClamAV on apache.org This is a multi-part message in MIME format. ------=_NextPart_000_00DD_01C6F766.6C0612A0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_00DD_01C6F766.6C0612A0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

 

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           &nb= sp;    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

------=_NextPart_000_00DD_01C6F766.6C0612A0--