SQL 在两个数据库之间添加外键关系

Add Foreign Key relationship between two Databases

提问by Sam

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

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

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.

消息 1763，级别 16，状态 0，第 1 行
不支持跨数据库外键引用。外键 Database2.table2。

消息 1750，级别 16，状态 0，行 1
无法创建约束。请参阅以前的错误。

How do I do that since the tables are in different databases.

由于表位于不同的数据库中，我该怎么做。

采纳答案by John Hartsock

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.

编辑：只是为了澄清。这不是强制执行参照完整性的最佳方法。理想情况下，您希望两个表都在同一个数据库中，但如果这是不可能的。那么上面的内容对您来说是一个潜在的解决方法。

回答by A-K

If you need rock solid integrity, have both tables in one database, and use an FK constraint. If your parent table is in another database, nothing prevents anyone from restoring that parent database from an old backup, and then you have orphans.

如果您需要坚如磐石的完整性，请将两个表放在一个数据库中，并使用 FK 约束。如果您的父表在另一个数据库中，则没有什么可以阻止任何人从旧备份恢复该父数据库，然后您就会有孤儿。

This is why FK between databases is not supported.

这就是不支持数据库之间 FK 的原因。

回答by Cade Roux

In my experience, the best way to handle this when the primary authoritative source of information for two tables which are related has to be in two separate databases is to sync a copy of the table from the primary location to the secondary location (using T-SQL or SSIS with appropriate error checking - you cannot truncate and repopulate a table while it has a foreign key reference, so there are a few ways to skin the cat on the table updating).

根据我的经验，当两个相关表的主要权威信息源必须位于两个单独的数据库中时，处理此问题的最佳方法是将表的副本从主要位置同步到辅助位置（使用 T-带有适当错误检查的 SQL 或 SSIS - 当表具有外键引用时，您不能截断和重新填充表，因此有几种方法可以在表更新时给猫换皮肤）。

Then add a traditional FK relationship in the second location to the table which is effectively a read-only copy.

然后在表的第二个位置添加一个传统的 FK 关系，这实际上是一个只读副本。

You can use a trigger or scheduled job in the primary location to keep the copy updated.

您可以在主要位置使用触发器或计划作业来保持副本更新。

回答by Camilo J

You could use check constraint with a user defined function to make the check. It is more reliable than a trigger. It can be disabled and reenabled when necessary same as foreign keys and rechecked after a database2 restore.

您可以将检查约束与用户定义的函数一起使用来进行检查。它比触发器更可靠。必要时可以像外键一样禁用和重新启用它，并在 database2 恢复后重新检查。

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)

回答by EBarr

The short answer is that SQL Server (as of SQL 2008) does not support cross database foreign keys--as the error message states.

简短的回答是 SQL Server（从 SQL 2008 开始）不支持跨数据库外键——正如错误消息所述。

While you cannot have declarative referential integrity (the FK), you can reach the same goal using triggers. It's a bit less reliable, because the logic you write may have bugs, but it will get you there just the same.

虽然您不能拥有声明性参照完整性（FK），但您可以使用触发器达到相同的目标。它不太可靠，因为您编写的逻辑可能有错误，但它会让您达到同样的目的。

See the SQL docs @ http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspxWhich state:

请参阅 SQL 文档@ http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx哪个状态：

Triggers are often used for enforcing business rules and data integrity. SQL Server provides declarative referential integrity (DRI) through the table creation statements (ALTER TABLE and CREATE TABLE); however, DRI does not provide cross-database referential integrity. To enforce referential integrity (rules about the relationships between the primary and foreign keys of tables), use primary and foreign key constraints (the PRIMARY KEY and FOREIGN KEY keywords of ALTER TABLE and CREATE TABLE). If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed (fired).

触发器通常用于执行业务规则和数据完整性。SQL Server 通过表创建语句（ALTER TABLE 和 CREATE TABLE）提供声明性引用完整性 (DRI)；但是，DRI 不提供跨数据库引用完整性。要强制参照完整性（关于表的主键和外键之间关系的规则），请使用主键和外键约束（ALTER TABLE 和 CREATE TABLE 的 PRIMARY KEY 和 FOREIGN KEY 关键字）。如果触发器表上存在约束，则会在 INSTEAD OF 触发器执行之后和 AFTER 触发器执行之前检查它们。如果违反约束，则 INSTEAD OF 触发器操作将回滚并且不执行（触发）AFTER 触发器。

There is also an OK discussion over at SQLTeam - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31135

SQLTeam 也有一个好的讨论 - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31135

回答by Jan

As the error message says, this is not supported on sql server. The only way to ensure refrerential integrity is to work with triggers.

正如错误消息所说，这在 sql server 上不受支持。确保参照完整性的唯一方法是使用触发器。