sp_foreignkey

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

Syntax

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

Parameters

Examples

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.

  • You cannot use a Java datatype with sp_foreignkey.

See also

alter table, create table, create trigger in Reference Manual: Commands.

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:

InformationValues
Event

38

Audit option

exec_procedure

Command or access audited

Execution of a procedure

Information in extrainfo
  • 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

Related reference
sp_commonkey
sp_dropkey
sp_helpjoins
sp_helpkey
sp_primarykey