Chapter 11: Accessing Catalog Information with CSPs

sp_fkeys

Description

Returns primary and foreign key information for the specified table or tables. Foreign keys must be declared using the ANSI integrity constraint mechanism.

Syntax

sp_fkeys pktable_name [, pktable_owner]
 [, pktable_qualifier] [, fktable_name] 
 [, fktable_owner] [, fktable_qualifier]

Parameters

pktable_name: is the name of the table containing the primary key. Views, aliases, and wildcard-character search patterns are not supported. You must specify either this parameter or the fktable_name parameter, or both. Views and aliases are not supported.

pktable_owner: is the owner of the table containing the primary key. Views, aliases, and wildcard-character search patterns are not supported. If you do not specify this parameter, sp_fkeys looks first for a table owned by the current user and then for a table owned by the database owner.

pktable_qualifier: is ignored. Leave blank or set to NULL.

fktable_name: is the name of the table containing the foreign key. Wildcard-character search patterns are not supported. Views and aliases are not supported.

fktable_owner: is the owner of the table containing the foreign key. Wildcard-character search patterns are not supported. If you do not specify this parameter, sp_fkeys looks first for a table owned by the current user and then for a table owned by the database owner.

fktable_qualifier: is ignored. Leave blank or set to NULL.

Usage

This function corresponds to the ODBC function SQLForeignKeys.
Information is based on the SYSCOLUMNS, SYSFOREIGNKEYS, SYSINDEXES, SYSRELS, and SYSSYNONYMS system catalog tables.
For information about creating a foreign key, see the appropriate IBM DATABASE 2 SQL Reference manual.

Results

sp_fkeys returns a row for each column that is part of the foreign key or primary key in a primary key/foreign key relationship.

Results are ordered by the following columns:

PKTABLE_OWNER
PKTABLE_NAME
KEY_SEQ

The lengths for varchar columns shown in the result set tables are maximums; the actual lengths depend on the target database.

The following table shows the result set.

**Table 11-9: Result set for sp_fkeys**
Column	Datatype	Description
PKTABLE_QUALIFIER	varchar(128)	NULL.
PKTABLE_OWNER	varchar(128)	Primary key table owner.
PKTABLE_NAME	varchar(128) NOT NULL	Primary key table name.
PKCOLUMN_NAME	varchar(128) NOT NULL	Primary key column name.
FKTABLE_QUALIFIER	varchar(128)	NULL.
FKTABLE_OWNER	varchar(128)	Foreign key table owner.
FKTABLE_NAME	varchar(128) NOT NULL	Foreign key table name.
FKCOLUMN_NAME	varchar(128) NOT NULL	Foreign key column name.
KEY_SEQ	smallint NOT NULL	Column sequence number in key (starting with 1).
UPDATE_RULE	smallint	Action to be applied to the foreign key when the SQL operation is update: 0 means cascade. 1 means restrict. 2 means set null. NULL means not applicable to the target database.
DELETE_RULE	smallint	Action to be applied to the foreign key when the SQL operation is delete: 0 means cascade. 1 means restrict. 2 means set null. NULL means not applicable to the target database.
FK_NAME	varchar(128)	Foreign key identifier; NULL if not applicable to the target database.
PK_NAME	varchar(128)	Primary key identifier; NULL if not applicable to the target database.

View this book as PDF