In database terms, a foreign key is a column that is linked to another table‘s primary key field in a relationship between two tables.

A foreign key is a type of constraint, and so if you want to create a foreign key in SQL Server, you’ll need to create a foreign key constraint.

This article demonstrates how to create a foreign key constraint in SQL Server, using Transact-SQL.

Example

The easiest way to demonstrate this is with an example. In this example, we use T-SQL to create a foreign key constraint using the ALTER TABLE statement:

USE Music;
ALTER TABLE Albums
  ADD CONSTRAINT FK_Albums_Artists FOREIGN KEY (ArtistId)     
      REFERENCES dbo.Artists (ArtistId)
      ON DELETE CASCADE    
      ON UPDATE CASCADE
  ;
GO

This creates a relationship between two tables (the Albums table and the Artists) table). We do this by creating a foreign key constraint on the Albums table. We specify that the Albums.ArtistId column is a foreign key to the Artists.ArtistId column.

This obviously assumes that the two tables exist. If they didn’t, we’d get an error.

We also use GO which is not actually part of T-SQL. It’s recognised by various utilities to signal the end of a batch of T-SQL statements.

Check the Result

You can check the result by running the following script:

USE Music;
SELECT 
    name,
    type_desc,
    delete_referential_action_desc,
    update_referential_action_desc
FROM sys.foreign_keys;
GO

This lists out the foreign keys in the Music database. Change the name of the database to suit.

If your database has too many foreign keys, you can always narrow it down with a WHERE clause to the specific foreign key that you’re interested in. You can also use the wildcard character (*) if you need all columns to be returned.