A foreign key is a field that is linked to another table‘s primary key field in a relationship between two tables.

In relational database management systems, a relationship defines a relationship between two or more tables. That is, the data in one table is related to the data in the other. One table contains the primary key and the other table contains the foreign key.

When we establish a relationship between the tables, we link the foreign key with the primary key. From that point on, any value in the foreign key field should match a value from the primary key field in the other table.

In the above example, the CityId field in the Customer table is a foreign key. It is linked to the CityId field in the City table which is a primary key. For any given record, the value of Customer.CityId will match the value of City.CityId.

So foreign keys are an essential part of relational database systems. Without foreign keys, we wouldn’t be able to match data in one table with data from another. At least, we wouldn’t be able to match it with full confidence that the results are correct. This is because the value of the primary field  is a unique identifier.

Therefore, the value of the foreign key field is the unique identifier for the record in the other table. So we know that the foreign key value refers to only that single record.

Creating a Foreign Key

If we’re using a graphical user interface (GUI) when working with the database, then we may be able to use a wizard to walk through the process of creating a foreign key.

Otherwise, if we’re using SQL to create scripts for the database objects, we can define the foreign key as part of those scripts.

Here’s an example of creating a foreign key relationship in SQL:

CREATE TABLE Types( 
    TypeId INTEGER PRIMARY KEY, 
    Type
);

CREATE TABLE Pets( 
    PetId INTEGER PRIMARY KEY, 
    PetName,
    TypeId,
    FOREIGN KEY(TypeId) REFERENCES Types(TypeId)
);

The part that creates the foreign key is this:

FOREIGN KEY(TypeId) REFERENCES Types(TypeId)

The FOREIGN KEY(TypeId) part declares Pets.TypeId as the foreign key.

Once these two tables have been created, whenever we insert a row into the Pets table, we must make sure that the value we specify for the TypeId column is a value that’s already in the TypeId of the Types table. If we specify a value that’s not already in the parent table, we’ll get an error. This is the whole purpose of a foreign key constraint – it prevents users from entering data that would create an orphaned record. It helps us to maintain referential integrity.

Composite Foreign Keys

A composite foreign key is a foreign key consisting of multiple columns.

You can create a composite foreign key just as you would create a single foreign key, except that instead of specifying just one column, you provide the name of two or more columns, separated by a comma.

Like this:

CONSTRAINT FK_FKName FOREIGN KEY (FKColumn1, FKColumn2)
REFERENCES PrimaryKeyTable (PKColumn1, PKColumn2);

See How to Create a Composite Foreign Key in SQL Server for an example.