我可以跨数据库创建外键吗？
我们有 2 个数据库 - DB1 和 DB1。 DB2。
我可以在 DB1 中创建一个与 DB2 中的表之一有关系的表吗？ 换句话说，我的表中可以有来自另一个数据库的外键吗？
我用不同的用户连接到这些数据库。 有什么想法吗？
现在，我收到错误：
ORA-00942: 表或视图不存在
We have 2 databases - 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?
I connect to these databases with different users.
Any ideas?
Right now, I receive the error:
ORA-00942:Table or view does not exist
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不可以，Oracle 不允许您创建通过数据库链接引用表的外键约束。您必须使用触发器来强制完整性。
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.
处理这个问题的一种方法是在本地数据库上创建主表的物化视图，然后创建指向 MV 的完整性约束。
那行得通。但这可能会导致一些问题。首先，如果您需要对物化视图进行完全刷新，则需要在执行操作之前禁用约束。否则，Oracle 将无法在引入新行之前删除 MV 中的行。
其次，您可能会遇到一些时间延迟。例如，假设您向远程站点上的主表添加一条记录。然后您想向本地表添加一条子记录。但MV每天都会更新，但这还没有发生。你会遇到外键违规，只是因为 MV 尚未刷新。
如果您走这条路，最安全的方法是将 MV 设置为在主表提交时快速刷新。这意味着数据库链接几乎始终保持打开状态。如果您需要进行彻底的刷新，您将需要执行管理工作。
总而言之，我们通常发现触发器更容易。在某些情况下，我们只是在逻辑模型中定义了 FK，但通过设置日常作业来手动实现它，该作业将检查违规行为并提醒员工。当然，我们非常小心，因此这些警报非常罕见。
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.
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.
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.
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.
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.