Displaying foreign key constraints 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.

 

• SYSCST View
• SYSKEYCST View
• SYSREFCST View

 

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