You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Andy Jefferson <li...@datanucleus.org> on 2011/06/28 16:27:18 UTC
DatabaseMetaData.getIndexInfo() to get unique compound indexes
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
So I can work out that I have a compound index here ... since all have this
weird name. But I can't work out that it is UNIQUE.
Questions :
1. How do I get to know that these are parts of "constrName" ... rather than
"SQLxxxxxxxx" ?
2. How do I get to know that they are part of a UNIQUE ?
TIA
--
Andy
Re: DatabaseMetaData.getIndexInfo() to get unique compound indexes
Posted by Andy Jefferson <li...@datanucleus.org>.
> 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.
Thanks Rick, that works much better :-)
--
Andy
Re: DatabaseMetaData.getIndexInfo() to get unique compound indexes
Posted by Rick Hillegas <ri...@oracle.com>.
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
>
Re: DatabaseMetaData.getIndexInfo() to get unique compound indexes
Posted by Andy Jefferson <li...@datanucleus.org>.
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
--
Andy