`SQLForeignKeys()` - Get a list of foreign key columns

SQLForeignKeys() 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 that is generated by a query.

ODBC specifications for `SQLForeignKeys()`

Table 1. `SQLForeignKeys()` specifications
ODBC specification level	In X/Open CLI CAE specification?	In ISO CLI specification?
1.0	No	No

Syntax

SQLRETURN   SQLForeignKeys  (SQLHSTMT           hstmt,
                             SQLCHAR      FAR  *szPkCatalogName,
                             SQLSMALLINT       cbPkCatalogName,
                             SQLCHAR      FAR  *szPkSchemaName,
                             SQLSMALLINT       cbPkSchemaName,
                             SQLCHAR      FAR  *szPkTableName,
                             SQLSMALLINT       cbPkTableName,
                             SQLCHAR      FAR  *szFkCatalogName,
                             SQLSMALLINT       cbFkCatalogName,
                             SQLCHAR      FAR  *szFkSchemaName,
                             SQLSMALLINT       cbFkSchemaName,
                             SQLCHAR      FAR  *szFkTableName,
                             SQLSMALLINT       cbFkTableName);

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. `SQLForeignKeys()` arguments
Data type	Argument	Use	Description
SQLHSTMT	hstmt	input	Specifies the statement handle on which to return results.
SQLCHAR *	szPkCatalogName	input	Specifies the catalog qualifier of the primary key table. This must be a null pointer or a zero length string.
SQLSMALLINT	cbPkCatalogName	input	Specifies the length, in bytes, of the szPkCatalogName argument. This must be set to 0.
SQLCHAR *	szPkSchemaName	input	Specifies the schema qualifier of the primary key table.
SQLSMALLINT	cbPkSchemaName	input	Specifies the length, in bytes, of the szPkSchemaName argument.
SQLCHAR *	szPkTableName	input	Specifies the name of the table that contains the primary key.
SQLSMALLINT	cbPkTableName	input	Specifies the length, in bytes, of the szPkTableName argument.
SQLCHAR *	szFkCatalogName	input	Specifies the catalog qualifier of the table that contains the foreign key. This must be a null pointer or a zero length string.
SQLSMALLINT	cbFkCatalogName	input	Specifies the length, in bytes, of the szFkCatalogName argument. This must be set to 0.
SQLCHAR *	szFkSchemaName	input	Specifies the schema qualifier of the table that contains the foreign key.
SQLSMALLINT	cbFkSchemaName	input	Specifies the length, in bytes, of the szFkSchemaName argument.
SQLCHAR *	szFkTableName	input	Specifies the name of the table that contains the foreign key.
SQLSMALLINT	cbFkTableName	input	Specifies the length, in bytes, of the szFkTableName argument.

Usage

If the szPkTableName argument contains a table name and the szFkTableName argument 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 the szFkTableName argument contains a table name and the szPkTableName argument is an empty string, SQLForeignKeys() returns a result set that contains all of the foreign keys in the table that you specify in the szFkTableName argument and the all the primary keys (on other tables) to which they refer.

If both of the szPkTableName argument and the szFkTableName argument contain table names, SQLForeignKeys() returns foreign keys that refer to the primary key of the table that you specify in the szPkTableName argument from the table that you specify in the szFkTableName argument. All foreign keys that this type of SQLForeignKeys() call returns refer to a single primary key.

If you do not specify a schema qualifier argument that is associated with a table name, Db2 ODBC uses the schema name that is currently in effect for the current connection.

The following table lists each column in the result set that SQLForeignKeys() currently returns.

Table 3. Columns returned by `SQLForeignKeys()`
Column number	Column name	Data type	Description
1	PKTABLE_CAT	VARCHAR(128)	This is always NULL.
2	PKTABLE_SCHEM	VARCHAR(128)	Contains the name of the schema to which the table in PKTABLE_NAME belongs.
3	PKTABLE_NAME	VARCHAR(128) NOT NULL	Contains the name of the table on which the primary key is defined.
4	PKCOLUMN_NAME	VARCHAR(128) NOT NULL	Contains the name of the column on which the primary key is defined.
5	FKTABLE_CAT	VARCHAR(128)	This is always NULL.
6	FKTABLE_SCHEM	VARCHAR(128)	Contains the name of the schema to which the table in FKTABLE_NAME belongs.
7	FKTABLE_NAME	VARCHAR(128) NOT NULL	Contains the name of the table that on which the foreign key is defined.
8	FKCOLUMN_NAME	VARCHAR(128) NOT NULL	Contains the name of the column on which the foreign key is defined.
9	KEY_SEQ	SMALLINT NOT NULL	Contains the ordinal position of the column in the key. The first position is 1.
10	UPDATE_RULE	SMALLINT	Identifies the action that is applied to the foreign key when the SQL operation is UPDATE. IBM® Db2 database management systems always return one of the following values: SQL_RESTRICT SQL_NO_ACTION Both of these values indicate that an update is rejected if it removes a primary key row that a foreign key references, or adds a value in a foreign key that is not present in the primary key. You might encounter the following UPDATE_RULE values when connected to non-IBM relational database management systems: SQL_CASCADE SQL_SET_NULL
11	DELETE_RULE	SMALLINT	Identifies the action that is applied to the foreign key when the SQL operation is DELETE. The following values indicate the action that is applied: SQL_CASCADE: when a primary key value is deleted, that value in related foreign keys is also deleted. SQL_NO_ACTION: the delete is rejected if it removes values from a primary key that a foreign key references. SQL_RESTRICT: the delete is rejected if it removes values from a primary key that a foreign key references. SQL_SET_DEFAULT: when a primary key value is deleted, that value is replaced with a default value in related foreign keys. SQL_SET_NULL: when a primary key value is deleted, that value is replaced with a null value in related foreign keys.
12	FK_NAME	VARCHAR(128)	Contains the name of the foreign key. This column contains a null value if it is not applicable to the data source.
13	PK_NAME	VARCHAR(128)	Contains the name of the primary key. This column contains a null value if it is not applicable to the data source.
14	DEFERRABILITY	SMALLINT	Db2 ODBC always returns a value of NULL. Other database management systems support the following values: SQL_INITIALLY_DEFERRED SQL_INITIALLY_IMMEDIATE SQL_NOT_DEFERRABLE

If you request foreign keys that are associated with a primary key, the returned rows in the result set are sorted by the values that the following columns contain:

FKTABLE_CAT
FKTABLE_SCHEM
FKTABLE_NAME
KEY_SEQ

If you request the primary keys that are associated with a foreign key, the returned rows in the result set are sorted by the values that the following columns contain:

PKTABLE_CAT
PKTABLE_SCHEM
PKTABLE_NAME
KEY_SEQ

The column names used by Db2 ODBC 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.

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

Db2 ODBC applications that issue SQLForeignKeys() against a Db2 for z/OS® server should expect the result set columns listed in Table 3.

For consistency with ANSI/ISO SQL standard of 1992 limits, the VARCHAR columns of the result set are declared with a maximum length attribute of 128 bytes. Because Db2 names are smaller than 128 characters, you can always use a 128-character (plus the nul-terminator) output buffer to handle table names. Call SQLGetInfo() with each of the following attributes to determine the actual amount of space that you need to allocate when you connect to another database management system:

SQL_MAX_CATALOG_NAME_LEN to determine the length that the PKTABLE_CAT and FKTABLE_CAT columns support
SQL_MAX_SCHEMA_NAME_LEN to determine the length that the PKTABLE_SCHEM and FKTABLE_SCHEM columns support
SQL_MAX_TABLE_NAME_LEN to determine the length that the PKTABLE_NAME and FKTABLE_NAME columns support
SQL_MAX_COLUMN_NAME_LEN to determine the length that the PKCOLUMN_NAME and FKCOLUMN_NAME columns support

Return codes

After you call SQLForeignKeys(), it returns one of the following values:

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 4. `SQLForeignKeys()` SQLSTATEs
SQLSTATE	Description	Explanation
08S01	Communication link failure.	The communication link between the application and data source fails before the function completes.
24000	Invalid cursor state.	A cursor is open on the statement handle.
HY001	Memory allocation failure.	Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY009	Invalid use of a null pointer.	The arguments szPkTableName and szFkTableName are both null pointers.
HY010	Function sequence error.	The function is called during a data-at-execute operation. (That is, the function is called during a procedure that uses the `SQLParamData()` or `SQLPutData()` functions.)
HY090	Invalid string or buffer length.	This SQLSTATE is returned for one or more of the following reasons: The value of one of the name length arguments is less than 0 and not equal SQL_NTS. The length of the table or owner name is greater than the maximum length that is supported by the server.
HYC00	Driver not capable.	Db2 ODBC does not support "catalog" as a qualifier for table name.
HY014	No more handles.	Db2 ODBC is not able to allocate a handle due to low internal resources.

Example

The following example shows an application that uses SQLForeignKeys() to retrieve foreign key information about a table.

Figure 1. An application that retrieves foreign key information about a table

/******************************************************************/
 /*  Invoke SQLForeignKeys against PARENT Table. Find all          */
 /*  tables that contain foreign keys on PARENT.                   */
 /******************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlca.h>
#include "cli.h"
#include "sqlcli1.h"
#include "sqlcli1.h"
int main( )
{
   SQLHENV         hEnv    = SQL_NULL_HENV;
   SQLHDBC         hDbc    = SQL_NULL_HDBC;
   SQLHSTMT        hStmt   = SQL_NULL_HSTMT;
   SQLRETURN       rc      = SQL_SUCCESS;
   SQLINTEGER      RETCODE = 0;
   char            pTable [200];
   char            *pDSN = "STLEC1";
   SQLSMALLINT     update_rule;
   SQLSMALLINT     delete_rule;
   SQLINTEGER      update_rule_ind;
   SQLINTEGER      delete_rule_ind;
   char            update [25];
   char            delet  [25];   
   typedef struct varchar    // define VARCHAR type
   {
     SQLSMALLINT  length;
     SQLCHAR      name [128];
     SQLINTEGER   ind;
   } VARCHAR;
   VARCHAR pktable_schem;
   VARCHAR pktable_name;
   VARCHAR pkcolumn_name;
   VARCHAR fktable_schem;
   VARCHAR fktable_name;
   VARCHAR fkcolumn_name;
   (void) printf ("**** Entering CLIP02.\n\n");
  /*****************************************************************/
  /* Allocate environment handle                                   */
  /*****************************************************************/
   RETCODE = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
   if (RETCODE != SQL_SUCCESS)
     goto dberror;
  /*****************************************************************/
  /* Allocate connection handle to DSN                             */
  /*****************************************************************/
   RETCODE = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hDbc);
   if( RETCODE != SQL_SUCCESS )      // Could not get a connect handle
     goto dberror;
  /*****************************************************************/
  /* CONNECT TO data source (STLEC1)                               */
  /*****************************************************************/
   RETCODE = SQLConnect(hDbc,        // Connect handle
                        (SQLCHAR *) pDSN, // DSN
                        SQL_NTS,     // DSN is nul-terminated
                        NULL,        // Null UID
                        0   ,
                        NULL,        // Null Auth string
                        0);
   if( RETCODE != SQL_SUCCESS )      // Connect failed
     goto dberror;
  /*****************************************************************/
  /* Allocate statement handle                                     */
  /*****************************************************************/
  rc = SQLAllocHandle( SQL_HANDLE_STMT, hDbc, &hStmt);
  if (rc != SQL_SUCCESS)
    goto exit;
  /*****************************************************************/
  /* Invoke SQLForeignKeys against PARENT Table, specifying NULL   */
  /* for table with foreign key.                                   */
  /*****************************************************************/
  rc = SQLForeignKeys (hStmt,
                       NULL,
                       0,
                       (SQLCHAR *) "ADMF001",
                       SQL_NTS,
                       (SQLCHAR *) "PARENT",
                       SQL_NTS,
                       NULL,
                       0,
                       NULL,
                       SQL_NTS,
                       NULL,
                       SQL_NTS);
  if (rc != SQL_SUCCESS)
  {
    (void) printf ("**** SQLForeignKeys Failed.\n");
    goto dberror;
  }
  /*****************************************************************/
  /* Bind following columns of answer set:                         */
  /*                                                               */
  /*   2) pktable_schem                                            */
  /*   3) pktable_name                                             */
  /*   4) pkcolumn_name                                            */
  /*   6) fktable_schem                                            */
  /*   7) fktable_name                                             */
  /*   8) fkcolumn_name                                            */
  /*  10) update_rule                                              */
  /*  11) delete_rule                                              */
  /*                                                               */
  /*****************************************************************/
  rc = SQLBindCol (hStmt,           // bind pktable_schem
                   2,
                   SQL_C_CHAR,
                   (SQLPOINTER) pktable_schem.name,
                   128,
                   &pktable_schem.ind);
  rc = SQLBindCol (hStmt,           // bind pktable_name
                   3,
                   SQL_C_CHAR,
                   (SQLPOINTER) pktable_name.name,
                   128,
                   &pktable_name.ind);
  rc = SQLBindCol (hStmt,           // bind pkcolumn_name
                   4,
                   SQL_C_CHAR,
                   (SQLPOINTER) pkcolumn_name.name,
                   128,
                   &pkcolumn_name.ind);
  rc = SQLBindCol (hStmt,           // bind fktable_schem
                   6,
                   SQL_C_CHAR,
                   (SQLPOINTER) fktable_schem.name,
                   128,
                   &fktable_schem.ind);
  rc = SQLBindCol (hStmt,           // bind fktable_name
                   7,
                   SQL_C_CHAR,
                   (SQLPOINTER) fktable_name.name,
                   128,
                   &fktable_name.ind);
  rc = SQLBindCol (hStmt,           // bind fkcolumn_name
                   8,
                   SQL_C_CHAR,
                   (SQLPOINTER) fkcolumn_name.name,
                   128,
                   &fkcolumn_name.ind);
  rc = SQLBindCol (hStmt,           // bind update_rule
                   10,
                   SQL_C_SHORT,
                   (SQLPOINTER) &update_rule;
                   0,
                   &update_rule_ind);
 rc = SQLBindCol (hStmt,           // bind delete_rule
                   11,
                   SQL_C_SHORT,
                   (SQLPOINTER) &delete_rule,
                   0,
                   &delete_rule_ind);
  /*****************************************************************/
  /* Retrieve all tables with foreign keys defined on PARENT       */
  /*****************************************************************/
  while ((rc = SQLFetch (hStmt)) == SQL_SUCCESS)
  {
    (void) printf ("**** Primary Table Schema is %s. Primary Table Name is %s.\n",
                   pktable_schem.name, pktable_name.name);
    (void) printf ("**** Primary Table Key Column is %s.\n",
                   pkcolumn_name.name);
    (void) printf ("**** Foreign Table Schema is %s. Foreign Table Name is %s.\n",
                   fktable_schem.name, fktable_name.name);
    (void) printf ("**** Foreign Table Key Column is %s.\n",
                   fkcolumn_name.name);
    if (update_rule == SQL_RESTRICT)     // isolate update rule
      strcpy (update, "RESTRICT");
    else
    if (update_rule == SQL_CASCADE)
      strcpy (update, "CASCADE");
    else
      strcpy (update, "SET NULL");
    if (delete_rule == SQL_RESTRICT)     // isolate delete rule
      strcpy (delet, "RESTRICT");
    else
    if (delete_rule == SQL_CASCADE)
      strcpy (delet, "CASCADE");
    else
    if (delete_rule == SQL_NO_ACTION)
      strcpy (delet, "NO ACTION");
    else
      strcpy (delet, "SET NULL");
    (void) printf ("**** Update Rule is %s. Delete Rule is %s.\n",
                   update, delet);
  }
  /*****************************************************************/
  /* Deallocate statement handle                                   */
  /*****************************************************************/
  rc = SQLFreeHandle (SQL_HANDLE_STMT, hStmt);
  /*****************************************************************/
  /* DISCONNECT from data source                                   */
  /*****************************************************************/
   RETCODE = SQLDisconnect(hDbc);
   if (RETCODE != SQL_SUCCESS)
     goto dberror;
  /*****************************************************************/
  /* Deallocate connection handle                                  */
  /*****************************************************************/
   RETCODE = SQLFreeHandle (SQL_HANDLE_DBC, hDbc);
   if (RETCODE != SQL_SUCCESS)
     goto dberror;
  /*****************************************************************/
  /* Free environment handle                                       */
  /*****************************************************************/
   RETCODE = SQLFreeHandle (SQL_HANDLE_ENV, hEnv);
   if (RETCODE == SQL_SUCCESS)
     goto exit;
   dberror:
   RETCODE=12;
   exit:
   (void) printf ("**** Exiting  CLIP02.\n\n");
   return RETCODE;
}

SQLForeignKeys() - Get a list of foreign key columns

ODBC specifications for SQLForeignKeys()