oracle 我可以跨数据库创建外键吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2972957/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can I create Foreign Keys across Databases?
提问by Amir
We have 2 databases - DB1 & DB2.
我们有 2 个数据库 - DB1 和 DB2。
Can I create a table in DB1 that has a relation with one of the tables in DB2? In other words, can I have a Foreign Key in my table from another database?
我可以在 DB1 中创建一个与 DB2 中的一个表有关系的表吗？换句话说，我的表中可以有来自另一个数据库的外键吗？
I connect to these databases with different users. Any ideas?
我用不同的用户连接到这些数据库。有任何想法吗？
Right now, I receive the error:
现在，我收到错误消息：
ORA-00942:Table or view does not exist
ORA-00942:表或视图不存在
回答by Tony Andrews
No, Oracle does not allow you to create a foreign key constraint that references a table via a database link. You would have to use triggers to enforce the integrity.
不，Oracle 不允许您创建通过数据库链接引用表的外键约束。您将不得不使用触发器来强制执行完整性。
回答by Jim Hudson
One way to deal with this would be to create a materialized view of the master table on the local database, then create the integrity constraint pointing to the MV.
处理此问题的一种方法是在本地数据库上创建主表的物化视图，然后创建指向 MV 的完整性约束。
That works. But it can lead to some problems. First, if you ever need to do a complete refresh of the materialized view, you'll need to disable the constraint before doing do. Otherwise, Oracle won't be able to delete the rows in the MV before bringing in the new rows.
那个有效。但这会导致一些问题。首先，如果您需要完全刷新物化视图，则需要在执行操作之前禁用约束。否则，Oracle 将无法在引入新行之前删除 MV 中的行。
Second, you may run into some timing delays. For example say you add a record to the master table on the remote site. Then you want to add a child record to the local table. But the MV is set to refresh daily and that hasn't happened yet. You'll get a foreign key violation, simply because the MV hasn't refreshed.
其次，您可能会遇到一些时间延迟。例如，假设您向远程站点的主表添加了一条记录。然后你想在本地表中添加一个子记录。但是 MV 设置为每天刷新，而这还没有发生。你会得到一个外键冲突，仅仅是因为 MV 没有刷新。
If you go this route, your safest approach is to set the MV to fast refresh on commit of the master table. That'll mean keeping a DB Link open nearly all the time. And you'll have admin work to do if you ever need to do a complete refresh.
如果你走这条路，你最安全的方法是将 MV 设置为在主表提交时快速刷新。这意味着几乎一直保持 DB Link 处于打开状态。如果您需要完全刷新，您将有管理工作要做。
All in all, we've generally found that a trigger is easier. In some cases, we've simply defined the FK in our logical model but implemented it manually by setting up a daily job that will check for violations and alert staff. Of course, we're pretty careful so those alerts are exceedingly rare.
总而言之，我们通常发现触发器更容易。在某些情况下，我们只是在我们的逻辑模型中定义了 FK，但通过设置日常工作来手动实现它，该工作将检查违规并提醒员工。当然，我们非常小心，因此这些警报非常罕见。
