Add Foreign Key relationship between two Databases

您需要使用触发器跨数据库管理引用约束。

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

编辑: 仅供澄清。这不是执行参照完整性的最佳方法。理想情况下，您希望两个表都位于同一个数据库中，但是如果这是不可能的。那么上面的内容对你来说是一个潜在的工作。

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

简短的回答是，SQLServer (从 SQL2008开始)不支持跨数据库外键——如错误消息所述。

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.

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

触发器通常用于强制执行 业务规则和数据完整性 服务器提供声明性 参照完整性 表创建语句(ALTER 表和创建表) ; 但是，DRI 不提供跨数据库 参照完整性执行 参照完整性 主要和次要之间的关系 表的外键) ，使用主键 和外键约束(外键约束) 主关键字和外来关键字 和创建表)。如果 constraints exist on the trigger 表后，将对它们进行检查 INSTEADOF 触发器执行和优先级 到 AFTER 触发器执行 如果违反了约束，则 INSTEAD 回滚触发器操作，并且 the AFTER trigger is not executed (开火)。

在 SQLTeam-http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31135上还有一个 OK 讨论

如果需要坚如磐石的完整性，请在一个数据库中同时包含两个表，并使用 FK 约束。如果您的父表位于另一个数据库中，没有什么可以阻止任何人从旧备份还原该父数据库，那么您就有了孤儿。

This is why FK between databases is not supported.

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).

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

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

您可以在用户定义的函数中使用检查约束来进行检查。它比触发器更可靠。可以在必要时禁用和重新启用它，与外键相同，并在执行 datase2恢复之后重新检查。

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)

通过数据库实现参照完整性是 不是件容易的事。

以下是经常使用的机制清单:

• 克隆 & 同步: 引用资料定期被克隆/合并到 参考数据库中。如果引用的数据很少更改，这可能是合适的。您最终会得到相同数据的两个物理副本，并且需要一个可靠的进程来保持它们的同步(例如，使用 ETL 管道)。
• Triggers: Changes to the 参考数据 and the 引用资料 are caught by SQL triggers, which ensure referential integrity. However, triggers can be slow, and may not fire at a database restore. It cannot hurt to run scheduled consistency checks as part of the operations monitoring. Write access to the referenced database is required for installing and maintaining the trigger.
• Check constraints: SQL-Server offers user-defined contraints, which ensure that every row satisfies a given condition. One can exploit this functionality by writing a user defined function that checks the existence of a row in the 引用资料, and then use this function as a CHECK's predicate in the referencing table. This does not catch changes in the referenced data. It is an RDBMS-specific solution, but works accross server boundaries (e.g. using linked servers). It is a good choice for referencing globally unique IDs, such as article codes in a company's ERP system, which never get deleted or re-assigned.
• 重新考虑数据库体系结构: 当上述所有机制都不能令人满意时，多个数据库可能会合并到单个数据库中。原始数据库名称可以成为模式名称，从而允许对数据库对象进行有效的分组。