In this article, we will explore the concept of foreign keys in SQL Server and understand their significance in database design and data integrity.

Introduction

A foreign key is a column or set of columns that establishes a referential link between the data in two tables. It allows us to maintain the integrity of the data by ensuring that the values in the foreign key column(s) match the values in the referenced table’s primary key column(s). The referenced table is known as the parent table, while the table containing the foreign key is referred to as the child table.

Foreign keys play a crucial role in maintaining data consistency and enforcing relationships between tables. They help prevent the insertion of invalid data into the child table by restricting the values in the foreign key column(s) to those present in the parent table’s primary key column(s).

Creating a Foreign Key

Let’s consider an example where we have two tables: Customers and CustomerOrders. The Customers table stores detailed information about the customers, while the CustomerOrders table stores the order details of the clients. To ensure that the CustomerOrders table only contains valid customer data, we need to create a foreign key between the Customers and CustomerOrders tables.

Here’s an example of how we can create the Customers table and populate it with some sample data:

CREATE TABLE Customers (
  ID INT PRIMARY KEY,
  CustomerName VARCHAR(50),
  CustomerAge SMALLINT,
  CustomerCountry VARCHAR(50)
)

INSERT INTO Customers (ID, CustomerName, CustomerAge, CustomerCountry)
VALUES (1, 'Salvador', 23, 'Brazil'),
       (2, 'Lawrence', 60, 'China'),
       (3, 'Ernest', 38, 'India')

Next, we can create the CustomerOrders table with a foreign key that references the ID column of the Customers table:

CREATE TABLE CustomerOrders (
  ID INT PRIMARY KEY,
  OrderDate DATETIME,
  CustomerID INT FOREIGN KEY REFERENCES Customers(ID),
  Amount BIGINT
)

Now, when we try to insert a new row into the CustomerOrders table, the value of the CustomerID must match the values in the ID column of the Customers table. This ensures that only valid customer IDs are inserted into the CustomerOrders table.

Foreign Key Constraints

By default, SQL Server automatically assigns a name to the foreign key constraint. However, if we want to give a specific name to the foreign key constraint, we can do so using the following syntax:

CREATE TABLE CustomerOrders (
  ID INT PRIMARY KEY,
  OrderDate DATETIME,
  CustomerID INT,
  Amount BIGINT,
  CONSTRAINT FK_CustomerCheck FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
)

We can also add a foreign key to an existing table using the ALTER TABLE statement:

ALTER TABLE CustomerOrders
ADD CONSTRAINT FK_CustomerCheck FOREIGN KEY (CustomerID) REFERENCES Customers(ID)

To view more details about the foreign key relationships in the database, we can use the following query:

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

When updating or deleting data from the parent table, we need to specify the behavior of the child table’s data, as it is referenced to the parent table. SQL Server allows us to define update and delete rules for foreign keys to determine the behavior of the child data.

The available delete rules are:

• No Action: Returns an error when attempting to delete a row from the parent table. The delete statement will be rolled back.
• Cascade: Deletes all associated rows from the child table when a row is deleted from the parent table.
• Set Null: Updates the associated values in the child table with null when a row is deleted from the parent table. The foreign key column must be nullable.
• Set Default: Updates the associated values in the child table with the default value of the foreign key column when a row is deleted from the parent table. A default constraint must be specified for the foreign key column, and the default value must match in the parent table.

The available update rules are:

• No Action: Returns an error when attempting to update a row in the parent table. The update statement will be rolled back.
• Cascade: Updates all associated rows in the child table when a row is updated in the parent table.
• Set Null: Updates the associated values in the child table to a default value when a row is updated in the parent table. The foreign key column must be nullable.
• Set Default: Updates the associated values in the child table with the default value of the foreign key column when a row is updated in the parent table. A default constraint must be specified for the foreign key column, and the default value must match in the parent table.

Here’s an example of creating a CustomerSales table with a foreign key that has a delete rule of No Action and an update rule of Set Null:

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
)

INSERT INTO CustomerSales (ID, SaleDate, CustomerID, SaleAmount)
VALUES (1, '05-Mar-2019', 1, 726.24),
       (2, '02-Nov-2019', 2, 817.33),
       (3, '13-Nov-2019', 3, 768.02)

When we try to delete a row from the Customers table, we will encounter an error due to the foreign key rule preventing the deletion of the referenced column value. Similarly, updating a row in the Customers table will affect the associated rows in the CustomerSales table, even if no changes were made directly to the CustomerSales table.

Conclusion

In this article, we have explored the concept of foreign keys in SQL Server and their importance in maintaining data integrity. We have learned how foreign keys establish relationships between tables and enforce referential integrity. Additionally, we have seen how to create foreign keys, specify their constraints, and define update and delete rules. Understanding foreign keys is essential for designing robust and reliable database systems.