[ http://issues.apache.org/jira/browse/DERBY-1577?page=comments#action_12424942 ] Kathey Marsden commented on DERBY-1577: ---------------------------------------
I think that the name of the backing index created is in fact different than the primary key, so I think it is correct that getIndexInfo() returns the generated index name. Is it really a bug? If you need the primary key and the index name to match, I wonder if RENAME INDEX might help: http://db.apache.org/derby/docs/10.1/ref/rrefsqlj95598.html > DatabaseMetaData.getIndexInfo() returns internal names > ------------------------------------------------------ > > Key: DERBY-1577 > URL: http://issues.apache.org/jira/browse/DERBY-1577 > Project: Derby > Issue Type: Bug > Components: JDBC > Affects Versions: 10.1.3.1 > Environment: Windows 2003 Server > Reporter: Jorg Janke > > Problem: > ------------- > We inquire the meta data of the database and then dynamically update the > database to its target date (e.g. add/modify tables, columns, indexes, > constraints, ...) via (standard) DDL. > When requesting the indexes for a table, we get the internal name, not the > index name. > When (re-) the submitting the DDL > ALTER TABLE AD_ACCESSLOG ADD CONSTRAINT AD_ACCESSLOG_KEY PRIMARY KEY > (AD_ACCESSLOG_ID) > I get the error message > Constraints 'AD_ACCESSLOG_KEY' and 'AD_ACCESSLOG_KEY' have the same set of > columns, which is not allowed. > Technical Description > --------------------- > Problem is that the Derby implementation of > DatabaseMetaData.getIndexInfo() > returns the internal (conglomerate) name rather then the "real" name of the > index. > I checked - in > org.apache.derby.client.am.DatabaseMetaData.getIndexInfoX(..) you call the > function SYSIBM.SQLSTATISTICS(?,?,?,?,?,?) - which returns the wrong data. > Results from getIndexInfo(..) > 0: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=0, > INDEX_QUALIFIER=, INDEX_NAME=SQL060709062929330, TYPE=3, ORDINAL_POSITION=1, > COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, > FILTER_CONDITION=null > 1: TABLE_CAT=, TABLE_SCHEM=COMPIERE, TABLE_NAME=AD_ACCESSLOG, NON_UNIQUE=1, > INDEX_QUALIFIER=, INDEX_NAME=SQL060716064852400, TYPE=3, ORDINAL_POSITION=1, > COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, > FILTER_CONDITION=null > Results from getImportedKeys(..) > 0: PKTABLE_CAT=, PKTABLE_SCHEM=COMPIERE, PKTABLE_NAME=AD_COLUMN, > PKCOLUMN_NAME=AD_COLUMN_ID, FKTABLE_CAT=, FKTABLE_SCHEM=COMPIERE, > FKTABLE_NAME=AD_ACCESSLOG, FKCOLUMN_NAME=AD_COLUMN_ID, KEY_SEQ=1, > UPDATE_RULE=3, DELETE_RULE=0, FK_NAME=ADCOLUMN_ADACCESSLOG, > PK_NAME=AD_COLUMN_KEY, DEFERRABILITY=7 > The problem would be solved, if in addition to the (internal type 3) index > info you would provide the index type 1/2 info with the resuly of > 0: .. INDEX_NAME=AD_ACCESSLOG_KEY, TYPE=1, ORDINAL_POSITION=1, > COLUMN_NAME=AD_ACCESSLOG_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, > FILTER_CONDITION=null > 1: .. INDEX_NAME=ADCOLUMN_ADACCESSLOG, TYPE=3, ORDINAL_POSITION=1, > COLUMN_NAME=AD_COLUMN_ID, ASC_OR_DESC=A, CARDINALITY=null, PAGES=null, > FILTER_CONDITION=null > The original table definition is: > CREATE TABLE AD_ACCESSLOG > ( > AD_ACCESSLOG_ID DECIMAL(10,0) NOT NULL, > AD_CLIENT_ID DECIMAL(10,0) NOT NULL, > AD_ORG_ID DECIMAL(10,0) NOT NULL, > ISACTIVE CHAR(1) DEFAULT 'Y' NOT NULL, > CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT > NULL, > CREATEDBY DECIMAL(10,0) NOT NULL, > UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT > NULL, > UPDATEDBY DECIMAL(10,0) NOT NULL, > AD_TABLE_ID DECIMAL(10,0) NULL, > AD_COLUMN_ID DECIMAL(10,0) NULL, > RECORD_ID DECIMAL(10,0) NULL, > CONSTRAINT AD_ACCESSLOG_KEY > PRIMARY KEY (AD_ACCESSLOG_ID), > CONSTRAINT ADCOLUMN_ADACCESSLOG > FOREIGN KEY (AD_COLUMN_ID) > REFERENCES AD_COLUMN (AD_COLUMN_ID) > ) > --- > Note that you create an index for a constraint - that is fine, but it would > be helpful to again not get the internal name, but the "external". > Index 'SQL060716064852400' was created to enforce constraint > 'ADCOLUMN_ADACCESSLOG'. It can only be dropped by dropping the constraint. - > DROP INDEX SQL060716064852400 > --- > Help requested: > --------------- > If you please could fix it > and tell me if I could find/update/fix the function SYSIBM.SQLSTATISTICS. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
