FOREIGNKEYCOLUMNS 

Prerequisites

You have generated the demo data for the SQL Tutorial.

Log on to the demo database instance DEMODB as user MONA.

Create referential constraints. Proceed as described in SQL Tutorial, Foreign Key Dependencies Between Tables.

Examples

You can use the system table FOREIGNKEYCOLUMNS to determine the following database information, among other things:

●     All referential constraints in which the column CNO in the CUSTOMER table is the referenced column

SELECT schemaname, tablename, columnname, fkeyname, rule
  FROM DOMAIN.FOREIGNKEYCOLUMNS
    WHERE reftablename = 'CUSTOMER'
      AND refcolumnname = 'CNO'

●     All referential constraints in which the column HNO in the RESERVATION table is the referenced column

SELECT fkeyname, rule
  FROM DOMAIN.FOREIGNKEYCOLUMNS
    WHERE tablename = 'RESERVATION'
      AND columnname = 'HNO'

●     All referential constraints in which the referencing columns are from the RESERVATION table

SELECT DISTINCT fkeyname, rule, refschemaname, reftablename
  FROM DOMAIN.FOREIGNKEYCOLUMNS
    WHERE tablename = 'RESERVATION'

All referential constraints: see FOREIGNKEYS