SQLForeignKeys

SQLForeignKeys

SQLForeignkeys

• A list of foreign keys of a specified table (columns of a specified table referring to the primary keys of other tables)
• A list of foreign keys of other tables referring to the primary keys of a specified table

SQLForeignKeysW() as a Unicode string supports same execution as SQLForeignKeys().

Syntax

SQLRETURN  SQLForeignKeys (
    SQLHSTMT        stmt,   
    SQLCHAR *       pkcName,    
    SQLSMALLINT     pkcNaneLength,      
    SQLCHAR *       pksName,        
    SQLSMALLINT     pksNameLength,      
    SQLCHAR *       pktName, 
    SQLSMALLINT     pktNameLength,      
    SQLCHAR *       fkcName,
    SQLINTEGER      fkcNaneLength,      
    SQLCHAR *       fksName,
    SQLSMALLINT     fksNameLength,
    SQLCHAR *       fktName,
    SQLSMALLINT     fktNameLength);

Arguments

Data Type	Argument	In/Output	Description
SQLHSTMT	stmt	Input	Statement handle
SQLCHAR*	pkcName	Input	Primary key table catalog name
SQLSMALLINT	pksNameLength	Input	The length, in bytes, of *pksName
SQLCHAR *	pktName	Input	Primary key table
SQLSMALLINT	pktNameLength	Input	The length, in bytes, of *pktName
SQLCHAR *	fkcName	Input	Foreign key table catalog name
SQLSMALLINT	fkcNameLength	Input	The length, in bytes, of *ksName

Return Values

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_INVALID_HANDLE
SQL_ERROR

Description

If *pktName contains a table name, SQLForeignKeys()returns a result set containing the primary key of the specified table and all of the foreign keys that refer to it. The list of foreign keys in other tables does not include foreign keys that point to unique constraints in the specified table.

If *fktName has a table name, SQLForeignKeys() will returns a result set containing all of the foreign keys in the specified table that point to primary keys in others tables, and the primary keys in the other tables to which they refer. The list of foreign keys in the specified table does not contain foreign keys that refer to unique constraints in other tables.

If both *pktName and *fktName have table names, SQLForeignKeys will return the foreign keys of the table specified by *fktName. For *fktName, refer to the primary keys of the table specified in *pktName.

SQLForeignKeys() returns the result in the standard result set form sorted by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_Name, and KEY_SEQ in case the foreign keys related to the primary keys are requested. If the primary keys related to the foreign keys are requested, the result in the standard result set sorted by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_Name, and KEY_SEQ will be returned. The following table lists the strings of the result sets.

String Name	String No.	Data Type	Description
PKTABLE_CAT	1	VARCHAR	Always NULL return
PKTABLE_SCHEM	2	VARCHAR	Foreign key table schema name NULL if not applicable to the database.
PKTABLE_NAME	3	VARCHAR (NOT NULL)	Primary key table name
PKCOLUMN_NAME	4	VARCHAR (NOT NULL)	Primary key column name. As for the unnamed column, Altibase CLI driver makes the empty character string return.
FKTABLE_CAT	5	VARCHAR	Always NULL Return
FKTABLE_SCHEM	6	VARCHAR	Primary key table schema name NULL if not applicable to the database
FKTABLE_NAME	7	VARCHAR (NOT NULL)	Foreign key table name
FKCOLUMN_NAME	8	VARCHAR (NOT NULL)	Foreign key column name. As for the unnamed column, Altibase CLI driver makes the empty character string return.
KEY_SEQ	9	SMALLINT (NOT NULL)	Column number sequence (starting with 1)
UPDATE_RULE	10	SMALLINT	Application of SQL_NO_ACTION to the foreign key upon UPDATE operation
DELETE_RULE	11	SMALLINT	Application of SQL_NO_ACTION to the foreign key upon DELETE operation
FK_NAME	12	VARCHAR	Foreign key name. NULL not proper for the database
PK_NAME	13	VARCHAR	Primary key name. NULL not proper for the database
DEFERRABILITY	14	SMALLINT	SQL_INITIALLY_IMMEDIATE

[Table 2‑1] Columns Returned by SQLForeignKeys()

Diagnosis

SQLSTATE	Description	Comments
08S01	Communication channel error	Communication channel failure before the function processing between the Altibase CLI driver and the database is completed
HY009	HY009 Invalid Arguments used (null pointer)	Argument pktName and fktName are NULL pointer.
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.

Related Functions

SQLBindCol
SQLFetch
SQLPrimaryKeys
SQLStatistics

Example

< Refer to: $ALTIBASE_HOME/sample/SQLCLI/demo_meta9.cpp >

if (SQLForeignKeys(stmt,
                       NULL, 0,
                       "SYS", SQL_NTS,
                       "ORDERS", SQL_NTS,
                       NULL, 0,
                       NULL, 0,
                       NULL, 0) != SQL_SUCCESS)
    {
        execute_err(dbc, stmt, "SQLForeignKeys");
        SQLFreeStmt(stmt, SQL_DROP);
        return SQL_ERROR;
    }

    SQLBindCol(stmt, 2, SQL_C_CHAR, szPKSchema, NAME_LEN, &cbPKSchema);
    SQLBindCol(stmt, 3, SQL_C_CHAR, szPKTableName, NAME_LEN,&cbPKTableName);
    SQLBindCol(stmt, 4, SQL_C_CHAR, szPKColumnName, NAME_LEN, &cbPKColumnName);
    SQLBindCol(stmt, 6, SQL_C_CHAR, szFKSchema, NAME_LEN, &cbFKSchema);
    SQLBindCol(stmt, 7, SQL_C_CHAR, szFKTableName, NAME_LEN,&cbFKTableName);
    SQLBindCol(stmt, 8, SQL_C_CHAR, szFKColumnName, NAME_LEN, &cbFKColumnName);
    SQLBindCol(stmt, 9, SQL_C_SSHORT, &KeySeq, 0, &cbKeySeq);