When creating a foreign key constraint in SQL Server, we have the option of including ON DELETE SET NULL in the definition. When we use this option in a foreign key, it tells SQL Server to automatically set the foreign key column values to NULL in the child table when the corresponding primary key record in the parent table is deleted.

Example

Imagine we have a database for a school management system that tracks students and the courses they are enrolled in. The relationship between students and courses is maintained through a Registrations table, which references both the Students and Courses tables.

Tables:

1. Students:
   • StudentID (Primary Key)
   • Name
   • Email
2. Courses:
   • CourseID (Primary Key)
   • CourseName
3. Registrations:
   • RegistrationID (Primary Key)
   • StudentID (Foreign Key)
   • CourseID (Foreign Key)
   • RegistrationDate

Use Case for ON DELETE SET NULL:

In this case, if a course is deleted from the system (perhaps it is no longer offered), it might make sense to keep the student’s registration record for historical purposes but set the CourseID to NULL since the course no longer exists. This ensures the student’s record isn’t lost, but acknowledges that the specific course is no longer available.

Therefore, we can create a foreign key with ON DELETE SET NULL to facilitate this requirement.

Here’s an example of a script that creates the tables:

-- Create Students table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Email NVARCHAR(100)
);

-- Create Courses table
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName NVARCHAR(100)
);

-- Create Registrations table with ON DELETE SET NULL
CREATE TABLE Registrations (
    RegistrationID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    RegistrationDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID) ON DELETE SET NULL
);

Here, the Registrations table has a foreign key that uses the ON DELETE SET NULL option. We can see that option in the last line of the table definition. Actually, the table has two foreign keys, but only one uses the ON DELETE SET NULL option. The other one uses the default option (which is ON DELETE NO ACTION).

Now let’s insert some data:

-- Insert data into Students table
INSERT INTO Students (StudentID, Name, Email) VALUES
(1, 'Max Power', '[email protected]'),
(2, 'Lisa Weir', '[email protected]');

-- Insert data into Courses table
INSERT INTO Courses (CourseID, CourseName) VALUES
(1, 'Mathematics'),
(2, 'History');

-- Insert data into Registrations table
INSERT INTO Registrations (RegistrationID, StudentID, CourseID, RegistrationDate) VALUES
(1, 1, 1, '2024-08-20'),
(2, 2, 2, '2024-08-21');

Here’s what we’ve got so far:

SELECT * FROM Students;
SELECT * FROM Courses;
SELECT * FROM Registrations;

Result:

StudentID  Name       Email                
---------  ---------  ---------------------
1          Max Power  [email protected]
2          Lisa Weir  [email protected]

CourseID  CourseName 
--------  -----------
1         Mathematics
2         History    

RegistrationID  StudentID  CourseID  RegistrationDate        
--------------  ---------  --------  ------------------------
1               1          1         2024-08-20T00:00:00.000Z
2               2          2         2024-08-21T00:00:00.000Z

Demonstrate ON DELETE SET NULL

Now that we’ve got data in the tables, let’s look at what happens when we delete a row from the parent table of the relationship:

-- Delete a course
DELETE FROM Courses WHERE CourseID = 1;

-- Check data after deletion
SELECT * FROM Students;
SELECT * FROM Courses;
SELECT * FROM Registrations;

Result:

StudentID  Name       Email                
---------  ---------  ---------------------
1          Max Power  [email protected]
2          Lisa Weir  [email protected]

CourseID  CourseName
--------  ----------
2         History   

RegistrationID  StudentID  CourseID  RegistrationDate        
--------------  ---------  --------  ------------------------
1               1          null      2024-08-20T00:00:00.000Z
2               2          2         2024-08-21T00:00:00.000Z

So we can see that the course has disappeared from the Courses table, and the Registrations table has null in the CourseID for the deleted course (previously it had 1, which was the ID for that course).

Explanation:

• The Registrations table contains foreign keys to both Students and Courses tables.
• The foreign key constraint on CourseID in the Registrations table is set with ON DELETE SET NULL. This means if a course is deleted, any related CourseID in the Registrations table will be set to NULL rather than deleting the row.
• After executing the script, if the Mathematics course (with CourseID = 1) is deleted, the CourseID in the Registrations table for RegistrationID = 1 will be set to NULL, but the row itself remains intact. This retains the historical data for the student’s registration but reflects that the course no longer exists.

When to Use ON DELETE SET NULL

The ON DELETE SET NULL option isn’t for every occasion. There are many occasions where other options would be more appropriate, such as ON DELETE CASCADE, ON DELETE SET DEFAULT, or the default option ON DELETE NO ACTION.

That said, there are also times ON DELETE SET NULL would be a good option. We might want to use it when we want to preserve child records when a parent record is deleted. For example, when historical data should be preserved, but the link to a non-existent entity should be removed.

The ON DELETE SET NULL option might also be a good fit when the relationship between tables is optional.

Considerations

• Ensure the foreign key column is nullable.
• Be aware of potential data integrity issues with orphaned records.
• Consider using ON DELETE CASCADE if child records should always be deleted with the parent.
• Use with caution in complex database designs to avoid unintended consequences.