SQLForeignKeys--Get the list of foreign key columns

Purpose

SQLForeignKeys() returns information about foreign keys for the specified table. The information is returned in a SQL result set that can be processed using the same functions that are used to retrieve a result set generated by a query. PKCatalogName, NameLength1, PKSchemaName, NameLength2, FKCatalogName, NameLength4, FKSchemaName and NameLength5 are ignored. Columns 1, 2, 5, 6, 12, and 13 of the returned result set are always a zero-length string. Columns 10, 11, and 14 of the returned result set are always zero.

Syntax

SQLRETURN   SQLForeignKeys  (
                  SQLHSTMT          StatementHandle,  /* hstmt */
                  SQLCHAR      *FAR PKCatalogName,    /* szPkCatalogName */
                  SQLSMALLINT       NameLength1,      /* cbPkCatalogName */
                  SQLCHAR      *FAR PKSchemaName,     /* szPkSchemaName */
                  SQLSMALLINT       NameLength2,      /* cbPkSchemaName */
                  SQLCHAR      *FAR PKTableName,      /* szPkTableName */
                  SQLSMALLINT       NameLength3,      /* cbPkTableName */
                  SQLCHAR      *FAR FKCatalogName,    /* szFkCatalogName */
                  SQLSMALLINT       NameLength4       /* cbFkCatalogName */
                  SQLCHAR      *FAR FKSchemaName,     /* szFkSchemaName */
                  SQLSMALLINT       NameLength5,      /* cbFkSchemaName */
                  SQLCHAR      *FAR FKTableName,      /* szFkTableName */
                  SQLSMALLINT       NameLength6);     /* cbFkTableName */
 

Function arguments

Table 58. SQLForeignKeys arguments

Usage

If PKTableName contains a table name, and FKTableName is an empty string, SQLForeignKeys() returns a result set containing the primary key of the specified table and all of the foreign keys (in other tables) that refer to it.

If FKTableName contains a table name, and PKTableName is an empty string, SQLForeignKeys() returns a result set containing all of the foreign keys in the specified table and the primary keys (in other tables) to which they refer.

If both PKTableName and FKTableName contain table names, SQLForeignKeys() returns the foreign keys in the table specified in FKTableName that refer to the primary key of the table specified in PKTableName. This should be one key at the most.

If the foreign keys associated with a primary key are requested, the result set is ordered by FKTABLE_NAME and ORDINAL_POSITION. If the primary keys associated with a foreign key are requested, the result set is ordered by PKTABLE_NAME and ORDINAL_POSITION.

The VARCHAR columns of the catalog functions result set are declared with a maximum length attribute of 128 to be consistent with SQL92 limits.

Although new columns might be added and the names of the existing columns changed in future releases, the position of the current columns does not change.

The result set contains these columns:

Column 1 PKTABLE_CAT (VARCHAR(128))

This is always a zero-length string.

Column 2 PKTABLE_SCHEM (VARCHAR(128))

This is always a zero-length string.

Column 3 PKTABLE_NAME (VARCHAR(128) not NULL)

Name of the table containing the primary key.

Column 4 PKCOLUMN_NAME (VARCHAR(128) not NULL)

Primary key column name.

Column 5 FKTABLE_CAT (VARCHAR(128))

This is always a zero-length string.

Column 6 FKTABLE_SCHEM (VARCHAR(128))

This is always a zero-length string.

Column 7 FKTABLE_NAME (VARCHAR(128) not NULL)

Name of the table containing the foreign key.

Column 8 FKCOLUMN_NAME (VARCHAR(128) not NULL)

Foreign key column name.

Column 9 ORDINAL_POSITION (SMALLINT not NULL)

Ordinal position of the column in the key, starting at 1.

Column 10 UPDATE_RULE (SMALLINT)

This is always a zero.

Column 11 DELETE_RULE (SMALLINT)

This is always a zero.

Column 12 FK_NAME (VARCHAR(128))

This is always a zero-length string.

Column 13 PK_NAME (VARCHAR(128))

This is always a zero-length string.

Column 14 DEFERRABILITY (SMALLINT)

This is always a zero.

The column names used by DB2 CLI follow the X/Open CLI CAE specification style. The column types, contents, and order are identical to those defined for the SQLForeignKeys() result set in ODBC.

Return codes

• SQL_SUCCESS
• SQL_SUCCESS_WITH_INFO
• SQL_ERROR
• SQL_INVALID_HANDLE

Diagnostics

Table 59. SQLForeign SQLSTATEs

Restrictions

None.

Related reference

• Key to DB2 CLI function descriptions
• DB2 CLI function summary
• SQLPrimaryKeys--Get primary key columns of a table