List foreign keys with columns in Db2 database

Query below returns foreign key constrant columns defined in a database.


        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

Sample results

Foreign keys in Sample database with their columns:

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.