1 Version 4.0 -- 1/15/89 sp_foreignkey
______________________________________________________________________
NAME: sp_foreignkey
FUNCTION:
Defines a foreign key on a table or view in the current database.
SYNTAX:
sp_foreignkey tabname, pktabname, col1 [, col2, col3, ..., col8]
EXAMPLES:
1) sp_foreignkey titles, publishers, pub_id
The primary key of the publishers table is the pub_id column.
sp_foreignkey Version 4.0 -- 1/15/89 2
______________________________________________________________________
The titles table also contains a pub_id column, which is a
foreign key of publishers.
2) sp_foreignkey orders, parts, part, subpart
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.
PARAMETERS:
tabname - is the name of the table or view that contains the
foreign key.
pktabname - is the name of the table or view that has the pri-
mary key to which the foreign key applies.
col1 - is the name of the first column that makes up the foreign
key. The foreign key must consist of at least one column,
3 Version 4.0 -- 1/15/89 sp_foreignkey
______________________________________________________________________
and can have a maximum of eight columns.
COMMENTS:
o Executing sp_foreignkey adds the key to the syskeys table.
o Keys are created in order to make explicit a logical relation-
ship that is implicit in your database design.
o To display a report on the keys that have been defined, execute
sp_helpkey.
o You must be the owner of the table or view in order to define
its foreign key.
o The primary key of pktabname table or view must already be
defined.
o 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
sp_foreignkey Version 4.0 -- 1/15/89 4
______________________________________________________________________
up the primary key. The datatypes (and the lengths) of the
primary and foreign keys must agree, but the nulltypes need not
agree.
o When you define a foreign key, a common key is automatically
defined between the pktabname and tabname tables or views, and
added to syskeys.
o SQL Server installation scripts installmaster and installmodel
execute the sp_foreignkey procedure on all the system tables.
MESSAGES:
The table or view specified with the tabname parameter doesn't
exist in the current database.
The table or view specified with the pktabname parameter doesn't
exist in the current database or doesn't have a primary key
defined.
You are not the owner of the table or view.
5 Version 4.0 -- 1/15/89 sp_foreignkey
______________________________________________________________________
The table or view specified with the tabname parameter does not
have a column of the specified name.
The number of columns in the foreign key of tabname must be the
same as the number of columns in the primary key of pktname.
The datatypes of the columns of the foreign key of tabname and
the primary key of pktabname must be the same.
The foreign key has been defined and added to syskeys. A common
key has also been added to syskeys.
PERMISSIONS:
Execute permission to tabname owner.
TABLES USED:
syscolumns, syskeys, sysobjects
SEE ALSO:
sp_commonkey, sp_dropkey, sp_helpkey, sp_helpjoins,
sp_foreignkey Version 4.0 -- 1/15/89 6
______________________________________________________________________
sp_primarykey, CREATE TRIGGER