跨数据库边界的 SQL Server 外键 - 实施技术 [英] SQL Server Foreign Keys across database boundaries - techniques for enforcement

问题描述

我有两个独立的 SQL Server 2005 数据库(在同一台服务器上)

I have two separate SQL Server 2005 databases (on the same server)

• 安全数据库

• 主应用数据库

• security database

• main application database

安全数据库有一个用户表，其中包含进行身份验证所需的一切.-

The security database has a user table with everything needed to authenticate. -

我想在用户和人员表之间强制执行映射.我假设外键不能跨数据库映射，因此我想知道如何执行关系的完整性.

I want to enforce a mapping between the user and the person table. I'm assuming that foreign keys can't be mapped across databases thus I am wondering what to do to enforce the integrity of the relationship.

推荐答案

确实不支持跨库外键

    Msg 1763, Level 16, State 0, Line 2
    Cross-database foreign key references are not supported.

如果你真的想在数据库端强制执行参照完整性，你将不得不依赖触发器.(我不推荐)

If you really want to enforce the referential integrity on the database side you will have to rely on triggers. (which I don't recommend)

为了使您的代码更易于维护，您可以为要检查参照完整性的表创建同义词.

to make your code more maintainable you could create synonyms for the tables you want to check referential integrity on.

      CREATE SYNONYM myTable FOR otherdatabase.dbo.myTable;

这将使手动"检查更容易，因为您不能在同义词上创建外键.

This would be to make the "manual" checks easier, as you can not create foreign keys on a synonym.

这篇关于跨数据库边界的 SQL Server 外键 - 实施技术的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！