SQL Server 2008中的外键指向另一个数据库
如何添加引用另一个数据库中的外部列的约束？
前段时间我读到它可以通过链接服务器来完成，其他人则说可以通过触发器来完成。如果可能的话，首选的方法是什么？
谢谢！
How can I add a constraint that references a foreign column from another database?
Some time ago I read that it can be done with linked server and others say with triggers. What's the preferred way of doing this if that's possible at all?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
链接服务器将无法工作。
FK 必须指向本地表。
首选的方法是不这样做，尽管您可以采取一些技巧来实现这一点。
例如，您可以使用触发器导致创建/检查引用，但我不认为这相当于 FK 约束。
相关问题：
您可以在 SQLServer 2k5 中的链接服务器表的视图上使用外键吗？
Linked servers will not work.
FK's must point to local tables.
The preferred way is not to do this, though you can pull a few hacks to make it happen.
For example you could have triggers cause the reference to be created/checked but I wouldn't consider that equivalent to a FK constraint.
Related question:
Can you have a Foreign Key onto a View of a Linked Server table in SQLServer 2k5?
使用替代触发器（您可能需要也可能不需要链接服务器，具体取决于其他数据库是否位于不同的服务器上）。确保触发器可以处理多行插入/更新/删除。我还建议将检查失败的记录移至异常表。
Use an instead of trigger (you may or may not need linked servers depending on whether the other database is on a differnt server). Make sure the trigger can handle multiple row inserts/updates/deletes. I'd also suggest moving the records that fail the check to an exception table.
外键不能跨越数据库边界。如果你尝试这样做，你会得到：
如果您需要在两个单独的数据库之间强制执行某种关系，那么是的 - 您可能需要一个链接服务器（如果第二个数据库位于第二个服务器上），并且可能需要触发器 - 但所有这些东西都很难获得是的，效率很低，而且很容易出错。
您可以执行此操作的一种方法是数据复制 - 将要引用的表复制到源数据库中，然后与该复制的表建立外键关系。但这永远不会是完全“实时”和“实时”的——数据复制也会有一点滞后。
Foreign keys cannot go across database boundaries. If you try to do this, you'll get:
If you need to enforce some kind of relationship between two separate databases, then yes - you might need a linked server (if that second database is on a second server), and possibly triggers - but all of those things will be very hard to get right, very inefficient and very error prone.
One way you might be able to do this would be data replication - replicate the table you want to reference into your source database, and then establish a foreign key relationship with that replicated table. But that will never be quite "live" and "real-time" - there will also be a bit of a lag in the data replication.