Derby - constraints.

Question

In the Derby server, how can you use the information in the system tables of the schema to create a select statement in order to retrieve the constraint names for each table?

Answer 1

The relevant manual is the Derby Reference Manual.

SELECT c.constraintname, t.tablename
    FROM sysconstraints c, systables t
    WHERE c.tableid = t.tableid;

Answer 2

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

Answer 3

SELECT sc.schemaname, co.constraintname, t.tablename, cg.descriptor, t2.tablename, cg2.descriptor, f.deleterule, f.updaterule
FROM sys.sysconstraints co
JOIN sys.sysschemas sc ON co.schemaid = sc.schemaid
JOIN sys.systables t ON co.tableid = t.tableid
JOIN sys.sysforeignkeys f ON co.constraintid = f.constraintid
JOIN sys.sysconglomerates cg ON f.conglomerateid = cg.conglomerateid
JOIN sys.sysconstraints co2 ON f.keyconstraintid = co2.constraintid
JOIN sys.systables t2 ON co2.tableid = t2.tableid
JOIN sys.syskeys k ON co2.constraintid = k.constraintid
JOIN sys.sysconglomerates cg2 ON k.conglomerateid = cg2.conglomerateid
WHERE co.type = 'F' 
    and sc.schemaname = current schema    

the two descriptor entries contain a list of column numbers for each table, like

BTREE(2,1)

where the numbers correspond to the column numbers in the syscolumns table for the corresponding table.

If anyone has an elegant way of extracting this in this query, I would like to know. I am getting a list of all the columns for a table in a separate query and extracting the names from that after parsing the descriptors to get the numbers.