db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: DatabaseMetaData.getIndexInfo() to get unique compound indexes
Date Tue, 28 Jun 2011 15:19:28 GMT
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
>


Mime
View raw message