Control Referential Integrity Across SQL Server Databases and Identify Orphan Records

By: Alejandro Cobar   |   Updated: 2023-06-30   |   Comments   |   Related: > Referential Integrity

Problem

In my previous article about cross database referential integrity, I covered a solution from the child table perspective to prevent having data without a corresponding parent record in a different database. In this article, we explore how to implement a solution for the parent table if you want to avoid using INSTEAD OF triggers on the child table.

Solution

In this article, I will cover how cross database referential integrity can be controlled from the parent table and also a complementary option if you want to avoid using triggers.

Initial Considerations

• This article covers databases within a SQL Managed Instance, a SQL Server instance in a VM, or an on-prem deployment. Therefore, Azure SQL databases won't apply to this solution.
• It is assumed that you have already read or are familiar with the previous article, Checking Cross-Database Referential Integrity in SQL Server - Part 1.

Trigger in the Parent Table

Let's jump straight to the code with a few notes:

• The solution will be exposed using a 1 to 1 "virtual relationship."
• I will be using the same objects created in the previous article: "parent" and "children" databases, along with the "parent_table" and "child_table," respectively.
• You can get as complex/specific as you want with your implementation, meaning that you can decide the course of action you want to take when the parent records are deleted (e.g., delete the child records as well or leave them as they are).

-- source: https://www.MSSQLTips.com
--
--

CREATE OR ALTER TRIGGER [dbo].[trg_fk_parent] ON [dbo].[parent_table]
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE FROM children..child_table WHERE configuration_id IN (SELECT id FROM DELETED)
 
    SELECT @@ROWCOUNT AS 'Child rows deleted'
       
    IF(@@ROWCOUNT > 0)
       DELETE FROM parent_table WHERE id IN (SELECT id FROM DELETED)
 
    RETURN
END
GO

Before executing a DELETE statement in the parent table, let's first have a quick look at the current data in both tables:

Let's try to delete a row that doesn't exist in the parent table to see what outcome we get:

As expected, we got a message that no child rows were affected.

Now, let's delete an existing parent record:

This time the message is that a child row was deleted. Let's see the final state of our tables for comparison:

Excellent, it worked as expected!

You might think that the code of the parent trigger looks a bit cheap/trivial, and you are right (at least compared to the code of the trigger from the child_table). However, keep in mind that it does not cease to be the parent control point of your child data, which can be quite important to your use case.

Tradeoffs

A solution like this comes with its tradeoffs (as with anything), some of which can be:

• Hard to maintain if you have many parent/child tables in your environment.
• Performance penalty for your child table if it is a highly transactional table.

The code within your triggers can be as complex as the database developer wants, and if it is not well documented, it can be a nightmare to maintain.

Identify Orphan Records

If the tradeoffs are a deal breaker for your use case and you don't want to use triggers, we can look at a different approach to this cross-database parent/child references.

Stored Procedure to Detect Orphan Records

This stored procedure will help identify any issues of orphaned records between the tables.

-- source: https://www.MSSQLTips.com
--
--

CREATE OR ALTER PROCEDURE Check_OrhpanRecords
   @parent_table    VARCHAR(64),
   @parent_table_id VARCHAR(64),
   @child_table     VARCHAR(64),
   @child_table_id  VARCHAR(64),
   @child_table_fk  VARCHAR(64)
AS
BEGIN
   SET NOCOUNT ON;
 
   DECLARE @sqlCommand NVARCHAR(500)
 
   SET @sqlCommand = '
   SELECT parent.'+@parent_table_id+' AS parent_id,
          child.'+@child_table_fk+' AS child_record_fk,
         child.'+@child_table_id+' AS child_record_id
   FROM '+@child_table+' child
   LEFT JOIN '+@parent_table+' parent ON parent.'+@parent_table_id+' = child.'+@child_table_fk+'
   WHERE parent.'+@parent_table_id+' IS NULL
   '
 
   EXECUTE sp_executesql @sqlCommand
END
GO

Let me demonstrate execution of this stored procedure using the following data from our demo:

You can see that the child_table has records with id 5 and 6, pointing to a non-existing parent record with id 3.

Bingo! The stored procedure can show the orphan records.

Pay close attention to the names passed to the parameters because those will dictate how the lookup is performed. If you specify a wrong value in any of them, you might get weird or no results.

Here's what each parameter means, so you can get a good idea while attempting to use this solution:

• @parent_table: the fully qualified name of the parent table.
• @parent_table_id: the name of the PK field in the parent table.
• @child_table: the fully qualified name of the child table.
• @child_table_fk: the name of the field in the child_table that virtually points to the parent record.
• @child_table_id: the name of the PK in the child_table.

Remember that you can tweak the logic of the stored procedure if you want to perform additional corrective actions; that will depend on your use case. With this solution, you can automate the execution of the stored procedure against all the qualifying pair of tables within your environment and build a reporting mechanism around it to stay on top of any issues.

Next Steps

• In this short set of articles, we covered a couple of simple solutions to take care of referenced/unreferenced data sitting across different databases in your environment. As the data platform caretaker, you should be able to determine which solution fits best for your environment. So, make sure to properly test the one you pick and be ready to give an extra layer of polish to the quality of the data sitting around within the database under your control.
• Check out this first article: Checking Cross-Database Referential Integrity in SQL Server - Part 1.

About the author

Alejandro Cobar is an MCP, SQL Server DBA and Developer with 10+ years of experience working in a variety of environments.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips