Foreign Key in SQL Server

by

Foreign Keys are a key component of SQL Server. They help link two tables while maintaining the relationship, so data can be maintained without mixing everywhere.

This is quite important, and the general operation of a foreign key in SQL Server is briefly described in the SQL Server tutorial. This is followed by explaining how to create foreign keys in SQL, supported by some real-world examples of SQL syntax.

By the end of this article, you will fully understand how to implement foreign keys into your databases to ensure coherent and dependable data management.

What is a Foreign Key in SQL Server

A foreign key is a Key that helps link one table with another in SQL Server. It can be any single field or a combination of fields concerning the Candidate Key of some other table. The table with the foreign key is termed the Child Table, and the table holding the Candidate Key is termed the Parent Table.

Foreign keys are supposed to enforce integrity references among tables in the database; that is, they prevent operations that cause broken links.

The foreign keys are essential because:

  • Data Integrity: Using foreign keys helps maintain proper table relations. For instance, in a table that holds orders, an order that is present cannot be made if there is no customer in the Customer table.
  • Cascading Actions: If any updates or deletes are cascaded, these can readily be done to help propagate the changes against the related tables.
  • Query Optimization: Foreign keys optimize the join operation between tables by SQL Server.

Creating Foreign Key in SQL Server

The syntax for creating a foreign key using the ALTER TABLE and ADD clause is below.

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column);

Where,

  • child_table: Table that the foreign key refers to.
  • child_column: Column in the child table that contains the foreign key.
  • fk_name: The name of the foreign key constraint.
  • parent_table: Table holding a primary key.
  • parent_column: The foreign key will reference the column in the parent table.

For example, let’s create two tables in an ecommerce database. The table names are Customers and Orders.

Query to create a Customers table.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    ContactName NVARCHAR(100),
    Country NVARCHAR(50)
);

Use the query below to create an Orders table.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    CustomerID INT,
    Amount DECIMAL(10, 2)
);

For instance, the relationship between an order and a customer does not hold unless the Customer ID stored in the Orders table is a foreign key to the CustomerID stored in the Customers table.

Adding Foreign Key

We have created two tables. Let’s use the command below to establish a relationship by adding a foreign key.

ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerOrder
FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID);
Creating Foreign Key in SQL Server

You can see from the above output that you have successfully created a table with a foreign key constraint.

Let’s insert some data into each table one by one.

INSERT INTO Customers (CustomerID, CustomerName, ContactName, Country)
VALUES (1, 'ACME Corporation', 'Mindy', 'USA');

INSERT INTO Orders (OrderID, OrderDate, CustomerID, Amount)
VALUES (101, '2024-05-31', 1, 1500.00);

When you run the above query, it inserts the record in both the Customers and Orders tables.

But I want to share that they will not be deleted whenever you delete the records from the parent table because they are related to the records in the child table.

So, how can you achieve that? Deleting the parent table records should not affect the related records in the child table.

You need to apply the CASCADING action on the foreign keys to do so. For example, you may establish the foreign key so that when entries in the parent table are deleted or updated, entries in the child table are also deleted or updated automatically.

To apply the cascading, use the code below.

ALTER TABLE Orders
ADD CONSTRAINT FK_CustomerOrder
FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID)
ON DELETE CASCADE
ON UPDATE CASCADE;

Where,

  • ON DELETE CASCADE: Deletion of any record from the Customers table deletes all of its associated records lying under the Orders table.
  • ON UPDATE CASCADE: Update of a different CustomerID, if and when absorbed into the Customers table, updates the corresponding CustomerID lying under the Orders table.

Conclusion

Foreign keys in a relational database maintain consistency and integrity between different tables. They also maintain the data structure because they show the relation of one table to the other in a structured and ordered way, which is paramount to database work.

One of the prevalent issues pertinent for a reliable database administrator and developer in creating a solid and efficient database system is creating and regulating foreign keys in SQL Server.

You may like to read: