[ http://issues.apache.org/jira/browse/DERBY-1577?page=comments#action_12427428 ] Jorg Janke commented on DERBY-1577: -----------------------------------
Yes DERBY-543 is basically the same - my suggestion would to use the same name for the index as for the constraint. But, that the type (statistic, normal, other, ..) of index is unfortunately the next hurdle. Basically the index info do not allow me to differentiate between a normal index, a primary key or forign key - So the issue is NOT solved - Jason asked me to close this one and open a new bug for this issue - DERBY-1669 > 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
