With the introduction of referential integrity into the major enterprise DBMS products during the mid-1990s, system catalogs were expanded to describe primary keys, foreign keys, and the parent/child relationships that they create. In DB2, which was among the first to support referential integrity, the description is provided by the SYSCAT. REFERENCES system catalog table, described in Table 16-8. Every parent/child relationship between two tables in the database is represented by a single row in the SYSCAT.REFERENCES table. The row identifies the names of the parent and child tables, the name of the relationship, and the delete and update rules for the relationship. You can query it to find out about the relationships in the database:
List all of the parent/child relationships among my tables, showing the name of the relationship, the name of the parent table, the name of the child table, and the delete rule for each one.
SELECT CONSTNAME, REFTABNAME, TABNAME, DELETERULE
FROM SYSCAT.REFERENCES
WHERE DEFINER = USER
List all of the tables related to the SALESREPS table as either a parent or a child.
SELECT REFTABNAME
FROM SYSCAT.REFERENCES
WHERE TABNAME = ‘SALESREPS’
UNION
SELECT TABNAME
FROM SYSCAT.REFERENCES
WHERE REFTABNAME = ‘SALESREPS’
The names of the foreign key columns and the corresponding primary key columns are listed (as text) in the FK_COLNAMES and PK_COLNAMES columns of the REFERENCES system table. A second system table, SYSCAT.KEYCOLUSE, shown in Table 16-9, provides a somewhat more useful form of the information. There is one row in this system table for each column in each foreign key, primary key, or uniqueness constraint defined in the database. A sequence number defines the order of the columns in a compound key. You can use this system table to find out the names of the columns that link a table to its parent, using a query like this one:
List the columns that link ORDERS to PRODUCTS in the relationship named ISFOR.
SELECT COLNAME, COLSEQ
FROM SYSCAT.KEYCOLUSE
WHERE CONSTNAME = ‘ISFOR’
ORDER BY COLSEQ
The primary key of a table and the parent/child relationships in which it participates are also summarized in the SYSCAT.TABLES and SYSCAT.COLUMNS system tables, shown previously in Tables 16-2 and 16-4. If a table has a primary key, the KEYCOLUMNS column in its row of the SYSCAT.TABLES system table is nonzero, and tells how many columns make up the primary key (one for a simple key; two or more for a composite key). In the SYSCAT.COLUMNS system table, the rows for the columns that make up the primary key have a nonzero value in their KEYSEQ column.
The value in this column indicates the position (1, 2, and so on) of the primary key column within the primary key.
You can query the SYSCAT.COLUMNS table to find a table’s primary key:
List the columns that form the primary key of the PRODUCTS table.
SELECT COLNAME, KEYSEQ, TYPENAME, REMARKS
FROM SYSCAT.COLUMNS
WHERE TABNAME = ‘PRODUCTS’
AND KEYSEQ > 0
ORDER BY KEYSEQ
The DB2 catalog support for primary and foreign keys is typical of that found in other major SQL products. The Oracle system view USER_CONSTRAINTS, for example, provides the same information as the DB2 SYSCAT.REFERENCES system table. Information about the specific columns that make up a foreign key or primary key appears in the Oracle USER_CONS_COLUMNS system view, which is analogous to the dB2 SYSCAT.KEYCOLUSE system table. Microsoft SQL Server has a similar system catalog structure, with foreign key information divided between the SYSFOREIGNKEYS and SYSREFERENCES system tables.
Informix Universal Server takes a similar approach to the DB2 catalog, but with the same types of differences previously illustrated in its table information and column information support. Each constraint defined within the database generates one row in the Informix SYSCONSTRAINTS system table, which defines the name of the constraint and its type (check constraint, primary key, referential, and so on). This system table also assigns an internal constraint-id number to identify the constraint within the catalog. The table to which the constraint applies is also identified by table-id (which serves as a foreign key to the SYSTABLES system table).
Further information about the referential constraints (foreign keys) is contained in a SYSREFERENCES system table. Again in this table, the constraint, the primary key, and the parent table are identified by internal IDs that link the foreign key information to the SYSCONSTRAINTS and SYSTABLES system tables. The SYSREFERENCES table contains information about the delete rule and update rule that apply to the foreign key relationship and similar information.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.