Show TOC

Example documentationFOREIGNKEYCOLUMNS

 

Use of the system table DOMAIN.FOREIGNKEYCOLUMNS

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

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

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Note the General Instructions for formulating SQL statements.

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 referencing 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'

More Information

All referential constraints: see FOREIGNKEYS