Wednesday, April 7, 2021

Displaying foreign key constraints using SQL

foreign key information using sql

I was asked:

What about referenced tables when you specify a foreign key when creating a table? I can check this when I run DSPFD, it is in Parent File Description but I still can't find an SQL to elaborate all tables in our Application.

I have to admit this one took me some time to find the information I need to provide the example in this post.

A foreign key is one of the many constraints that can be used with Db2 for i tables and DDS files. Doing a quick search of the IBM KnowledgeCenter I found the following:

  • SYSCST:  Every constraint, can be considered the "header" file for constraints
  • SYSCSTCOL:  Columns upon which the constraints have been defined
  • SYSCSTDEP:  Tables upon which the constrains have been defined
  • SYSKEYCST:  Every unique, primary, and foreign key that has been defined
  • SYSREFCST:  Foreign keys that have been defined

Before I start showing which of these Views I used I need to show the code for my Tables. The first table, TABLE1 is defined thus:

CREATE TABLE MYLIB.TABLE1
(CUSTOMER VARCHAR(10),
 PRIMARY KEY(CUSTOMER)
) ;

I can then use the CUSTOMER column as a foreign key in TABLE2:

CREATE TABLE MYLIB.TABLE2
(COLUMN1 INTEGER,
 COLUMN2 TIMESTAMP,
 COLUMN3 VARCHAR(10),
 CONSTRAINT FK_COLUMN3 FOREIGN KEY(COLUMN3)
                       REFERENCES TABLE1(CUSTOMER)
) ;

A constraint, FK_COLUMN3, has been defined as a foreign key in TABLE2, defining the column in TABLE2 COLUMN3 to reference the column CUSTOMER in TABLE1.

As the person who asked the question said I can see this constraint using the Display File Description command, DSPFD:

DSPFD FILE(MYLIB/TABLE2) TYPE(*CST)

This displays the following:

Constraint Description
    Referential Constraint
      Constraint  . . . . . . . . . : CST       FK_COLUMN3
        Type  . . . . . . . . . . . : TYPE      *REFCST
        Check pending . . . . . . . :           NO
        Constraint state  . . . . . : STATE     ESTABLISHED
                                                *ENABLED

      File  . . . . . . . . . . . . : PRNFILE   TABLE1
          Library . . . . . . . . . : LIB       MYLIB
        Parent key  . . . . . . . . : PRNKEY    CUSTOMER
      Foreign key . . . . . . . . . : FRNKEY    COLUMN3
      Delete rule . . . . . . . . . : DLTRULE   *NOACTION
      Update rule . . . . . . . . . : UPDRULE   *NOACTION

After searching in all of the Views of constraint information I found the ones I would need to use:

  • SYSCST:  Basic data about the constraint
  • SYSKEYCST:  Foreign key information
  • SYSREFCST:  Constraint detail information

Here is the map of the DSPFD to the SQL Views' columns:

DSPFD
parameter
SQL View View column
Constraint SYSCST CONSTRAINT_NAME
Check pending SYSCST CHECK_PENDING
Constraint state SYSCST CONSTRAINT_STATE
SYSCST ENABLED
Parent file description: file SYSREFCST UNIQUE_CONSTRAINT_NAME
Parent file description: library
Parent file description: parent key
Foreign key SYSCST COLUMN_NAME
Delete rule SYSREFCST DELETE_RULE
Update rule SYSREFCST UPDATE_RULE

In my example SQL statement I will be having some additional columns:

  • SYSCST:
    • SYSTEM_TABLE_SCHEMA:  Library
    • SYSTEM_TABLE_NAME:  System table name
  • SYSKEYCST:
    • COLUMN_NAME:  SQL long column name
    • SYSTEM_COLUMN_NAME:  Short column name
    • ORDINAL_POSITION:  Generated sequence

The finished SQL Select statement is:

01  SELECT A.SYSTEM_TABLE_SCHEMA,A.SYSTEM_TABLE_NAME,
02         A.CONSTRAINT_NAME,A.CHECK_PENDING,A.CONSTRAINT_STATE,
03         A.ENABLED,
04         B.UNIQUE_CONSTRAINT_NAME,B.DELETE_RULE,B.UPDATE_RULE,
05         c.COLUMN_NAME,C.SYSTEM_COLUMN_NAME,C.ORDINAL_POSITION
06  FROM QSYS2.SYSCST A
07    LEFT OUTER JOIN QSYS2.SYSREFCST B
08  ON A.CONSTRAINT_SCHEMA = B.CONSTRAINT_SCHEMA
09  AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
10    LEFT OUTER JOIN QSYS2.SYSKEYCST C
11  ON A.CONSTRAINT_SCHEMA = C.CONSTRAINT_SCHEMA
12  AND A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
13  WHERE A.SYSTEM_TABLE_SCHEMA = 'MYLIB'
14  AND A.SYSTEM_TABLE_NAME = 'TABLE2'
15  ORDER BY C.ORDINAL_POSITION ;

Lines 1 - 3: All the columns prefixed with "A" come from SYSCST.

Line 4: "B" prefix is for columns from SYSREFCST.

Line 5: "C" for columns from SYSKEYCST.

Line 6: SYSCST is the "main" View.

Lines 7 – 9: Joining to SYSREFCST using the constraint library and name.

Lines 10 – 12: Joining to SYSKEYCST using the constraint library and name.

Lines 13 and 14: Here is where I define the Library and Table name used by this statement.

Line 15: Ordering the results by the ordinal position of the foreign keys.

I have had to break the results into three parts to make it fit in this page.

SYSTEM_       SYSTEM_     CONSTRAINT  CHECK_   CONSTRAINT
TABLE_SCHEMA  TABLE_NAME  _NAME       PENDING  _STATE       ENABLED
------------  ----------  ----------  -------  -----------  -------
MYLIB         TABLE2      FK_COLUMN3  NO       ESTABLISHED  YES

UNIQUE_                              DELETE     UPDATE
CONSTRAINT_NAME                      _RULE      _RULE
-----------------------------------  ---------  ---------
Q_U4142SH_TESTTABLE1_CUSTOMER_00001  NO ACTION  NO ACTION

             SYSTEM_      ORDINAL_
COLUMN_NAME  COLUMN_NAME  POSITION
-----------  -----------  --------
COLUMN3      COLUMN3             1

If the person who asked the question wanted to run this for one of their own tables they would change the WHERE clause.

 

 

This article was written for IBM i 7.4, and should work for some earlier releases too.

1 comment:

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.