Query below returns foreign key constrant columns defined in a database.
select ref.tabschema concat '.' concat ref.tabname as foreign_table, '>-' as rel, ref.reftabschema concat '.' concat ref.reftabname as primary_table, key.colseq as no, key.colname as fk_column_name, ' = ' as join, keypk.colname as pk_column_name, ref.constname as fk_constraint_name from syscat.references ref left outer join syscat.keycoluse key on key.tabschema = ref.tabschema and key.tabname = ref.tabname and key.constname = ref.constname left outer join syscat.keycoluse keypk on keypk.tabschema = ref.reftabschema and keypk.tabname = ref.reftabname and keypk.constname = ref.refkeyname and keypk.colseq=key.colseq
- foreign_table - foreign table name with schema name
- rel - relationship symbol implicating direction
- primary_table - primary (referenced) table name with schema name
- no - id of the column in key. Single coumn keys always have 1, composite keys have 1, 2, ... n for each column of the key
- fk_column_name - foreign table column
- join - "=" symbol indicating join operation for pair of columns
- pk_column_name - primary (referenced) table column
- fk_constraint_name - foreign key constraint name
- One row represents one foreign key column. If foreign key consists of multiple columns (composite key), each column appears separately.
- Scope of rows: all foregin keys in a database and their columns
- Ordered by foreign table schema name and table name and column ordinal posion in key
Foreign keys in Sample database with their columns: