SQL Server Referential Integrity Across Databases Using Triggers

By:   |   Updated: 2019-03-04   |   Comments   |   Related: More > Triggers

Problem

You have two SQL Server databases that are not independent of each other, the consistency of one relies on the consistency of the other. Since you want to keep all the business rules in the database your first thought is to use a Foreign Key constraint between the two databases, but when you try to create a foreign key between the two tables on the different databases you quickly learn that it is not possible. In this tip I will explain a way to achieve this with triggers to get the same result as with a Foreign Key constraint.

Solution

When working with a relational database, like SQL Server we must keep the premise that the data is always the most important thing. If you are a DBA, it is pointless if your SQL Server instance complies with security and regulations (like SOX, HIPAA or ISO some_number to name a few) if the information on which your company relies on is corrupt or inexact. Additionally, it is also pointless if you are a developer and your application works perfectly, but the databases in use for your application are inconsistent.

What is Cross Database Referential Integrity in SQL Server?

It refers to implementing referential integrity between different databases. Those databases could be in the same server or in a remote computer.

We all know that the concept of referential integrity states that table relationships must always be consistent. In other words, if a given table A has a relation with another table B, and a row of table A references a row of table B, then that row of table B must exist.

Maybe you ask yourself why Microsoft does not offer the possibility to create a foreign key amongst different databases or even different servers. But think about if it were possible, what would happen if for example you have defined a foreign key between two databases and you set one of those databases offline? In the event that happened you could lose consistency on your databases.

Of course, if we implement Cross Database Referential Integrity by using triggers, we will face the same risk. The difference is that in this case we can't blame Microsoft for the eventual loss of consistency because the implementation is our code.

The SQL Server Trigger Approach

We can use triggers to implement foreign keys amongst databases. Basically, the trigger will perform some validations before changing or deleting any data on the referenced table allowing us to code our own algorithm to check for referential integrity violations.

If you have read my previous tip about triggers, you may remember that there are two types of triggers, the FOR/AFTER triggers which are fired only when all operations specified in the triggering SQL statement have executed successfully; and the INSTEAD OF triggers. Although both types of triggers are suitable for implementing cross database referential integrity, I prefer to use INSTEAD OF triggers.

The reason why I prefer this type of triggers is not only because the INSTEAD OF triggers replaces the actual DML command that fires the trigger. Basically, it is about a semantic difference. I mean, we don't want to run our referential integrity code after changing the referenced table rows with all that resource consumption. Instead, and forgive the redundancy, we want to run our code instead of the statement that may change the referenced table row.

Something you must have in mind when using triggers to enforce relations amongst tables is that you must create the triggers in such a way that they can handle set based data instead of row by row.

Sample SQL Server Referential Integrity Solution with Triggers

Now I will show you a practical example on how to implement referential integrity with a trigger. Let's suppose that we have an application for the human resources department. This application is integrated with other application that handles security access for all the applications of the company.

A person must first exist in the SecDB database before they can be entered into the HR database.

In order to set up our test environment, first we need to create two databases, SecDB which will contain the logins and HR which is where the information about employees will be stored.

USE Master;
GO
CREATE DATABASE [HR]
 ON  PRIMARY 
( NAME = N'HR', FILENAME = N'E:\MSSQL\HR.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'HR_log', FILENAME = N'E:\MSSQL\HR_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
 
CREATE DATABASE [SecDB]
 ON  PRIMARY 
( NAME = N'SecDB', FILENAME = N'E:\MSSQL\SecDB.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'SecDB_log', FILENAME = N'E:\MSSQL\SecDB_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO			

The next step is to create the table Users in the SecDB database and fill it with data.

USE [SecDB]
GO
CREATE TABLE [dbo].[Users]
    (
      [UserID] [INT] NOT NULL ,
      [UserName] [VARCHAR](50) NOT NULL ,
      [UserPassword] [VARCHAR](50) NOT NULL ,
      PRIMARY KEY CLUSTERED ( [UserID] )
    )
GO
INSERT  [dbo].[Users]
        ( [UserID] ,
          [UserName] ,
          [UserPassword]
        )
        SELECT  1 ,
                N'Garrison Haney' ,
                N'Pa$$w0rd'
        UNION ALL
        SELECT  2 ,
                N'Ursa Dyer' ,
                N'Pa$$w0rd'
        UNION ALL
        SELECT  3 ,
                N'Shannon Moody' ,
                N'Pa$$w0rd'
        UNION ALL
        SELECT  4 ,
                N'Jack Hensley' ,
                N'Pa$$w0rd'
        UNION ALL
        SELECT  5 ,
                N'Harriet Williamson' ,
                N'Pa$$w0rd'
        UNION ALL
        SELECT  6 ,
                N'Rahim Moran' ,
                N'Pa$$w0rd'
        UNION ALL
        SELECT  7 ,
                N'Kirestin Ingram' ,
                N'Pa$$w0rd'        
GO			

Also, we must do the same with the Employees table in the HR database.

USE [HR]
GO
 
CREATE TABLE [dbo].[Employees]
    (
      [EmployeeID] [INT] IDENTITY(1, 1) NOT NULL ,
      [EmployeeName] [VARCHAR](50) NULL ,
      [EmployeeAddress] [VARCHAR](50) NULL ,
      [MonthSalary] [NUMERIC](10, 2) NULL ,
      [UserID] [INT] NULL ,
      PRIMARY KEY CLUSTERED ( [EmployeeID] )
    )
GO

CREATE UNIQUE NONCLUSTERED INDEX IX_UserID ON dbo.Employees
(
   UserID ASC
)
GO
 
SET IDENTITY_INSERT [dbo].[Employees] ON 
GO
INSERT  [dbo].[Employees]
        ( [EmployeeID] ,
          [EmployeeName] ,
          [EmployeeAddress] ,
          [MonthSalary] ,
          [UserID]
        )
        SELECT  1 ,
                N'Garrison Haney' ,
                N'381-4291 Enim, Av.' ,
                CAST(6357.00 AS NUMERIC(10, 2)) ,
                1
        UNION ALL
        SELECT  2 ,
                N'Ursa Dyer' ,
                N'Ap #596-1792 Odio. St.' ,
                CAST(6168.00 AS NUMERIC(10, 2)) ,
                2
        UNION ALL
        SELECT  4 ,
                N'Jack Hensley' ,
                N'363-7192 Eu, Av.' ,
                CAST(7333.00 AS NUMERIC(10, 2)) ,
                4
        UNION ALL
        SELECT  5 ,
                N'Harriet Williamson' ,
                N'369-7806 Vulputate St.' ,
                CAST(5574.00 AS NUMERIC(10, 2)) ,
                5
        UNION ALL
        SELECT  6 ,
                N'Rahim Moran' ,
                N'675-1652 Venenatis Ave' ,
                CAST(9970.00 AS NUMERIC(10, 2)) ,
                6
        UNION ALL
        SELECT  7 ,
                N'Kirestin Ingram' ,
                N'Ap #255-8512 Adipiscing. St.' ,
                CAST(8603.00 AS NUMERIC(10, 2)) ,
                7
GO
SET IDENTITY_INSERT [dbo].[Employees] OFF
GO			

At this point we can say that basically there is a relation between the table Users in the SecDB database and the table Employees in the HR database. The relation means that we should not be able to delete a row from the Users table that has associated row in the Employees table.

These are the tables on our test environment.

Create SQL Server Trigger to Enforce Referential Integrity

Now we are ready to create the INSTEAD OF trigger on the table Users that will replace the DELETE statement. As you may see in the code below, we throw an exception when a DELETE statement will affect rows on the Users table with an associated row on the Employees table.

USE SecDB;
GO
CREATE TRIGGER TR_Users_Employees_Delete ON dbo.Users
    INSTEAD OF DELETE
AS
    SET NOCOUNT ON
    IF EXISTS ( SELECT  0
                FROM    Deleted D
                        INNER HR.dbo.Employees E ON D.UserID = E.UserID )
        BEGIN
         ;
            THROW 51000, 'You Need to delete the Employee First', 1;  
        END
    ELSE
        BEGIN
            DELETE  Users
            FROM    Users U
                    INNER JOIN Deleted D ON D.UserID = U.UserID  
        END
GO			

Additionally, in the next code section you will see the INSTEAD OF trigger that will replace the UPDATE statement. In this trigger we validate that no one can modify the UserID column if there is a row on table Employees with that UserID.

USE SecDB;
GO
CREATE TRIGGER TR_Users_Employees_Update ON dbo.Users
    INSTEAD OF UPDATE
AS
    SET NOCOUNT ON
    IF EXISTS ( SELECT  0
                FROM    Deleted D 
                        INNER JOIN HR.dbo.Employees E ON D.UserID = E.UserID )
        BEGIN
         ;
            THROW 51000, 'At least one User has an associated Employee. Modifying UserID is not allowed.', 1;  
        END
    ELSE
        BEGIN
            UPDATE  dbo.Users
            SET     UserName = I.UserName,
               UserPassword = I.UserPassword
            FROM    Inserted I
                    INNER JOIN dbo.Users U 
               ON U.UserID = I.UserID
        END 
GO			

Test SQL Server Trigger to Enforce Referential Integrity

Now in order to test how our foreign key alternative solution works with a DELETE statement we can use the following code. I put the code inside a transaction just to keep order in my test environment, you can omit this.

BEGIN TRANSACTION
SELECT * FROM dbo.Users WHERE UserID = 4
SELECT * FROM HR.dbo.Employees WHERE UserID = 4
 
DELETE FROM dbo.Users WHERE UserID = 4
ROLLBACK TRANSACTION			

As you can see in the image below, when we try to remove a row from the Users table which is referenced by the Employees table, we get an error message.

Testing the INSTEAD OF DELETE trigger.

Now let's try to delete a row from the Users table with UserID = 3, which as you can see above has no corresponding row in the Employees table. Here is the code.

BEGIN TRANSACTION
SELECT * FROM dbo.Users WHERE UserID = 3
SELECT * FROM HR.dbo.Employees WHERE UserID = 3
 
DELETE FROM dbo.Users WHERE UserID = 3
ROLLBACK TRANSACTION			

On the next screen capture you can see that the delete statement worked as expected.

Testing the INSTEAD OF DELETE trigger.

Let's see how our solution works with an update statement when the row to be updated will create an integrity conflict.

BEGIN TRANSACTION
SELECT * FROM dbo.Users WHERE UserID = 4
SELECT * FROM HR.dbo.Employees WHERE UserID = 4
 
UPDATE dbo.Users SET UserID = 100 WHERE UserID = 4
ROLLBACK TRANSACTION			

As you can see on the next screen capture, our instead of update trigger worked as expected by disallowing the update of the Users table since UserID = 4 has a relation with a row on the Employees table.

Testing the INSTEAD OF UPDATE trigger.

Now let's try to change the value of UserID = 3 to UserID = 100.

BEGIN TRANSACTION
SELECT * FROM dbo.Users WHERE UserID = 3
SELECT * FROM HR.dbo.Employees WHERE UserID = 3
 
UPDATE dbo.Users SET UserID = 100 WHERE UserID = 3
ROLLBACK TRANSACTION			

As you can see on the next screen capture, we were able to modify the UserID value because it has no conflicts with the Employees table.

Testing the INSTEAD OF UPDATE trigger.
Next Steps


Last Updated: 2019-03-04


get scripts

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools