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:
DatabaseMetaDataI 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
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.
SQL* are the unique/primary indexes that are configured directly on the table definition, like the
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';