Ajouter une relation de clé étrangère entre deux bases de données

Question

J'ai deux tables dans deux bases de données différentes. Dans la table 1 (dans la base de données 1), il y a une colonne appelée colonne 1 et c'est une clé primaire. Maintenant, dans la table 2 (dans la base de données 2), il y a une colonne appelée colonne 2 et je veux l'ajouter en tant que clé étrangère.

J'ai essayé de l'ajouter et j'ai obtenu l'erreur suivante :

Msg 1763, Level 16, State 0, Line 1
Les références à des clés étrangères entre bases de données ne sont pas prises en charge. Clé étrangère Database2.table2.

Msg 1750, Level 16, State 0, Line 1
Impossible de créer une contrainte. Voir les erreurs précédentes.

Comment faire puisque les tables sont dans des bases de données différentes.

Answer 1

Vous devez gérer la contrainte référentielle entre les bases de données à l'aide d'un déclencheur.

---

En principe, vous créez un déclencheur d'insertion et de mise à jour pour vérifier l'existence de la clé dans la table de la clé primaire. Si la clé n'existe pas, vous annulez l'insertion ou la mise à jour, puis vous traitez l'exception.

Exemple :

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

---

Édité : Juste pour clarifier. Ce n'est pas la meilleure approche pour assurer l'intégrité référentielle. L'idéal serait que les deux tables se trouvent dans la même base de données, mais si ce n'est pas possible. Dans ce cas, la solution décrite ci-dessus est une solution de contournement potentielle.

Answer 2

Si vous avez besoin d'une intégrité à toute épreuve, placez les deux tables dans une seule base de données et utilisez une contrainte FK. Si votre table mère se trouve dans une autre base de données, rien n'empêche quiconque de restaurer cette base de données mère à partir d'une ancienne sauvegarde, et vous vous retrouvez alors avec des orphelins.

C'est pourquoi le FK entre bases de données n'est pas pris en charge.

Answer 3

Vous pouvez utiliser une contrainte de contrôle avec une fonction définie par l'utilisateur pour effectuer le contrôle. Cette fonction est plus fiable qu'un déclencheur. Elle peut être désactivée et réactivée si nécessaire, tout comme les clés étrangères, et vérifiée à nouveau après une restauration de la base de données2.

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 4

D'après mon expérience, la meilleure façon de gérer ce problème lorsque la source d'information principale faisant autorité pour deux tables liées doit se trouver dans deux bases de données distinctes est de synchroniser une copie de la table de l'emplacement principal vers l'emplacement secondaire (en utilisant T-SQL ou SSIS avec un contrôle d'erreur approprié - vous ne pouvez pas tronquer et repeupler une table lorsqu'elle a une référence de clé étrangère, il y a donc plusieurs façons de procéder à la mise à jour de la table).

Ajoutez ensuite une relation FK traditionnelle dans le deuxième emplacement de la table, qui est en fait une copie en lecture seule.

Vous pouvez utiliser un déclencheur ou un travail planifié dans l'emplacement principal pour maintenir la copie à jour.

Answer 5

La réponse courte est que SQL Server (à partir de SQL 2008) ne prend pas en charge les clés étrangères entre bases de données, comme l'indique le message d'erreur.

Bien qu'il ne soit pas possible d'avoir une intégrité référentielle déclarative (le FK), on peut atteindre le même objectif en utilisant des déclencheurs. C'est un peu moins fiable, parce que la logique que vous écrivez peut avoir des bogues, mais cela vous permettra tout de même d'atteindre votre but.

Voir la documentation SQL @ http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx Quel état :

Les déclencheurs sont souvent nous les règles de gestion et l'intégrité des données. SQL [ ] l'intégrité référentielle déclarative (DRI) par le biais de les instructions de création de tables (ALTER TABLE et CREATE TABLE) ; cependant, DRI n'assure pas l'intégrité référentielle l'intégrité référentielle entre bases de données. Pour appliquer l'intégrité référentielle l'intégrité référentielle (règles concernant les relations entre les k primaires et les k clés étrangères des tables), utilisez les clés primaires et des clés étrangères (la directive PRIMARY KEY et FOREIGN KEY des commandes ALTER TABLE et CREATE TABLE). Si des contraintes existent sur la table elles sont vérifiées après le triage AU LIEU DE l'exécution du déclencheur AFTER. Si les sont violées, le déclencheur INSTEAD OF sont annulées et le déclencheur AFTER le déclencheur AFTER n'est pas exécuté (tiré).

Il y a également une discussion intéressante sur SQLTeam - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31135