Esat Erkec
Illustration of what the foreign key is

What is a foreign key in SQL Server

February 13, 2020 by

In this article, we will seek an answer to an important question – “What is a foreign key in SQL Server?”. At the same time, we will give some seconder answers to this question. In this way, we can understand the foreign key concept more clearly.

Introduction

A foreign key is a column or set of columns that allow us to establish a referential link between the data in two tables. This referential link helps to match the foreign key column data with the data of the referenced table data. The referenced table is called the parent table and the table that involves a foreign key is called the child table. In addition, if a foreign key references another column of the same table, this reference type is called a self-reference.

Until this part of the article, we have answered the “What is a foreign key in SQL” question briefly. Now, we will take some examples in order to understand the foreign key designing model and usage details.

Create a foreign key

Suppose that we have two tables and the first one is the Customers table which stores detailed information about the customers of an organization. The other one is CustomerOrders that stores the order details of the clients. According to the database design, the CustomerOrders table must not contain any invalid customer data. To overcome this issue, we need to create a foreign key between Customers and CustomerOrders columns. The following illustration shows the design of these two tables:

Illustration of what the foreign key is

This foreign key establishes referential integrity between Customers and CustomerOrders tables, thus, restricting the insertion of a new row when the CustomerId value of the inserted row does not match the ID column values of the Customers table.

For this example, the Customers table is the parent table and the CustomerOrders table is the child table.

  • Tip: What is a foreign key in SQL Server: It creates a link between parent and child table columns and foreign key references a primary key in the parent table.

At first, we will create a Customers table through the following query and we will populate some sample data:

The following query will create the CustomerOrders table and the CustomerId column will be foreign key and it references the ID column of the Customers table. SQL Server automatically gives a name to the foreign key that will be created.

When we want to insert a new row into the CustomerOrders table, the value of the CustomerID must match the values in the ID columns of the Customers table.

Result of the Customers table

The previous insert batch statement does not return any error because all CustomerID values match ID column values in the Customers tables.

Foreign key data matching illustration

The following query will return an error and the insert statement will be rolled back because the Customers table does not contain any row that has an ID value equal “4”:

Foreign key constraint conflict error

If we want to give a name explicitly to the foreign key constraint, we can use the following query:

  • Tip: What is a foreign key in SQL Server: It is a constraint that provides referential integrity between two tables.

After creating a table, we can add a foreign key to this table. We can use the following query in order to add a foreign key to the existing table:

Tip: The following query will help to find out more details about foreign key relations in the executed database.

Finding foreign key details with query in a database

Foreign key update and delete rules

As we mentioned, the main purpose of the foreign key is to provide the referential integrity between parent and child table. Sometimes, we may need to update or delete data from the parent table. In this case, we have to decide the behavior of the child table data because it is referenced to the parent table. In the SQL Server, we can specify delete and update rules for the foreign keys so we can determine the behavior of the child data when we want to update or delete some data from the parent table.

  • Tip: What is a foreign key in SQL Server: The primary purpose of the foreign key is to establish control upon the data that will be inserted into the table, which involves foreign key. The inserted data must match the referenced table.

These rules are:

Delete Rules:

  • No Action: It returns an error when we want to delete any row from the parent table and the deleted statement will be rolled back
  • Cascade: In this option, the deleted statement also deletes all associated rows from the child table
  • Set Null: In this option, the deleted statement deletes the parent table row and associated values will be updated with null values on the child table. The foreign key column must be nullable
  • Set Default: The delete statement deletes parent table row and associated values of the child table will be updated with the default value of the foreign key column. In order to work this rule, a default constraint should be specified for the foreign key column and this default value must match in the parent table

Update Rules:

  • No Action: It returns an error when we want to update any row from the parent table and the update statement will be rolled back
  • Cascade: In this option, the updated statement also updates all associated rows from the child table
  • Set Null: In this option, the updated statement updates the parent table data and the associated values of the child table are updated to a default value. The foreign key column must be nullable
  • Set Default: The update statement updates the parent table row and child table associated values will be updated with the default value of the foreign key column. In order to work this rule, a default constraint should be specified for the foreign key column and this default value must match in the parent table

In the following example, we will create a CustomerSales table and this table CustomerId column referencing ID column of the Customers table. The delete rule of the foreign key will be specified as No Action and the update rule will be specified as Set Null option.

Now, we will populate some data into this table:

When we try to delete one row from the Customers (parent table), we will experience an error.

What is the foreign key in SQL Server

As we can see, the foreign key rule prevents deleting the referenced column value. Now, we will update a row of the Customers (parent table value).

What is the foreign key in SQL Server

As we can see, updating the Customers (parent table) affects the CustomerSales (child table). Whereas, we did not do any changing on the CustomerSales table.

Conclusion

In this article, we tried to find an answer to “What is a foreign key in the SQL Server” question and we looked at the foreign key concept from different perspectives and gave various answers in the light of this information.

Esat Erkec
Latest posts by Esat Erkec (see all)
DDL, SQL commands, T-SQL

About Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert. Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn. View all posts by Esat Erkec

374 Views