From derby-user-return-9802-apmail-db-derby-user-archive=db.apache.org@db.apache.org Fri Sep 19 15:25:48 2008 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 45037 invoked from network); 19 Sep 2008 15:25:48 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 19 Sep 2008 15:25:48 -0000 Received: (qmail 1446 invoked by uid 500); 19 Sep 2008 15:25:38 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 1419 invoked by uid 500); 19 Sep 2008 15:25:38 -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 1351 invoked by uid 99); 19 Sep 2008 15:25:37 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 19 Sep 2008 08:25:37 -0700 X-ASF-Spam-Status: No, hits=-4.0 required=10.0 tests=RCVD_IN_DNSWL_MED,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [192.18.43.132] (HELO sca-es-mail-1.sun.com) (192.18.43.132) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 19 Sep 2008 15:24:36 +0000 Received: from fe-sfbay-09.sun.com ([192.18.43.129]) by sca-es-mail-1.sun.com (8.13.7+Sun/8.12.9) with ESMTP id m8JFOxex020455 for ; Fri, 19 Sep 2008 08:24:59 -0700 (PDT) Received: from conversion-daemon.fe-sfbay-09.sun.com by fe-sfbay-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) id <0K7G008017SNN400@fe-sfbay-09.sun.com> (original mail from Richard.Hillegas@Sun.COM) for derby-user@db.apache.org; Fri, 19 Sep 2008 08:24:59 -0700 (PDT) Received: from richard-hillegas-computer.local ([195.13.166.253]) by fe-sfbay-09.sun.com (Sun Java System Messaging Server 6.2-8.04 (built Feb 28 2007)) with ESMTPSA id <0K7G00JHA85EBS00@fe-sfbay-09.sun.com> for derby-user@db.apache.org; Fri, 19 Sep 2008 08:24:54 -0700 (PDT) Date: Fri, 19 Sep 2008 08:24:49 -0700 From: Rick Hillegas Subject: Re: How can I fetch constraint attribute on Column Level from SYS Tables?? In-reply-to: <2B1C0D29BA003143832FFB60D0B8A4FB55FBB2@srvvp01.VPHQ.local> Sender: Richard.Hillegas@Sun.COM To: Derby Discussion Message-id: <48D3C441.6090902@sun.com> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7BIT References: <2B1C0D29BA003143832FFB60D0B8A4FB55FBB2@srvvp01.VPHQ.local> User-Agent: Thunderbird 2.0.0.16 (Macintosh/20080707) X-Virus-Checked: Checked by ClamAV on apache.org Hi Takashi, If you are just looking for primary keys, you may find the information you need in DatabaseMetaData.getPrimaryKeys(). If you are just looking for a table's foreign keys, you may find the information you need in DatabaseMetaData.getImportedKeys(). You may also find more information in the DESCRIPTOR column of SYS.SYSCONGLOMERATES. The following query will give you this object: select tab.tablename, con.constraintname, cong.descriptor from sys.sysconstraints con, sys.systables tab, sys.syskeys keys, sys.sysconglomerates cong where con.constraintid = keys.constraintid and keys.conglomerateid = cong.conglomerateid and tab.tableid = con.tableid and tab.tablename = ? The DESCRIPTOR column holds a fairly informative object of type org.apache.derby.catalog.IndexDescriptor. Please note that IndexDescriptor is not part of Derby's public api and so you are not guaranteed that these objects will behave compatibly in future releases. On the bright side, I am not aware of any incompatible changes to this interface over the last 4 years. Hope this helps, -Rick Takashi Saruhashi wrote: > Hello all, > > My name is Takashi. Now, I want to fetch constraint attributes on Column > level from SYS Tables. > Does anyone know how to fetch it?? > > I checked Derby's reference manual here, > http://db.apache.org/derby/docs/10.3/ref/rrefsistabs38369.html > > Actually, in SYSCOLUMNS Table, there's no association with > SYSCONSTRAINTS Table and vice versa... > > However, when I create Table, I can assign some constraints on Column > level indeed. > Therefore, I guess there should be some way to fetch it on Column > level... > > Any suggestion is quite appreciated. > > > Best regards. > Takashi >