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.

pktable_owner: is the owner of the table containing the primary 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.

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

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

fktable_owner: is the owner of the table containing the foreign 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.

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.

Table 4-11 shows the result set for sp_fkeys:

**Table 4-11: 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