SQLForeignKeys function (CLI) - Get the list of foreign key columns

Returns information about foreign keys for the specified table. The information is returned in an SQL result set which can be processed using the same functions that are used to retrieve a result generated by a query.

Specification:

CLI 2.1
ODBC 1.0

The SQLForeignKeys() function returns information about foreign keys for the specified table. The information is returned in an SQL result set which you can process by using the same functions that you use to retrieve a result that is generated by a query.

Unicode equivalent: You can also use this function with the Unicode character set. The corresponding Unicode function is SQLForeignKeysW(). See Unicode functions (CLI) for information about ANSI to Unicode function mappings.

Syntax

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

Function arguments

Table 1. SQLForeignKeys arguments
Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	The statement handle.
SQLCHAR *	PKCatalogName	Input	The catalog qualifier of the 3-part primary key table name. If the target DBMS does not support 3-part naming, and PKCatalogName is not a null pointer and does not point to a zero-length string, then an empty result set and SQL_SUCCESS is returned. Otherwise, this is a valid filter for DBMSs that support 3-part naming.
SQLSMALLINT	NameLength1	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store PKCatalogName, or SQL_NTS if PKCatalogName is null-terminated.
SQLCHAR *	PKSchemaName	Input	The schema qualifier of the primary key table.
SQLSMALLINT	NameLength2	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store PKSchemaName, or SQL_NTS if PKSchemaName is null-terminated.
SQLCHAR *	PKTableName	Input	The name of the table name that contains the primary key.
SQLSMALLINT	NameLength3	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store PKTableName, or SQL_NTS if PKTableName is null-terminated.
SQLCHAR *	FKCatalogName	Input	The catalog qualifier of the 3-part foreign key table name. If the target DBMS does not support 3-part naming, and FKCatalogName is not a null pointer and does not point to a zero-length string, then an empty result set and SQL_SUCCESS is returned. Otherwise, this is a valid filter for DBMSs that support 3-part naming.
SQLSMALLINT	NameLength4	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store FKCatalogName, or SQL_NTS if FKCatalogName is null-terminated.
SQLCHAR *	FKSchemaName	Input	The schema qualifier of the table that contains the foreign key.
SQLSMALLINT	NameLength5	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store FKSchemaName, or SQL_NTS if FKSchemaName is null-terminated.
SQLCHAR *	FKTableName	Input	The name of the table that contains the foreign key.
SQLSMALLINT	NameLength6	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store FKTableName, or SQL_NTS if FKTableName is null-terminated.

Usage

If PKTableName contains a table name, and FKTableName is an empty string, the SQLForeignKeys() function returns a result set that contains 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, the SQLForeignKeys() function returns a result set that contains 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, the SQLForeignKeys() function returns the foreign keys in the table that are specified in FKTableName, which refer to the primary key of the table that is specified in PKTableName. There should be one key at the most.

If the schema qualifier argument that is associated with a table name is not specified, the schema name defaults to the table name that is currently in effect for the current connection.

Columns Returned by SQLForeignKeys lists the columns of the result set that is generated by the SQLForeignKeys() call. If the foreign keys that are associated with a primary key are requested, the result set is ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and ORDINAL_POSITION. If the primary keys that are associated with a foreign key are requested, the result set is ordered by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, and ORDINAL_POSITION.

Call SQLGetInfo() with the SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_SCHEMA_NAME_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN to determine the actual lengths of the associated TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns that are supported by the connected DBMS.

You can specify *ALL as a value in the SchemaName to resolve unqualified stored procedure calls or to find libraries in catalog API calls. CLI searches on all existing schemas in the connected database. You are not required to specify *ALL, as this behavior is the default in CLI. Alternatively, you can set the SchemaFilter IBM® Data Server Driver configuration keyword or the Schema List CLI/ODBC configuration keyword to *ALL.

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

Columns that are returned by SQLForeignKeys

Column 1 PKTABLE_CAT (VARCHAR(128))

Name of the catalog for PKTABLE_NAME. The value is NULL if this table does not have catalogs.

Column 2 PKTABLE_SCHEM (VARCHAR(128))

Name of the schema containing PKTABLE_NAME.

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))

Name of the catalog for FKTABLE_NAME. The value is NULL if this table does not have catalogs.

Column 6 FKTABLE_SCHEM (VARCHAR(128))

Name of the schema containing FKTABLE_NAME.

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 KEY_SEQ (SMALLINT not NULL)

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

Column 10 UPDATE_RULE (SMALLINT)

Action to be applied to the foreign key when the SQL operation is UPDATE:

SQL_RESTRICT
SQL_NO_ACTION

The update rule for IBM Db2® DBMSs is always either RESTRICT or SQL_NO_ACTION. However, ODBC applications might encounter the listed UPDATE_RULE values when connected to RDBMSs that are not provided by IBM:

SQL_CASCADE
SQL_SET_NULL

Column 11 DELETE_RULE (SMALLINT)

Action to be applied to the foreign key when the SQL operation is DELETE:

SQL_CASCADE
SQL_NO_ACTION
SQL_RESTRICT
SQL_SET_DEFAULT
SQL_SET_NULL

Column 12 FK_NAME (VARCHAR(128))

Foreign key identifier. NULL if not applicable to the data source.

Column 13 PK_NAME (VARCHAR(128))

Primary key identifier. NULL if not applicable to the data source.

Column 14 DEFERRABILITY (SMALLINT)

One of:

SQL_INITIALLY_DEFERRED
SQL_INITIALLY_IMMEDIATE
SQL_NOT_DEFERRABLE

Note: The column names that are used by 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_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 2. SQLForeignKeys SQLSTATEs
SQLSTATE	Description	Explanation
24000	Invalid cursor state.	A cursor is already opened on the statement handle.
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
HY001	Memory allocation failure.	Db2 CLI is unable to allocate memory required to support execution or completion of the function. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY009	Invalid argument value.	The arguments PKTableName and FKTableName were both NULL pointers.
HY010	Function sequence error.	The function was called while in a data-at-execute (`SQLParamData()`, `SQLPutData()`) operation. The function was called while in a BEGIN COMPOUND and END COMPOUND SQL operation. An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called. The function was called before a statement was prepared on the statement handle.
HY014	No more handles.	Db2 CLI was unable to allocate a handle due to resource limitations.
HY090	Invalid string or buffer length.	The value of one of the name length arguments was less than 0, but not equal to SQL_NTS. The length of the table or owner name is greater than the maximum length that is supported by the server.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. You can set the timeout period by using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetStmtAttr()`. Note: This SQLSTATE applies only to .Net applications.

Restrictions

None.

Example

  /* get the list of foreign key columns */
  cliRC = SQLForeignKeys(hstmt,
                         NULL,
                         0,
                         tbSchema,
                         SQL_NTS,
                         tbName,
                         SQL_NTS,
                         NULL,
                         0,
                         NULL,
                         SQL_NTS,
                         NULL,
                         SQL_NTS);