Return-Path: X-Original-To: apmail-db-derby-user-archive@www.apache.org Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 037D167E1 for ; Tue, 28 Jun 2011 15:20:08 +0000 (UTC) Received: (qmail 85959 invoked by uid 500); 28 Jun 2011 15:20:07 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 85924 invoked by uid 500); 28 Jun 2011 15:20:06 -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 85914 invoked by uid 99); 28 Jun 2011 15:20:06 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 28 Jun 2011 15:20:06 +0000 X-ASF-Spam-Status: No, hits=-2.3 required=5.0 tests=RCVD_IN_DNSWL_MED,SPF_PASS,UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [148.87.113.121] (HELO rcsinet10.oracle.com) (148.87.113.121) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 28 Jun 2011 15:19:59 +0000 Received: from rtcsinet21.oracle.com (rtcsinet21.oracle.com [66.248.204.29]) by rcsinet10.oracle.com (Switch-3.4.4/Switch-3.4.2) with ESMTP id p5SFJalj000820 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK) for ; Tue, 28 Jun 2011 15:19:38 GMT Received: from acsmt358.oracle.com (acsmt358.oracle.com [141.146.40.158]) by rtcsinet21.oracle.com (8.14.4+Sun/8.14.4) with ESMTP id p5SFJZLT029738 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Tue, 28 Jun 2011 15:19:36 GMT Received: from abhmt101.oracle.com (abhmt101.oracle.com [141.146.116.53]) by acsmt358.oracle.com (8.12.11.20060308/8.12.11) with ESMTP id p5SFJUUD012047 for ; Tue, 28 Jun 2011 10:19:30 -0500 Received: from richard-hillegas-computer.local (/10.159.18.159) by default (Oracle Beehive Gateway v4.0) with ESMTP ; Tue, 28 Jun 2011 08:19:29 -0700 Message-ID: <4E09F100.9060502@oracle.com> Date: Tue, 28 Jun 2011 08:19:28 -0700 From: Rick Hillegas User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7 MIME-Version: 1.0 To: Derby Discussion Subject: Re: DatabaseMetaData.getIndexInfo() to get unique compound indexes References: <201106281527.18313.lists@datanucleus.org> <201106281556.08271.lists@datanucleus.org> In-Reply-To: <201106281556.08271.lists@datanucleus.org> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Source-IP: rtcsinet21.oracle.com [66.248.204.29] X-CT-RefId: str=0001.0A090204.4E09F10A.01B8:SCFSTAT5015188,ss=1,re=-4.000,fgs=0 Hi Andy, I believe that you see this behavior because the unique constraint does not give rise to a unique index. The index forbids duplicates if all of the key values are non-null. However, if any of the key values are null, then duplicates are allowed. If you need an index which forbids duplicate nulls, then you can use the CREATE INDEX statement. Hope this helps, -Rick On 6/28/11 7:56 AM, Andy Jefferson wrote: > On Tuesday 28 Jun 2011 15:27:18 Andy > DataNucleus (http://www.datanucleus.org) Jefferson wrote: >> If I create a unique index like this >> ALTER TABLE {tblName} ADD CONSTRAINT {constrName} UNIQUE (col1, col2, col3) >> >> and then call DatabaseMetaData.getIndexInfo() >> >> it returns >> tableCat = >> tableSchem = null >> tableName = tblName >> columnName = col1 >> nonUnique = true >> ordinalPosition = 1 >> indexName = SQL110628145704820 >> >> tableCat = >> tableSchem = null >> tableName = tblName >> columnName = col2 >> nonUnique = true >> ordinalPosition = 2 >> indexName = SQL110628145704820 >> >> tableCat = >> tableSchem = null >> tableName = tblName >> columnName = col3 >> nonUnique = true >> ordinalPosition = 3 >> indexName = SQL110628145704820 > I ought to mention that on the same case MySQL returns the correct index name, > and returns false on the "nonUnique", hence I have all information necessary >