Problema: Tienes dos bases de datos de SQL Server que no son independientes entre sí, la consistencia de una depende de la consistencia de la otra. Dado que deseas mantener todas las reglas de negocio en la base de datos, tu primera idea es usar una restricción de clave externa entre las dos bases de datos. Sin embargo, cuando intentas crear una clave externa entre las dos tablas en diferentes bases de datos, rápidamente te das cuenta de que no es posible.

Solución: En este consejo, explicaremos una forma de lograr la integridad referencial entre bases de datos utilizando disparadores para obtener el mismo resultado que con una restricción de clave externa.

Cuando trabajas con una base de datos relacional como SQL Server, es importante priorizar la integridad de los datos. Ya sea que seas un administrador de bases de datos o un desarrollador, es crucial asegurarte de que tus bases de datos sean consistentes y precisas. La integridad referencial entre bases de datos se refiere a la implementación de la integridad referencial entre diferentes bases de datos, ya sea que estén en el mismo servidor o en computadoras remotas.

El concepto de integridad referencial establece que las relaciones entre tablas siempre deben ser consistentes. Si una tabla A tiene una relación con otra tabla B, y una fila de la tabla A hace referencia a una fila de la tabla B, entonces esa fila de la tabla B debe existir. Sin embargo, Microsoft no ofrece la posibilidad de crear una restricción de clave externa entre diferentes bases de datos o incluso entre diferentes servidores. Esto se debe a que si una de las bases de datos se desconecta, podría provocar una pérdida de consistencia.

En su lugar, podemos utilizar disparadores para implementar la integridad referencial entre bases de datos. Los disparadores son tipos especiales de procedimientos almacenados que se ejecutan automáticamente en respuesta a eventos específicos, como modificaciones de datos. Al utilizar disparadores, podemos realizar validaciones antes de cambiar o eliminar cualquier dato en la tabla referenciada, lo que nos permite codificar nuestro propio algoritmo para verificar violaciones de integridad referencial.

Existen dos tipos de disparadores: disparadores FOR/AFTER y disparadores INSTEAD OF. Ambos tipos son adecuados para implementar la integridad referencial entre bases de datos, pero se prefieren los disparadores INSTEAD OF. Los disparadores INSTEAD OF reemplazan el comando DML real que dispara el disparador, lo que nos permite ejecutar nuestro código en lugar de la instrucción que puede cambiar la fila de la tabla referenciada.

Cuando utilizamos disparadores para hacer cumplir las relaciones entre tablas, es importante crear los disparadores de manera que puedan manejar datos basados en conjuntos en lugar de fila por fila.

Consideremos un ejemplo práctico para demostrar cómo implementar la integridad referencial con un disparador. Supongamos que tenemos una aplicación para el departamento de recursos humanos que está integrada con otra aplicación que maneja el acceso de seguridad para todas las aplicaciones de la empresa. Una persona debe existir primero en la base de datos SecDB antes de poder ser ingresada en la base de datos HR.

Para configurar nuestro entorno de prueba, necesitamos crear dos bases de datos: SecDB, que contendrá los inicios de sesión, y HR, donde se almacenará la información sobre los empleados.

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

A continuación, creamos la tabla Users en la base de datos SecDB y la llenamos con datos:

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

También necesitamos crear la tabla Employees en la base de datos HR:

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

En este punto, tenemos una relación entre la tabla Users en la base de datos SecDB y la tabla Employees en la base de datos HR. La relación significa que no deberíamos poder eliminar una fila de la tabla Users que tenga una fila asociada en la tabla Employees.

Para hacer cumplir esta integridad referencial, podemos crear un disparador INSTEAD OF en la tabla Users que reemplazará la instrucción DELETE:

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, 'Primero debes eliminar al Empleado', 1;  
        END
    ELSE
        BEGIN
            DELETE  Users
            FROM    Users U
                    INNER JOIN Deleted D ON D.UserID = U.UserID  
        END
GO

Además, podemos crear un disparador INSTEAD OF que reemplazará la instrucción UPDATE:

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, 'Al menos un Usuario tiene un Empleado asociado. No se permite modificar el UserID.', 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

Ahora, podemos probar cómo funciona nuestra solución alternativa de clave externa con una instrucción DELETE:

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

Como puedes ver, cuando intentamos eliminar una fila de la tabla Users que está referenciada por la tabla Employees, obtenemos un mensaje de error. Por otro lado, si intentamos eliminar una fila de la tabla Users que no tiene una fila correspondiente en la tabla Employees, la instrucción de eliminación funciona como se espera.

También podemos probar cómo funciona nuestra solución con una instrucción UPDATE:

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

Como puedes ver, nuestro disparador INSTEAD OF UPDATE impide la actualización de la tabla Users ya que UserID = 4 tiene una relación con una fila en la tabla Employees. Sin embargo, si intentamos cambiar el valor de UserID = 3 a UserID = 100, la instrucción de actualización funciona porque no tiene conflictos con la tabla Employees.

En conclusión, mediante el uso de disparadores, podemos implementar la integridad referencial entre bases de datos en SQL Server. Los disparadores nos permiten hacer cumplir las validaciones necesarias y mantener la consistencia de nuestras bases de datos, incluso cuando no son independientes entre sí. Aunque los disparadores tienen sus propios riesgos, como la posible pérdida de consistencia si no se implementan correctamente, proporcionan una solución viable cuando no es posible utilizar restricciones de clave externa entre diferentes bases de datos.