views:

189

answers:

0

I 'RTFM' and did not find a way to retrieve the columns involved in a unique constraint for a given schema and table.

Somewhere I read that there should be an implicitly created unique index, which backs the unique constraint. Its name is supposed to be equal to the name of the conglomerate that corresponds to the unique constraint.

So I did the following:

ij> SELECT
    constrs.CONSTRAINTNAME,
    congls.CONGLOMERATENAME
FROM
    SYS.SYSCONSTRAINTS constrs,
    SYS.SYSKEYS keys,
    SYS.SYSSCHEMAS schemas,
    SYS.SYSTABLES tables,
    SYS.SYSCONGLOMERATES congls
WHERE
        schemas.SCHEMANAME = 'SCHEMA1'
    AND
        tables.TABLENAME = 'TABLE1'
    AND
        constrs.TYPE = 'U'
    AND
        constrs.SCHEMAID = schemas.SCHEMAID
    AND
        schemas.SCHEMAID = tables.SCHEMAID
    AND
        constrs.TABLEID = tables.TABLEID
    AND
        constrs.CONSTRAINTID = keys.CONSTRAINTID
    AND
        keys.CONGLOMERATEID = congls.CONGLOMERATEID;

CONSTRAINTNAME|CONGLOMERATENAME

UN2|SQL090415061524542

And then I used the result to filter unique index definitions I found in the JDBC meta data (incl. column names):

connection.getMetaData().getIndexInfo(null, "SCHEMA1", "TABLE1", true, false);

The only result I got from the meta data call was "SQL090415061524540", which does not match the conglomerate name of the unique constraint.

Does anyone have an idea how to obtain the column names of a unique constraint in Derby?

Thanks in advance!

(Just noticed that I added a new "answer" instead of a question... will open a new question for my concern.)