16.10.3 SQLForeignKeys, SQLForeignKeysW : Hitachi Advanced Database Application Development Guide

(1) Function

This ODBC function returns column information for the following foreign keys as an SQL result set:

List of foreign keys in the specified table (columns in the specified table that refer to primary keys in other tables)
List of foreign keys in other tables that refer to the primary key in the specified table

(2) Format

For SQLForeignKeys

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

For SQLForeignKeysW

SQLRETURN SQLForeignKeysW
(
  SQLHSTMT        StatementHandle,    /* In */
  SQLWCHAR      * PKCatalogName,      /* In */
  SQLSMALLINT     NameLength1,        /* In */
  SQLWCHAR      * PKSchemaName,       /* In */
  SQLSMALLINT     NameLength2,        /* In */
  SQLWCHAR      * PKTableName,        /* In */
  SQLSMALLINT     NameLength3,        /* In */
  SQLWCHAR      * FKCatalogName,      /* In */
  SQLSMALLINT     NameLength4,        /* In */
  SQLWCHAR      * FKSchemaName,       /* In */
  SQLSMALLINT     NameLength5,        /* In */
  SQLWCHAR      * FKTableName,        /* In */
  SQLSMALLINT     NameLength6         /* In */
)

To Page Top

(3) Arguments

StatementHandle

Specifies a statement handle.

When you use this function, specify a value that was output by *OutputHandlePtr of SQLAllocHandle before this function is executed.

PKCatalogName

Specifies a primary key table catalog name. This argument is ignored, if specified. When you use this function, specify a null character string ("") or NULL.

NameLength1

Specifies the length of *PKCatalogName^# or SQL_NTS. This argument is ignored, if specified. When you use this function, specify 0.

PKSchemaName

Specifies a primary key table schema name. If NULL is specified, all schema names are processed.

NameLength2

Specifies the length of *PKSchemaName^# or SQL_NTS. If 0 is specified, all schema names are processed.

PKTableName

Specifies a primary key table name. For details about the value to be specified, see Table 16‒12: Combinations of PKTableName and FKTableName values and the result set that is returned based on the combination. If a null character string is specified, the number of rows in the result set will be zero.

NameLength3

Specifies the length of *PKTableName^# or SQL_NTS. If NULL is specified for PKTableName, this argument is ignored. If a non-null value is specified for PKTableName and 0 is specified for this argument, the result will be the same as when a null character string is specified for PKTableName.

FKCatalogName

Specifies a foreign key table catalog name. This argument is ignored, if specified. When you use this function, specify a null character string ("") or NULL.

NameLength4

Specifies the length of *FKCatalogName^# or SQL_NTS. This argument is ignored, if specified. When you use this function, specify 0.

FKSchemaName

Specifies a foreign key table schema name. If NULL is specified, all schema names are processed.

NameLength5

Specifies the length of *FKSchemaName^# or SQL_NTS. If 0 is specified, all schema names are processed.

FKTableName

Specifies a foreign key table name. For details about the value to be specified, see Table 16‒12: Combinations of PKTableName and FKTableName values and the result set that is returned based on the combination. If a null character string is specified, the number of rows in the result set will be zero.

NameLength6

Specifies the length of *FKTableName^# or SQL_NTS. If NULL is specified for PKTableName, this argument is ignored. If a value other than NULL is specified for PKTableName and 0 is specified for this argument, the result will be the same as when an empty string is specified for PKTableName.

#

The length must be in bytes for SQLForeignKeys and in characters for SQLForeignKeysW.

The following table shows the combinations of PKTableName and FKTableName values and the result set that is returned based on the combination.

Table 16‒12: Combinations of PKTableName and FKTableName values and the result set that is returned based on the combination
PKTableName	FKTableName	Result set that is returned
`NULL`	`NULL`	-- (error)
`Not NULL`	`NULL`	Returns information about the primary key of the table specified for `PKTableName` and the foreign keys of other tables that reference that primary key. The ODBC function does not return information about the foreign keys of other tables that reference keys only for the unique constraint in the table specified for `PKTableName`.
`NULL`	`Not NULL`	Returns information about the foreign key of the table specified for `FKTableName` and the primary keys of other tables that the foreign key references. The ODBC function does not return information about the foreign key of the table specified for `FKTableName` that references keys only for the unique constraint in other tables.
`Not NULL`	`Not NULL`	Returns information about the foreign key of the table specified for `FKTableName` and the primary key of the table that the foreign key references. This foreign key references only the primary key of the table specified for `PKTableName`.

To Page Top

(4) Return value

This ODBC function returns SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE.

A result set is created when SQLForeignKeys or SQLForeignKeysW is executed. The following table shows the format of the result set that is returned.

Table 16‒13: Format of the result set that is returned
Column No.	Type	Column name	Description
1	`Varchar`	`PKTABLE_CAT`	The null value is always returned.
2	`Varchar`	`PKTABLE_SCHEM`	Schema name of the primary key. If there is no schema name, a null character string is returned.
3	`Varchar`	`PKTABLE_NAME`	Table name of the primary key.
4	`Varchar`	`PKCOLUMN_NAME`	Column name of the primary key. If there is no column name, a null character string is returned.
5	`Varchar`	`FKTABLE_CAT`	The null value is always returned.
6	`Varchar`	`FKTABLE_SCHEM`	Schema name of the foreign key. If there is no schema name, a null character string is returned.
7	`Varchar`	`FKTABLE_NAME`	Table name of the foreign key.
8	`Varchar`	`FKCOLUMN_NAME`	Column name of the foreign key. If there is no column name, a null character string is returned.
9	`Smallint`	`KEY_SEQ`	Sequence number of the foreign key columns beginning at 1.
10	`Smallint`	`UPDATE_RULE`	Action to be applied to the foreign key when an SQL statement that performs update processing is requested. `SQL_NO_ACTION` If no primary key value corresponds to the foreign key value because the primary key value or the foreign key value was updated, the key value cannot be updated. However, if referential constraint check suppression (`DISABLE`) is specified in the referential constraint definition in the `CREATE` `TABLE` statement, the primary key value or the foreign key value can be updated.
11	`Smallint`	`DELETE_RULE`	Action to be applied to the foreign key when an SQL statement that performs deletion processing is requested. `SQL_NO_ACTION` If no primary key value corresponds to the foreign key value because rows were deleted from the table to be referenced, rows cannot be deleted. However, if referential constraint check suppression (`DISABLE`) is specified in the referential constraint definition in the `CREATE` `TABLE` statement, rows can be deleted.
12	`Varchar`	`FK_NAME`	Foreign key name.
13	`Varchar`	`PK_NAME`	Primary key name.
14	`Smallint`	`DEFERRABILITY`	Value indicating whether constraint checking on the foreign key is to be delayed. `SQL_NOT_DEFERRABLE` A constraint check is to be performed each time an SQL statement is executed. Nothing can be done to delay this constraint checking.

To Page Top

(5) SQLSTATE

This ODBC function returns one of the following SQLSTATE values:

SQLSTATE	Description	Remarks	Returned
`01000`	General warning	--	N
`08S01`	Communication link failure	--	N
`24000`	Invalid cursor status	The function was executed while a cursor was open.	Y
`40001`	Serialization failure	--	N
`40003`	Statement completion unknown	--	N
`5C002`	Character encoding conversion error	A character code that cannot be converted was detected.	Y
`HY000`	General error	--	N
`HY001`	Memory allocation error		Y
`HY008`	Operation cancelled		N
`HY009`	Invalid use of null pointer	`PKTableName` and `FKTableName` are both `NULL`.	Y
`HY010`	Function sequence error	--	Y
`HY013`	Memory management error	--	N
`HY090`	Invalid string or buffer length	One of the arguments that stores a name length exceeded the maximum length for the corresponding name.	Y
`HY117`	Connection suspended	--	N
`HYC00`	Optional feature not implemented		N
`HYT00`	Timeout expired		N
`HYT01`	Connection timeout expired		N
`IM001`	Driver does not support this function		N
`IM017`	Invalid asynchronous polling		N
`IM018`	Incomplete asynchronous execution		N

Legend:

Y: This SQLSTATE might be returned by the HADB ODBC driver.

N: This SQLSTATE is not returned by the HADB ODBC driver.

--: None

To Page Top

(6) Notes

The information about foreign keys that can be acquired depends on the privileges of the HADB user who executes this method. For details about the relationship between privileges and the information that can be acquired, see the topic Scope of information in dictionary tables and system tables that can be referenced by HADB users in the HADB Setup and Operation Guide.

To Page Top