在两个数据库之间添加外键关系 [英] Add Foreign Key relationship between two Databases

问题描述

我在两个不同的数据库中有两个表。在table1（它在database1中）有一个名为column1的列，它是一个主键。现在在table2（它是在databse2）有一个名为coulmn2的列，我想添加它作为外键。

我试图添加它，它给了我以下错误：

Msg 1763，Level 16，State 0，Line 1

跨数据库外键引用不被支持。外键Database2.table2。

消息1750，级别16，状态0，行1

无法创建约束。查看以前的错误。

我该怎么做既然这些表在不同的数据库中。

---

基本上你创建一个插入，更新触发器来验证主键表中的键是否存在。如果该键不存在，则返回插入或更新，然后处理该异常。

示例：

 创建触发器dbo.MyTableTrigger ON dbo.MyTable，插入后更新
作为
开始
 
如果不存在（从OtherDB中选择PK。 dbo.TableName其中PK in（从插入的FK中选择）BEGIN 
  - 在这里处理引用错误
 END 
 
 END 
  

---

编辑：只是为了澄清这不是强制引用的最佳方法完整性。理想情况下，你会希望两个表在同一个数据库，但如果这是不可能的。那么上述是一个潜在的解决你的。

I have two tables in two different databases. In table1(It is in database1) there is a column called column1 and it is a primary key. Now in table2(It is in databse2) there is a column called coulmn2 and I want to add it as a foreign key.

I tried to add it and it gave me the following error:

Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key Database2.table2.

Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

How do I do that Since The tables are in different databases.

解决方案

You would need to manage the referential constraint across databases using a Trigger.

---

Basically you create an insert, update trigger to verify the existence of the Key in the Primary key table. If the key does not exist then revert the insert or update and then handle the exception.

Example:

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

---

Edited: Just to clarify. This is not the best approach with enforcing referential integrity. Ideally you would want both tables in the same db but if that is not possible. Then the above is a potential work around for you.

这篇关于在两个数据库之间添加外键关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！