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.
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
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.
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:
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:
|
11 DELETE_RULE | SMALLINT | Action to be applied to the foreign
key when the SQL operation is DELETE:
|
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:
|
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
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
/* 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);
}
}