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')