Returns primary and foreign key information for the specified table or tables. Foreign keys must be declared using the ANSI integrity constraint mechanism.
sp_fkeys pktable_name [, pktable_owner] [, pktable_qualifier] [, fktable_name] [, fktable_owner] [, fktable_qualifier]
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. Wildcard-character search patterns, 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.
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.
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 6-11 shows the 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:
|
DELETE_RULE |
smallint |
Action to be applied to the foreign key when the SQL operation is delete:
|
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 |
