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.
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.
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.
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.
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.
Next Steps
- Read my previous tip about getting started with triggers.
- If you found it hard to understand the way I captured which DML operation were performed in the Employees table take a look at this tip: Understanding SQL Server inserted and deleted tables for DML triggers.
- For another example of an INSTEAD OF trigger you can check out this tip: Using INSTEAD OF triggers in SQL Server for DML operations.
- You should also consider the fact that someone could accidentally drop the table. In a future tip I will show you how to create a DDL trigger to get that scenario covered.
- Stay tuned to the SQL Server Triggers Tips Category for more tips and tricks.
