如何查找指向 Oracle 中一条记录的外键依赖关系?
我有一个非常大的 Oracle 数据库,有很多表和数百万行。我需要删除其中之一,但希望确保删除它不会破坏任何其他指向它作为外键记录的依赖行。有没有办法获取指向该行的所有其他记录的列表,或者至少是表模式?我知道我可以尝试自己删除它并捕获异常,但我不会自己运行脚本并需要它第一次运行干净。
我可以使用 Oracle 的 SQL Developer 工具和 AllRoundAutomations 的 PL/SQL Developer。
提前致谢!
I have a very large Oracle database, with many many tables and millions of rows. I need to delete one of them, but want to make sure that dropping it will not break any other dependent rows that point to it as a foreign key record. Is there a way to get a list of all the other records, or at least table schemas, that point to this row? I know that I could just try to delete it myself, and catch the exception, but I won't be running the script myself and need it to run clean the first time through.
I have the tools SQL Developer from Oracle, and PL/SQL Developer from AllRoundAutomations at my disposal.
Thanks in advance!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(8)
这是我列出对表的所有引用的解决方案:
使用此解决方案,您还可以获得哪个表的哪一列引用目标表的哪一列的信息(并且您可以对其进行过滤)。
Here is my solution to list all references to a table:
With this solution you also have the information of which column of which table is referencing which column of your target table (and you can filter on it).
我总是查看起始表的外键并返回。数据库工具通常具有依赖项或约束节点。我知道 PL/SQL Developer 有办法查看 FK,但是我已经有一段时间没有使用它了,所以我无法解释它...
只需将 XXXXXXXXXXXX 替换为表名...
I always look at the Foreign keys for the starting table and work my way back. The DB tools usually have a dependencies or constraints node. I know PL/SQL Developer has a way to see FK's, but it's been a while since I have used it, so I can't explain it...
just replace XXXXXXXXXXXX with a table name...
我最近遇到了类似的问题,但很快就遇到了,找到直接依赖关系是不够的。因此,我编写了一个查询来显示多级外键依赖关系的树:
当在我的数据库中使用 SHIPMENT 作为 MYTABLE 时,它给出这样的结果:
I had a similar problem recently, but experienced soon, that finding the direct dependencies is not enough. So I wrote a query to show a tree of multilevel foreign key dependencies:
It gives a result like this, when using SHIPMENT as MYTABLE in my database:
我们可以使用数据字典来识别引用相关表的主键的表。由此我们可以生成一些动态 SQL 来查询这些表以获取我们想要的值:
这个示例有点作弊。目标主键的名称是硬编码的,并且引用列在所有依赖表上具有相同的名称。解决这些问题留给读者作为练习;)
We can use the data dictionary to identify the tables which reference the primary key of the table in question. From that we can generate some dynamic SQL to query those tables for the value we want to zap:
This example cheats a bit. The name of the target primary key is hardcoded, and the referencing column has the same name on all the dependent tables. Fixing these issues is left as an exercise for the reader ;)
也有过类似的情况。就我而言,我有几条记录,它们最终具有相同的 ID,只是大小写不同。想要检查每个记录存在哪些依赖记录,以了解哪个记录最容易删除/更新
以下内容打印出每个子表指向给定记录的所有子记录,以及每个表/主记录组合的计数
Had a similar situation. In my case I had a couple of records which had ended up with the same ID differing only by case. Wanted to check what dependent records exists for each to know which was easiest to delete/update
The following prints out all child records pointing to the given record, per child table with a count for each table/master record combination
我很惊讶基于外键关系找到表的依赖顺序是多么困难。我需要它,因为我想删除所有表中的数据并再次导入。这是我编写的用于按依赖顺序列出表的查询。我能够使用下面的查询编写删除脚本,并以相反的顺序使用查询结果再次导入。
I was surprised at how difficult it was to find the dependency order of tables based on foreign key relationships. I needed it because I wanted to delete the data from all tables and import it again. Here is the query I wrote to list the tables in dependency order. I was able to script the deletes using the query below, and import again using the results of the query in reverse order.
Oracle 约束使用表索引来引用数据。
要找出哪些表引用了一张表,只需按相反顺序查找索引即可。
观察:禁用引用可显着改善 DML 命令(更新、删除和插入)的时间。
这对批量操作有很大帮助,您知道所有数据都是一致的。
Oracle constraints uses Table Indexes to reference data.
To find out what tables are referencing one table, just look for index in reverse order.
Obs.: Disabling references improves considerably the time of DML commands (update, delete and insert).
This can help a lot in bulk operations, where you know that all data is consistent.