How to get all foreign keys information from derby db?

Question

In SQL I would normaly get foreign keys information like so...

SELECT
    TABLE_SCHEMA as fromSchema,
    TABLE_NAME as fromTable,
    COLUMN_NAME as fromColumn,
    REFERENCED_TABLE_SCHEMA as toSchema,
    REFERENCED_TABLE_NAME as toTable,
    REFERENCED_COLUMN_NAME as toColumn
FROM information_schema.key_column_usage col
where REFERENCED_COLUMN_NAME is not null
  and REFERENCED_TABLE_NAME is not null
  and REFERENCED_TABLE_SCHEMA is not null

But how can I get the same informations from DERBY database? I don't know how to get informations for columns toColumn and fromColumn??? I scanned whole derby documentation but I have no clue how to get name of foreign key column.

select FS.SCHEMANAME as "fromScheme",
       FT.TABLENAME  as "fromTable",
       TS.SCHEMANAME as "toScheme",
       TT.TABLENAME  as "toTable"
       -- How to get toColumn???
       -- How to get fromColumn???
from sys.SYSFOREIGNKEYS FK
         join SYS.SYSCONSTRAINTS S0 on FK.CONSTRAINTID = S0.CONSTRAINTID
         join SYS.SYSCONSTRAINTS S1 on FK.KEYCONSTRAINTID = S1.CONSTRAINTID
         join sys.SYSTABLES FT on S0.TABLEID = FT.TABLEID
         join sys.SYSSCHEMAS FS on FS.SCHEMAID = S0.SCHEMAID
         join sys.SYSTABLES TT on S1.TABLEID = TT.TABLEID
         join sys.SYSSCHEMAS TS on TS.SCHEMAID = S1.SCHEMAID;

In response to Lajos Arpad (2* comment)

create schema test;
CREATE TABLE test.Course
(
    id   INTEGER     NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    name VARCHAR(50) NOT NULL,
    CONSTRAINT primary_key PRIMARY KEY (id)
);

CREATE TABLE test.Student
(
    id        INTEGER     NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    name      VARCHAR(50) NOT NULL,
    surname   VARCHAR(50) NOT NULL,
    faculty   VARCHAR(50) NOT NULL,
    course_id INTEGER     NOT NULL,
    CONSTRAINT student_pk PRIMARY KEY (id),
    CONSTRAINT student_fk FOREIGN KEY (course_id) REFERENCES test.course
);

select
    '----------SYS CONSTRAINTS---------------',
    C.*,
    '----------SYS FOREIGN KEYS---------------',
    F.*,
    '----------SYS CONGLOMERATES---------------',
    CON.*,
    '----------SYS TABLES---------------',
    T.*,
    '----------SYS SCHEMAS---------------',
    SC.*
from SYSCONSTRAINTS C
join SYS.SYSFOREIGNKEYS F on C.CONSTRAINTID = F.CONSTRAINTID
join sys.SYSCONGLOMERATES CON on CON.CONGLOMERATEID = F.CONGLOMERATEID
join sys.SYSTABLES T on T.TABLEID = CON.TABLEID
join sys.SYSSCHEMAS SC on SC.SCHEMAID = CON.SCHEMAID;

Produces folowing results...

Answer 1

In the sys database you have these fields:

• sysconstraints.constraintid
• sysforeignkeys.constraintid
• sysforeignkeys.conglomerateid
• sysconglomerates.conglomerateid
• sysconglomerates.tableid
• systables.tableid
• systables.schemaid
• sysschemas.schemaid
• sysconstraints.schemaid

Join these together, filter by sysconstraints.type = 'F' and choose the columns you are interested in.