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

Question

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:

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"))

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.

Are you saying that the index name issue is present using _both_ methods? — Tim Biegeleisen, Dec 24 '18 at 00:24
yup tried both, got the same results. I can paste the resultset jdbc gives if it helps since the structure is a bit different, but the indices are named the same in both. — danf, Dec 24 '18 at 00:25
@TimBiegeleisen edited the post and pasted the example output I get in both cases — danf, Dec 24 '18 at 00:35
Why do you need to know the names? I can speculate and say that Derby wants to make sure that each index always has a unique name. — Tim Biegeleisen, Dec 24 '18 at 01:57
@TimBiegeleisen I need to be able to find out if some schema conversion ran correctly, it influences the state of my application. — danf, Dec 24 '18 at 06:58

score 8 · Answer 1 · answered Dec 24 '18 at 07:42

8

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';

answered Dec 24 '18 at 07:42

Noam

1,018
7
18

Exactly so. Note, moreover, that the "index" is a different object, both conceptually and physically, than the "constraint". What you named is the constraint, not the index. You can also name an index, but that's different than naming the constraint and having Derby generate a backing index for you automatically. – Bryan Pendleton Dec 24 '18 at 16:02

score 0 · Answer 2 · answered Dec 24 '18 at 13:58

0

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.

I just tested using DBeaver and derby-10.14.2.0.jar, and I found that, as @Noam mentions, "MY_TABLE_PK" is listed in SYS.SYSCONSTRAINTS

SELECT * FROM SYS.SYSCONSTRAINTS WHERE CONSTRAINTNAME='MY_TABLE_PK'

and "MY_TABLE_IX1" is listed in SYS.SYSCONGLOMERATES

SELECT * FROM SYS.SYSCONGLOMERATES WHERE CONGLOMERATENAME='MY_TABLE_IX1'

answered Dec 24 '18 at 13:58

Gord Thompson

116,920
32
215
418

Thanks for replying, That's kinda weird since that's not my case at all. I did omit one detail from my example since I didn't think it matters but perhaps it does: I also have a foreign key defined as `CONSTRAINT MY_TABLE_ATTR_FK FOREIGN KEY (SESSION_ID) REFERENCES SOME_OTHER_TABLE(SESSION_ID) ON DELETE CASCADE` - perhaps the difference is there..? – danf Dec 24 '18 at 14:52
@danf - Strange, indeed. After adding the FK contraint I can see it, too: `SELECT * FROM SYS.SYSCONSTRAINTS WHERE CONSTRAINTNAME='MY_TABLE_ATTR_FK'`. Yes, there are a number of "SQL*" indexes created as well, but the named items do show up for me. I'm using the latest versions of Derby and DBeaver, BTW. – Gord Thompson Dec 24 '18 at 15:20

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

2 Answers2