sp_foreignkey

Syntax

Parameters

tabname – is the name of the table or view that contains
the foreign key to be defined.

pktabname – is the name of the table or view that has the primary
key to which the foreign key applies. The primary key must already
be defined.

col1 – is the name of the first column that makes up the
foreign key. The foreign key must have at least one column and can
have a maximum of eight columns.

Examples

Example 1 – The primary key of the publishers table
is the pub_id column. The titles table
also contains a pub_id column, which
is a foreign key of publishers:

sp_foreignkey titles, publishers, pub_id

Example 2 – The
primary key of the parts table has been defined
with sp_primarykey as the partnumber and subpartnumber columns.
The orders table contains the columns part and subpart,
which make up a foreign key of parts:

sp_foreignkey orders, parts, part, subpart

Usage

There are additional considerations when using sp_foreignkey:

sp_foreignkey adds the key to the syskeys table. Keys make
explicit a logical relationship that is implicit in your database design.

sp_foreignkey does not
enforce referential integrity constraints; use the foreign
key clause of the create table or alter
table command to enforce a foreign key relationship.

The number and order of columns that make up the
foreign key must be the same as the number and order of columns
that make up the primary key. The datatypes (and lengths) of the
primary and foreign keys must agree, but the null types need not
agree.

The installation process runs sp_foreignkey on
the appropriate columns of the system tables.

To display a report on the keys that have been defined,
execute sp_helpkey.