Foreign Key in SQL Server

Foreign key in SQL Server is used to establish the relationship between two tables. In this tutorial, we will learn what is Foreign Key ?. How to create a Foreign Key constraint. How to add it to an existing table. Learn how to Drop the it etc.

What is Foreign Key

A foreign key in SQL Server is a column or group of columns in a table that corresponds to or references a primary key or a Unique key in another table.

Consider the following Customer & Invoice tables

We save the information about the customer in the Customer table, with customerID as the Primary key.

The Invoices of the customer are stored in a separate Invoices table with InvoiceID uniquely identifying each Invoice. We add the CustomerID in the Invoice table to link each Invoice to the Customer.

Since the CustomerID in the Invoice table links each Invoice to a Customer, we call CustomerID as Foreign Key in Invoice Table.

In this relationship the customer table is the Parent Table (or Principal table). The Invoice table, which depends on the customer table is child table (or dependent table)

The invoice table must not contain any invalid customer data. For Example, if we run this query against the above tables it will succeed although it contains the invalid CustomerID. ( CustomerID 5 does not exist in Customer Table)

This is where the Foreign Key constraint steps in. Using Foreign key we declare the relationship between two tables and SQL Server enforces that relationship

Creating a Foreign Key

The following is the basic Syntax for creating a Foreign Key inline. Using inline syntax you can only create Foreign Key on a single fields and also you cannot name it

The following query creates a Foreign Key Constraint on CustomerID column of Invoice Table. It refers to the CustomerID field of the Customer Table.

You can also define the Foreign key on table level. It starts with the Keyword FOREIGN KEY followed by child column on which you want to apply foreign key constraint.

Naming the Foreign key Constraint

The following query how you assign can name to the foreign key constraint. The name is needed when you want to drop the foreign key. Always follow a proper naming convention to name foreign key. You can use the convention FK_<childTableName>_<ParentTableName>_<childCol1>

Creating Composite Foreign Key

The following script shows how to create a composite Foreign Key.

Finding the Foreign Key Name

You can use the following query to find out the FK of a particualr table.

Source

Dropping Foreign Key

First find out the name of the Foreign key using the query from the previous section. Use the Alter table query to drop the Foreign key.

Adding Foreign Key to Existing Table

You can add Foreign Key using the Alter Table Syntax.

You can also use the following query to creating a Foreign Key without naming it. But beware SQL Server will not warn you if you run the query multiple times. It just create the duplicate Foreign Keys

Rules & Limitations

There are few important rules & Limitations that you must know when creating a Foreign key.

The Data type of Both Fields must match

The Data type of both parent & child tables must match. The following query fails becuase data type of CustomerID in the invoice table is varchar which is different from Customer table.

The columns names do not have to match. Only data types must match.

Foreign Key on UNIQUE Columns

Foreign keys can not only refer Primary keys but also Unique columns. In the example below we define CustomerID as UNIQUE column instead of Primary Key. You can still create a Foreign key constraint on CustomerID of invoice table.

Foreign key columns can be NULL

You can create a Foreign key constraint on Nullable columns. It is unlike Primary key which does not allow NOT NULL columns.

Does not create any index

SQL Server does not create any index on Foreign key columns. You need to manually create an index on a foreign key table if you uses these columns SQL joins

Foreign key can refer to the same Table

The Employee table below has a ManagerID field, which refer to Manager’s EmployeeID. Here Employee table is both parent & child table.

The following query creates FK on ManagerID which refers to the EmployeeID

Infact SQL Server allows a FK column to refer to itself. In the following example column TableAID refers to itself.

Always name your Foreign Keys

You should always name your Foreign Keys using a naming convention.

The query below creates a Foreign Key CustomerID on invoice table. Run this query several times and each time SQL server creates a new Foreign key. It does not throw any errors.

But if you the same name to create a constraint, SQL Server will throw an error.

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top