About

A foreign key constrains a set of one or more columns in a table to the range of primary or unique key values in another. It ensures that those columns cannot hold a value or combination of values not present in the referenced table. This prevents records from being orphaned from a parent by having nonexistent parent references recorded.

A simple database schema with a foreign key constraint looks something like this example:

Create table Parent (
       ParentCode      varchar (10) not null primary key
)
go

Create table Child (
       ChildID         int not null primary key
      ,ParentCode      varchar (10) not null
)
go

alter table Child
  add constraint FK_Child_Parent
      foreign key (ParentCode)
      references Parent
go

If we set up a list of legal values for ParentCode and try to insert valid and invalid child records the system will accept valid records and reject ones that violate the foreign key constraint.

-- Legal values for Parent are 'foo' and 'bar'
insert Parent (ParentCode) values ('foo')
insert Parent (ParentCode) values ('bar')

-- This record has a legal value for ParentCode
insert Child (ChildID, ParentCode) values (1, 'foo')

-- This record has an illegal value for ParentCode and will
-- violate the foreign key constraint when we attempt to
-- insert it, raising an error.
insert Child (ChildID, ParentCode) values (2, 'xyz')