What is a foreign key?

A Foreign Key (FK) in a SQL table’s field refers to a look-up (reference) field in another table. A simple example:

dbo.tblCustomers has a field name “StateID”. One would probably make this field a Foreign Key (FK) so that it would only take entries from a specific table’s specific field (e.g. dbo.tblLIBStates field name “StateID”). FK ensures correctness of data (e.g naming conventions, etc) in the primary table.

ALTER TABLE [dbo].[tblCustomers]  WITH CHECK ADD  CONSTRAINT
[FK_tblCustomers_tblLIBStates_StateID] FOREIGN KEY([StateID])
REFERENCES [dbo].[tblLIBStates] ([StateID])

A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data.

For completeness:

A foreign key is a reference from a value or set of values in one table, to a value or set of values in a 'target' or 'referenced' table, which can be a different table or the same table as the 'primary' or 'parent' table.

The target value set must be NON-NULLABLE, and enforced unique through either a PRIMARY KEY constraint, a UNIQUE constraint or a UNIQUE INDEX.

Foreign keys can be enabled or disabled, although disabling foreign keys can sometimes cause the query optimiser to make incorrect assumptions about the presence of data in the target table in older versions of SQL Server.

Foreign keys also have referential actions which are invokes when a row in a target table that is referenced by a row in the primary table is deleted, or any of the values in the set of values are updated. These actions are:

• NONE - An error is produced
• CASCADE - The operation 'cascades' meaning that the same action is taken in the primary table
• SET NULL (2005+) - The referencing values in the primary table are set to NULL
• SET DEFAULT (2005+) - The referencing values in the primary table are set to their default values provides through DEFAULT constraints

Foreign keys can also be marked as 'NOT FOR REPLICATION' which means that the constraint is not enforced when replication agents perform operations on the data.

The MSDN reference to the table_constraint clause of ALTER TABLE can be found here.