primarias - relacionar tablas en sql server por codigo

Agregar una relación de clave externa entre dos bases de datos

Question

Tengo dos tablas en dos bases de datos diferentes. En la tabla 1 (está en database1) hay una columna llamada column1 y es una clave principal. Ahora en la tabla 2 (está en databse2) hay una columna llamada coulmn2 y quiero agregarla como una clave externa.

Traté de agregarlo y me dio el siguiente error:

Msj 1763, nivel 16, estado 0, línea 1
Las referencias de claves externas entre bases de datos no son compatibles. Clave externa Database2.table2.

Msg 1750, nivel 16, estado 0, línea 1
No se pudo crear una restricción. Ver errores previos.

¿Cómo hago eso? Ya que las tablas están en diferentes bases de datos.

Answer 1

Como dice el mensaje de error, esto no es compatible con el servidor sql. La única forma de garantizar la integridad refrerencia es trabajar con desencadenadores.

Answer 2

Debería gestionar la restricción referencial en las bases de datos utilizando un desencadenador.

Básicamente, usted crea una inserción, activador de actualización para verificar la existencia de la clave en la tabla de clave principal. Si la clave no existe, invierta la inserción o actualización y luego maneje la excepción.

Ejemplo:

Create Trigger dbo.MyTableTrigger ON dbo.MyTable, After Insert, Update
As
Begin

   If NOT Exists(select PK from OtherDB.dbo.TableName where PK in (Select FK from inserted) BEGIN
      -- Handle the Referential Error Here
   END

END

Editado: solo para aclarar. Este no es el mejor enfoque para imponer la integridad referencial. Lo ideal es que desee ambas tablas en el mismo db, pero si eso no es posible. Entonces lo anterior es una posible solución para ti.

Answer 3

En mi experiencia, la mejor manera de manejar esto cuando la fuente de información primaria autorizada para dos tablas que están relacionadas tiene que estar en dos bases de datos separadas es sincronizar una copia de la tabla desde la ubicación primaria a la ubicación secundaria (usando T- SQL o SSIS con verificación de errores adecuada: no puede truncar y volver a llenar una tabla mientras tiene una referencia de clave externa, por lo que hay algunas maneras de descartar el gato en la actualización de la tabla).

A continuación, agregue una relación FK tradicional en la segunda ubicación a la tabla que efectivamente es una copia de solo lectura.

Puede usar un disparador o un trabajo programado en la ubicación principal para mantener la copia actualizada.

Answer 4

La respuesta corta es que SQL Server (a partir de SQL 2008) no admite claves externas de bases de datos cruzadas, como indica el mensaje de error.

Si bien no puede tener integridad referencial declarativa (el FK), puede alcanzar el mismo objetivo utilizando desencadenantes. Es un poco menos confiable, porque la lógica que usted escribe puede tener errores, pero de todos modos lo hará llegar allí.

Consulte los documentos de SQL @ http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx Qué estado:

Los desencadenantes a menudo se utilizan para hacer cumplir las reglas comerciales y la integridad de los datos. SQL Server proporciona integridad referencial declarativa (DRI) a través de las sentencias de creación de tablas (ALTER TABLE y CREATE TABLE); sin embargo, DRI no proporciona integridad referencial entre bases de datos. Para forzar la integridad referencial (reglas sobre las relaciones entre las claves primaria y externa de las tablas), use restricciones de clave primaria y extranjera (las palabras clave PRIMARY KEY y FOREIGN KEY de ALTER TABLE y CREATE TABLE). Si existen restricciones en la tabla de activación, se verifican después de la ejecución del desencadenador INSTEAD OF y antes de la ejecución del desencadenador AFTER. Si se infringen las restricciones, las acciones del disparador INSTEAD OF se revierten y el disparador AFTER no se ejecuta (dispara).

También hay una discusión correcta en SQLTeam - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31135

Answer 5

Puede usar la restricción de verificación con una función definida por el usuario para realizar la comprobación. Es más confiable que un disparador. Se puede desactivar y reactivar cuando sea necesario, al igual que las claves externas y volver a verificar después de una restauración de la base de datos2.

CREATE FUNCTION dbo.fn_db2_schema2_tb_A
(@column1 INT) 
RETURNS BIT
AS
BEGIN
    DECLARE @exists bit = 0
    IF EXISTS (
      SELECT TOP 1 1 FROM DB2.SCHEMA2.tb_A 
      WHERE COLUMN_KEY_1 =  @COLUMN1
    ) BEGIN 
         SET @exists = 1 
      END;
      RETURN @exists
END
GO

ALTER TABLE db1.schema1.tb_S
  ADD CONSTRAINT CHK_S_key_col1_in_db2_schema2_tb_A
    CHECK(dbo.fn_db2_schema2_tb_A(key_col1) = 1)

Answer 6

Si necesita una sólida integridad, tenga ambas tablas en una base de datos y use una restricción FK. Si su tabla primaria está en otra base de datos, nada impide que alguien restaure esa base de datos primaria de una copia de seguridad anterior, y luego tiene huérfanos.

Esta es la razón por la cual FK entre bases de datos no es compatible.