SQLForeignKeys - Get the 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 with the same functions that are used to retrieve a result that is generated by a query.

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLForeignKeysW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support for DB2 CLI.

Syntax

SQLRETURN   SQLForeignKeys   (SQLHSTMT          StatementHandle,
                              SQLCHAR           *PKCatalogName,
                              SQLSMALLINT       NameLength1,
                              SQLCHAR           *PKSchemaName,
                              SQLSMALLINT       NameLength2,
                              SQLCHAR           *PKTableName,
                              SQLSMALLINT       NameLength3,
                              SQLCHAR           *FKCatalogName,
                              SQLSMALLINT       NameLength4,
                              SQLCHAR           *FKSchemaName,
                              SQLSMALLINT       NameLength5,
                              SQLCHAR           *FKTableName,
                              SQLSMALLINT       NameLength6);

Function arguments

Table 1. SQLForeignKeys arguments
Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle.
SQLCHAR *	PKCatalogName	Input	Catalog qualifier of the primary key table. This must be a NULL pointer or a zero length string.
SQLSMALLINT	NameLength1	Input	Length of PKCatalogName. This must be set to 0.
SQLCHAR *	PKSchemaName	Input	Schema qualifier of the primary key table.
SQLSMALLINT	NameLength2	Input	Length of PKSchemaName.
SQLCHAR *	PKTableName	Input	Name of the table name containing the primary key.
SQLSMALLINT	NameLength3	Input	Length of PKTableName.
SQLCHAR *	FKCatalogName	Input	Catalog qualifier of the table containing the foreign key. This must be a NULL pointer or a zero length string.
SQLSMALLINT	NameLength4	Input	Length of FKCatalogName. This must be set to 0.
SQLCHAR *	FKSchemaName	Input	Schema qualifier of the table containing the foreign key.
SQLSMALLINT	NameLength5	Input	Length of FKSchemaName.
SQLCHAR *	FKTableName	Input	Name of the table containing the foreign key.
SQLSMALLINT	NameLength6	Input	Length of FKTableName.

Usage

If PKTableName contains a table name, and FKTableName is an empty string, SQLForeignKeys() 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, SQLForeignKeys() 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, 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 schema qualifier argument that is associated with a table name is not specified, then for the schema name the default is the one currently in effect for the current connection.

Table 2 lists the columns of the result set 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.

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

Table 2. Columns returned by SQLForeignKeys
Column number/name	Data type	Description
1 PKTABLE_CAT	VARCHAR(128)	The current server.
2 PKTABLE_SCHEM	VARCHAR(128)	The name of the schema containing PKTABLE_NAME.
3 PKTABLE_NAME	VARCHAR(128) not NULL	Name of the table containing the primary key.
4 PKCOLUMN_NAME	VARCHAR(128) not NULL	Primary key column name.
5 FKTABLE_CAT	VARCHAR(128)	The current server.
6 FKTABLE_SCHEM	VARCHAR(128)	The name of the schema containing FKTABLE_NAME.
7 FKTABLE_NAME	VARCHAR(128) not NULL	The name of the table containing the Foreign key.
8 FKCOLUMN_NAME	VARCHAR(128) not NULL	Foreign key column name.
9 KEY_SEQ	SMALLINT not NULL	The ordinal position of the column in the key, starting at 1.
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 following UPDATE_RULE values when connected to non-IBM RDBMSs: SQL_CASCADE SQL_SET_NULL
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
12 FK_NAME	VARCHAR(128)	Foreign key identifier. NULL if not applicable to the data source.
13 PK_NAME	VARCHAR(128)	Primary key identifier. NULL if not applicable to the data source.
14 DEFERRABILITY	SMALLINT	One of: SQL_INITIALLY_DEFERRED SQL_INITIALLY_IMMEDIATE SQL_NOT_DEFERRABLE
Note: The column names used by DB2 for i 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 3. SQLForeignKeys SQLSTATEs
SQLSTATE	Description	Explanation
24000	Cursor state that is not valid	A cursor is already opened on the statement handle.
40003 08S01	Communication link failure	The communication link between the application and data source fails before the function is completed.
HY001	Memory allocation failure	DB2 for i CLI is unable to allocate memory required to support the processing or completion of the function.
HY009	Argument value that is not valid	The arguments PKTableName and FKTableName were both NULL pointers.
HY010	Function sequence error
HY014	No more handles	DB2 for i CLI is unable to allocate a handle due to internal resources.
HY021	Internal descriptor that is not valid	The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.
HY090	String or buffer length that is not valid	The value of one of the name length arguments is less than 0, but not equal to SQL_NTS. The length of the table or owner name is greater than the maximum length supported by the data source. Refer to SQLGetInfo - Get general information.
HYC00	Driver not capable	DB2 for i CLI does not support catalog as a qualifier for table name.
HYT00	Timeout expired

Restrictions

None.

Example

Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.

/* From CLI sample browser.c */
/* ... */
SQLRETURN list_foreign_keys( SQLHANDLE hstmt,
                             SQLCHAR * schema,
                             SQLCHAR * tablename
                           ) {
 
/* ... */
    rc = SQLForeignKeys(hstmt, NULL, 0,
                        schema, SQL_NTS, tablename, SQL_NTS,
                        NULL, 0,
                        NULL, SQL_NTS, NULL, SQL_NTS);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) pktable_schem.s, 129,
                    &pktable_schem.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 3, SQL_C_CHAR, (SQLPOINTER) pktable_name.s, 129,
                    &pktable_name.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) pkcolumn_name.s, 129,
                    &pkcolumn_name.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 6, SQL_C_CHAR, (SQLPOINTER) fktable_schem.s, 129,
                    &fktable_schem.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 7, SQL_C_CHAR, (SQLPOINTER) fktable_name.s, 129,
                    &fktable_name.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 8, SQL_C_CHAR, (SQLPOINTER) fkcolumn_name.s, 129,
                    &fkcolumn_name.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 10, SQL_C_SHORT, (SQLPOINTER) &update_rule,
                    0, &update_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 11, SQL_C_SHORT, (SQLPOINTER) &delete_rule,
                    0, &delete_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 12, SQL_C_CHAR, (SQLPOINTER) fkey_name.s, 129,
                    &fkey_name.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 13, SQL_C_CHAR, (SQLPOINTER) pkey_name.s, 129,
                    &pkey_name.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    printf("Primary Key and Foreign Keys for %s.%s\n", schema, tablename);
    /* Fetch each row, and display */
    while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) {
        printf("  %s  %s.%s.%s\n      Update Rule ",
               pkcolumn_name.s, fktable_schem.s, fktable_name.s, fkcolumn_name.s);
        if (update_rule == SQL_RESTRICT) {
            printf("RESTRICT ");  /* always for IBM DBMSs */
        } else {
            if (update_rule == SQL_CASCADE) {
               printf("CASCADE ");  /* non-IBM only */
            } else {
               printf("SET NULL ");
            }
        }
        printf(", Delete Rule: ");
        if (delete_rule== SQL_RESTRICT) {
            printf("RESTRICT ");  /* always for IBM DBMSs */
        } else {
            if (delete_rule == SQL_CASCADE) {
               printf("CASCADE ");  /* non-IBM only */
            } else {
               if (delete_rule == SQL_NO_ACTION) {
                  printf("NO ACTION ");  /* non-IBM only */
               } else {
                    printf("SET NULL ");
                 }
            }
        }
        printf("\n");
        if (pkey_name.ind > 0 ) {
            printf("     Primary Key Name: %s\n", pkey_name.s);
        }
        if (fkey_name.ind > 0 ) {
            printf("     Foreign Key Name: %s\n", fkey_name.s);
        }
    }