sp_foreignkey

Description

Defines a foreign key on a table or view in the current database.

Syntax

sp_foreignkey tabname, pktabname, col1 [, col2] ... 
	[, col8] 

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

Permissions

You must be the owner of the table or view to execute sp_foreignkey. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Commands alter table, create table, create trigger

System procedures sp_commonkey, sp_dropkey, sp_helpjoins, sp_helpkey, sp_primarykey