[ http://issues.apache.org/jira/browse/DERBY-1577?page=comments#action_12427514 ] Kathey Marsden commented on DERBY-1577: ---------------------------------------
Yes, I like DERBY-1669 better as it states the core problem that there is not a way to differentiate between a normal index, a primary key or foreign key. It sounds like we need to discuss the best technical solution on the derby-dev alias. We can leave this one open too until that is decided, but I think it is better to branch the discussion off of DERBY-1669 as it is states the problem to be solved most clearly. (Just for clarification since folks on this list don't know Jason, Jason is one of the frontline Cloudscape support folks at IBM. ) > 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
