Apache Derby gives strange names to indices I created with meaningful names

  • A+
Category:Languages

I'm creating a simple demo table in derby using this ddl:

CREATE TABLE MY_TABLE (     SESSION_ID CHAR(36),     ATTRIBUTE_NAME VARCHAR(200),     CONSTRAINT MY_TABLE_PK PRIMARY KEY (SESSION_ID, ATTRIBUTE_NAME), ); CREATE INDEX MY_TABLE_IX1 ON MY_TABLE (SESSION_ID); 

I want to verify on a test if the INDEX MY_TABLE_IX1 was actually created.

Searching online I see 2 possible methods of achieving this:

  1. JDBC -->

    Using DatabaseMetaData I can do something along the lines of

    metadata.getIndexInfo(null, "APP", "MY_TABLE", false, false) 

    Iterate over the result set until I get a row where

    "MY_TABLE_IX1".equals(resultSet.getString("INDEX_NAME")) 
  2. SQL -->

    SELECT c.CONGLOMERATENAME, t.TABLENAME FROM SYS.SYSCONGLOMERATES c  JOIN SYS.SYSTABLES t ON c.TABLEID = t.TABLEID  WHERE c.CONGLOMERATENAME = 'MY_TABLE_IX1' AND t.TABLENAME = 'MY_TABLE' 

Leaving aside the obvious (such as I should also filter by column name, stuff like that), I encounter a very strange behavior:
Derby saves some of my indices as strings of the form SQL181215003216931 making me unable to locate these indices by name, while other indices are saved by the name I specified in my ddl.

Although I gave a small example, my actual schema is quite large, and if I run the following:

SELECT c.CONGLOMERATENAME, t.TABLENAME FROM SYS.SYSCONGLOMERATES c  JOIN SYS.SYSTABLES t ON c.TABLEID = t.TABLEID  WHERE c.CONGLOMERATENAME LIKE '%SQL%' 

I get quite a large result of indices that are named in the same manner (they differ by the trailing numbers after the SQL part) although I gave each and every one of them a meaningful name.

I tried scouring the web for info about this behavior but came up empty - does anyone know the answer to my mystery?
It seems there's no reference from the SQL#### type names to the names I originally gave, so how can I locate my indices based on my names?

Here's a sample output from the second SQL query:

CONGLOMERATENAME    TABLENAME ------------------------------------ SQL181215003159230  MY_TABLE SQL181215003159240  SOME_OTHER_TABLE SQL181215003216890  YET_ANOTHER_TABLE 

And from the JDBC execution:

TABLE_CAT|TABLE_SCHEMA|TABLE_NAME            |NON_UNIQUE|INDEX_QUALIFIER|INDEX_NAME        |TYPE|ORDINAL_POSITION|COLUMN_NAME|ASC_OR_DESC|CARDINALITY|PAGES|FILTER_CONDITION|          |APP         |MY_TABLE              |false     |               |SQL181224003626061|3   |1               |SESSION_ID |A          |null       |null |null            |          |APP         |SOME_OTHER_TABLE      |false     |               |SQL181215003159240|3   |1               |SESSION_ID |A          |null       |null |null            | 

---Edit----: Based on @Noam's answer below, seems he is correct and that the SQL### indices are indeed Primary Keys and indices set on Primary Key columns (although that's basically unwarranted, but that's besides the point).

Still my issue is that I need to find out if that index I declared with a specific name - and that name is nowhere to be found.

 


The SQL* are the unique/primary indexes that are configured directly on the table definition, like the MY_TABLE_PK

According to their documentation (https://db.apache.org/derby/docs/10.1/ref/rrefsqlj13590.html) you should be able to find the indexes of the constraints you can use this query (I made minor modifications to the query there):

SELECT * FROM  SYS.SYSCONSTRAINTS t JOIN SYS.SYSCONGLOMERATES c ON t.TABLEID = c.TABLEID  WHERE CONSTRAINTNAME = 'MY_TABLE_PK'; 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: