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:
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:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE Customers (ID INT PRIMARY KEY , CustomerName VARCHAR(50), CustomerAge SMALLINT, CustomerCountry VARCHAR(50) ) INSERT INTO [dbo].[Customers]([ID],[CustomerName],[CustomerAge],[CustomerCountry]) VALUES (1,N'Salvador',23,N'Brazil ') INSERT INTO [dbo].[Customers]([ID],[CustomerName],[CustomerAge],[CustomerCountry]) VALUES (2,N'Lawrence',60,N'China ') INSERT INTO [dbo].[Customers]([ID],[CustomerName],[CustomerAge],[CustomerCountry]) VALUES (3,N'Ernest',38,N'India') |
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.
|
1 2 3 4 5 6 |
CREATE TABLE CustomerOrders (ID INT PRIMARY KEY , OrderDate DATETIME, CustomerID INT FOREIGN KEY REFERENCES Customers(ID), Amout BIGINT, ) |
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.
|
1 2 3 4 5 |
INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amout]) VALUES (1,CAST('29-Apr-2019' AS DATETIME),1,968.45) INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amout]) VALUES (2,CAST('10-May-2019' AS DATETIME),2,898.36) INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amout]) VALUES (3,CAST('21-Oct-2019' AS DATETIME),3,47.01) SELECT * FROM [CustomerOrders] |
The previous insert batch statement does not return any error because all CustomerID values match ID column values in the Customers tables.
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”:
|
1 2 |
INSERT INTO [dbo].[CustomerOrders]([ID],[OrderDate],[CustomerID],[Amout]) VALUES (4,CAST('29-Apr-2019' AS DATETIME),4,968.45) |
If we want to give a name explicitly to the foreign key constraint, we can use the following query:
|
1 2 3 4 5 6 7 |
CREATE TABLE CustomerOrders (ID INT PRIMARY KEY , OrderDate DATETIME, CustomerID INT , Amout BIGINT, CONSTRAINT FK_CustomerCheck FOREIGN KEY (CustomerID) REFERENCES customers(ID) ) |
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:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE CustomerOrders (ID INT PRIMARY KEY, OrderDate DATETIME, CustomerID INT, Amout BIGINT ) ALTER TABLE CustomerOrders ADD CONSTRAINT FK_CustomerCheck FOREIGN KEY (CustomerID) REFERENCES Customers(ID) |
Tip: The following query will help to find out more details about foreign key relations in the executed database.
|
1 2 3 4 5 6 7 |
SELECT OBJECT_NAME(FK.referenced_object_id) AS 'Referenced Table', OBJECT_NAME(FK.parent_object_id) AS 'Referring Table', FK.name AS 'Foreign Key', COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) AS 'Referenced Column', COL_NAME(FK.parent_object_id, FKC.parent_column_id) AS 'Referring Column' FROM sys.foreign_keys AS FK INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.OBJECT_ID |
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.
|
1 2 3 4 5 6 7 |
CREATE TABLE CustomerSales (ID INT PRIMARY KEY, SaleDate DATETIME, CustomerID INT FOREIGN KEY REFERENCES Customers(ID) ON UPDATE CASCADE ON DELETE NO ACTION, SaleAmount MONEY, ) |
Now, we will populate some data into this table:
|
1 2 3 |
INSERT INTO [dbo].[CustomerSales]([ID],[SaleDate],[CustomerID],[SaleAmount]) VALUES (1,CAST('05-Mar-2019' AS DATETIME),1,726.24) INSERT INTO [dbo].[CustomerSales]([ID],[SaleDate],[CustomerID],[SaleAmount]) VALUES (2,CAST('02-Nov-2019' AS DATETIME),2,817.33) INSERT INTO [dbo].[CustomerSales]([ID],[SaleDate],[CustomerID],[SaleAmount]) VALUES (3,CAST('13-Nov-2019' AS DATETIME),3,768.02) |
When we try to delete one row from the Customers (parent table), we will experience an error.
|
1 |
DELETE FROM Customers WHERE Id=2 |
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).
|
1 2 3 4 5 6 |
DELETE CustomerOrders SELECT * FROM Customers SELECT * FROM CustomerSales UPDATE Customers SET ID=9999 WHERE ID=1 SELECT * FROM Customers SELECT * FROM CustomerSales |
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.
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
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023