How to Create a Foreign Key in SQL Server (T-SQL Examples)

Posted on by Ian

In this article I demonstrate how to create a foreign key in SQL Server using Transact-SQL. I demonstrate how to create a foreign key at the time of creating the table (as opposed to updating an existing table).

A foreign key is a column that references another table’s primary key column. This creates a relationship between the tables.

Example 1 – Preparation

In this example I’ll create a test database wth one table. This table will contain the primary key that our foreign key will reference.

Create the database:

CREATE DATABASE FK_Test;

Now create the primary key table:

USE FK_Test;

CREATE TABLE Country
(
    CountryId int IDENTITY (1,1) NOT NULL PRIMARY KEY,
    CountryName nvarchar(60)
);

Example 2 – Create the Foreign Key

Now that we’ve got a table with a primary key, let’s create another table with a foreign key that references that primary key.

CREATE TABLE City
(
    CityId int IDENTITY (1,1) NOT NULL PRIMARY KEY,
    CountryId int NOT NULL REFERENCES Country(CountryId),
    CityName nvarchar(60)
);

This is the simplest way to create a foreign key. All we do is add the REFERENCES clause (along with the primary key table and column) to the column that will have the foreign key constraint.

To be clear, the part that defines the foreign key is this:

REFERENCES Country(CountryId)

This is included in the column definition and simply states that this column will reference the CountryId column in the Country table.

In this case, both the foreign key and the primary key that it references, share the same name (CountryId). However, this is not a requirement – your foreign key column can have a completely different name to the column that it references (although all columns participating in a foreign key relationship must be defined with the same length and scale).

This example causes SQL Server to automatically generate the name of the foreign key. That’s because I didn’t provide a name. Read on to see how you can create a name for your foreign key.

But first, let’s check the foreign key constraint we just created.

Example 3 – Check the Foreign Key Constraint

There are many ways to return a foreign key using T-SQL, and here’s one of them:

EXEC sp_fkeys @fktable_name = City;

Result (using vertical output):

PKTABLE_QUALIFIER | FK_Test
PKTABLE_OWNER     | dbo
PKTABLE_NAME      | Country
PKCOLUMN_NAME     | CountryId
FKTABLE_QUALIFIER | FK_Test
FKTABLE_OWNER     | dbo
FKTABLE_NAME      | City
FKCOLUMN_NAME     | CountryId
KEY_SEQ           | 1
UPDATE_RULE       | 1
DELETE_RULE       | 1
FK_NAME           | FK__City__CountryId__38996AB5
PK_NAME           | PK__Country__10D1609FC8BFA7F2
DEFERRABILITY     | 7

The sp_fkeys system stored procedure returns information about our foreign key, its associated primary key, and other relevant details. You simply pass in the name of the foreign key table or the primary key table, and it will return the relevant info.

In this example, I pass the name of the foreign key table – City. In the results, we can look at the FK_NAME column to see that this table has a foreign key constraint called FK__City__CountryId__38996AB5. This is the one we just created.

So now that we’ve created the foreign key, whenever we attempt to insert or update a value in the City.CountryId column, the foreign key constraint will only allow it if the same value already exists in the Country.CountryId column. This ensures that referential integrity is maintained within the database.

Example 4 – More Options

It’s possible add more options to your foreign key definition.

For example you can provide a name for the foreign key. You can also specify what should happen to values in this column if the corresponding value in the primary key is updated or deleted.

Here, I create both tables again, but this time I explicitly specify these options (I do the same for the primary keys):

CREATE TABLE Country
(
    CountryId int IDENTITY (1,1) NOT NULL,
      CONSTRAINT PK_Country_CountryId PRIMARY KEY CLUSTERED (CountryId),
    CountryName nvarchar(60)
);

CREATE TABLE City
(
    CityId int IDENTITY (1,1) NOT NULL,
      CONSTRAINT PK_City_CityId PRIMARY KEY CLUSTERED (CityId),
    CountryId int NOT NULL,
      CONSTRAINT FK_City_Country FOREIGN KEY (CountryID)
        REFERENCES Country (CountryID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CityName nvarchar(60)
);

In this case, the foreign key definition starts with CONSTRAINT, followed by the name of the foreign key, followed by FOREIGN KEY, followed by the column that the foreign key constraint will be applied to (inserted in parentheses).

We then see the same REFERENCES clause that we saw in the previous example.

The ON DELETE CASCADE and ON UPDATE CASCADE clauses are used to ensure that changes made to Country table are automatically propagated to the City table. For example, if a row is deleted from the parent (primary key) table, any corresponding rows are deleted from the referencing (foreign key) table.

The default value for ON DELETE and ON UPDATE is NO ACTION. In this case the Database Engine raises an error, and the update or delete action on the row in the parent table is rolled back.

You can also use SET NULL to set the foreign key column to NULL (requires the foreign key column to be nullable), or SET DEFAULT to set it to its default value (requires the foreign key column to have a default definition. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column).

In this example I also took the opportunity to name the primary keys. You can see that the primary key syntax is similar to the foreign key syntax, but without the REFERENCES clause (and with an added CLUSTERED argument, which is the default for primary keys).

Now check the foreign key:

EXEC sp_fkeys @fktable_name = City;

Result:

PKTABLE_QUALIFIER | FK_Test
PKTABLE_OWNER     | dbo
PKTABLE_NAME      | Country
PKCOLUMN_NAME     | CountryId
FKTABLE_QUALIFIER | FK_Test
FKTABLE_OWNER     | dbo
FKTABLE_NAME      | City
FKCOLUMN_NAME     | CountryId
KEY_SEQ           | 1
UPDATE_RULE       | 0
DELETE_RULE       | 0
FK_NAME           | FK_City_Country
PK_NAME           | PK_Country_CountryId
DEFERRABILITY     | 7

We can see the foreign key’s name is now FK_City_Country and the primary key constraint of the column that it references is called PK_Country_CountryId.

Example 5 – Foreign Key on Multiple Columns

You can also create a foreign key on multiple columns that references a multicolumn primary key. Multicolumn primary keys are also known as composite primary keys. To create a composite foreign key, simply separate the columns with a comma when defining the key.

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 a more detailed example.

Is the Primary Key Really Necessary?

A primary key isn’t absolutely necessary for foreign keys, as you could use a unique constraint or unique index. Specifically, the Microsoft documentation states this:

FOREIGN KEY constraints can reference only columns in PRIMARY KEY or UNIQUE constraints in the referenced table or in a UNIQUE INDEX on the referenced table.

So while it’s usually good practice to have primary keys on all tables, your foreign keys aren’t obliged to reference them.