In SQL Server (and relational databases in general), a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.

This relationship is a cornerstone of relational database design, ensuring referential integrity between the related tables. When designing databases, it’s crucial to understand how changes to primary keys in parent tables can affect the foreign keys in child tables.

One of the various options available in SQL Server for handling such changes is the ON UPDATE SET NULL action.

This article explores the ON UPDATE SET NULL option in depth, providing detailed explanations, use cases, and SQL scripts to demonstrate its implementation.

What is a Foreign Key?

Just so we’re on the same page, let’s briefly look at what a foreign key actually is.

A foreign key is a constraint that enforces a link between the data in two tables. It is a field (or collection of fields) in one table that refers to the primary key in another table, thus creating a relationship between the two. This relationship helps maintain data integrity by ensuring that any value in the foreign key column corresponds to a valid record in the referenced table.

Understanding Referential Integrity

Referential integrity is a concept in relational databases that ensures that relationships between tables remain consistent. When a foreign key is used, SQL Server enforces this integrity by making sure that changes to the referenced primary key are handled correctly, depending on the action specified (NO ACTION, CASCADE, SET NULL, or SET DEFAULT).

Introduction to ON UPDATE Actions

When defining a foreign key in SQL Server, you can specify what should happen if the referenced key in the parent table is updated. The available actions include:

• NO ACTION: Prevents the update if it would cause a foreign key violation.
• CASCADE: Propagates the update to the foreign key in the child table.
• SET NULL: Sets the foreign key in the child table to NULL.
• SET DEFAULT: Sets the foreign key to its default value.

What is ON UPDATE SET NULL?

The ON UPDATE SET NULL option is used to handle cases where a change to the primary key in the parent table should result in the corresponding foreign key in the child table being set to NULL. This is useful in scenarios where the relationship between the records should be removed if the key changes, rather than updating to a new key or enforcing a constraint violation.

Practical Use Cases for ON UPDATE SET NULL

The ON UPDATE SET NULL option can be particularly useful in the following scenarios:

• Soft Deletion of Relationships: When you want to disassociate a record from a parent without deleting it.
• Historical Data Tracking: If the parent data changes and you want to preserve the history in the child table by nullifying the foreign key.
• Optional Relationships: In cases where a relationship is not mandatory, and the child record should remain even if the parent changes.

Implementing ON UPDATE SET NULL in SQL Server

To implement this option, we simply append the foreign key definition with ON UPDATE SET NULL.

For example, here’s the basic syntax we can use to create a parent table and child table:

CREATE TABLE ParentTable (
    ParentID INT PRIMARY KEY,
    Name NVARCHAR(50)
);

CREATE TABLE ChildTable (
    ChildID INT PRIMARY KEY,
    ParentID INT,
    ChildName NVARCHAR(50),
    FOREIGN KEY (ParentID) REFERENCES ParentTable(ParentID) 
    ON UPDATE SET NULL
);

So we can see the ON UPDATE SET NULL part on the last line of the child table’s definition. It’s the part that goes like this:

    FOREIGN KEY (ParentID) REFERENCES ParentTable(ParentID) 
    ON UPDATE SET NULL

The ON UPDATE SET NULL is actually attached to the foreign key definition, it’s only on a new line for readability. So that line could just as easily go like this:

FOREIGN KEY (ParentID) REFERENCES ParentTable(ParentID) ON UPDATE SET NULL

Of course, we can also name our foreign keys, in which case we’d use the following syntax:

CONSTRAINT fk_name FOREIGN KEY (ParentID) REFERENCES ParentTable(ParentID) ON UPDATE SET NULL

Where fk_name is the name of the foreign key.

Example Scenarios with SQL Scripts

Let’s look at an example for a better understanding. Here, we’ll create some tables with a referential relationship, we’ll populate those tables with data, and then we’ll make an update to a row in the parent table. This will demonstrate what happens to data in the child table when the parent table is updated.

Step 1: Create Parent and Child Tables

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(100)
);

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100),
    DepartmentID INT,
    CONSTRAINT fk_Employees_Department 
        FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
    ON UPDATE SET NULL
);

Step 2: Insert Data (and View It)

-- Populate Departments table
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(1, 'Human Resources'),
(2, 'Engineering'),
(3, 'Sales');

-- Populate Employees table
INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID) VALUES
(101, 'Alley Bridge', 1),
(102, 'Bob Smith', 2),
(103, 'Ash Sigley', 3);

-- Check the data
SELECT * FROM Departments;
SELECT * FROM Employees;

Result:

DepartmentID  DepartmentName 
------------  ---------------
1             Human Resources
2             Engineering    
3             Sales          

EmployeeID  EmployeeName  DepartmentID
----------  ------------  ------------
101         Alley Bridge  1           
102         Bob Smith     2           
103         Ash Sigley    3           

Step 3: Update the Parent Table

Now let’s go and update data in the parent table:

UPDATE Departments 
SET DepartmentID = 4 
WHERE DepartmentID = 3;

After this update, the DepartmentID in the Employees table where it was previously 3 will be set to NULL, effectively breaking the association between Ash Sigley and the Sales department.

Step 4: Verify the Result

SELECT * FROM Departments;
SELECT * FROM Employees;

Result:

DepartmentID  DepartmentName 
------------  ---------------
1             Human Resources
2             Engineering    
4             Sales          

EmployeeID  EmployeeName  DepartmentID
----------  ------------  ------------
101         Alley Bridge  1           
102         Bob Smith     2           
103         Ash Sigley    null        

We can see that Ash Sigley is still in the database, but no longer belongs to the Sales department (or any department for that matter).

Considerations

• Null Handling: When applying ON UPDATE SET NULL, your application must be designed to handle NULL values in the foreign key columns.
• Data Integrity: Consider whether setting a foreign key to NULL may violate any business rules.
• Performance Impact: Updating a primary key that triggers ON UPDATE SET NULL on multiple child records can impact performance.