blocks|key|1294623|text|我总是查看开始表的外键，然后返回。DB工具通常有一个依赖或约束节点。我知道PL/SQL+Developer有办法查看FK，但我已经有一段时间没有使用它了，所以我无法解释它……|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1294624|只需将XXXXXXXXXXXX替换为表名...|1294625|/*+The+following+query+lists+all+relationships+*/+

select
+a.owner%7C%7C'.'%7C%7Ca.table_name+"Referenced+Table"
,b.owner%7C%7C'.'%7C%7Cb.table_name+"Referenced+by"
,b.constraint_name+"Foreign+Key"
from+all_constraints+a,+all_constraints+b+
where+
b.constraint_type+=+'R'
and+a.constraint_name+=+b.r_constraint_name+
and+b.table_name='XXXXXXXXXXXX'+--+Table+name+
order+by+a.owner%7C%7C'.'%7C%7Ca.table_name|code-block|syntax|javascript|1294626|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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...

<pre><code>/* The following query lists all relationships */ 

select
 a.owner||'.'||a.table_name "Referenced Table"
,b.owner||'.'||b.table_name "Referenced by"
,b.constraint_name "Foreign Key"
from all_constraints a, all_constraints b 
where 
b.constraint_type = 'R'
and a.constraint_name = b.r_constraint_name 
and b.table_name='XXXXXXXXXXXX' -- Table name 
order by a.owner||'.'||a.table_name
</code></pre>

blocks|key|1079745|text|我们可以使用数据字典来标识引用相关表的主键的表。在此基础上，我们可以生成一些动态SQL来查询这些表，以获得我们想要转换的值：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1079746|SQL>+declare
++2++++++n+pls_integer;
++3++++++tot+pls_integer+:=+0;
++4++begin
++5++++++for+lrec+in+(+select+table_name+from+user_constraints
++6++++++++++++++++++++where+r_constraint_name+=+'T23_PK'+)
++7++++++loop
++8++++++++++execute+immediate+'select+count(*)+from+'%7C%7Clrec.table_name
++9++++++++++++++++++++++++++++++%7C%7C'+where+col2+=+:1'+into+n+using+&&target_val;
+10++++++++++if+n+=+0+then
+11++++++++++++++dbms_output.put_line('No+impact+on+'%7C%7Clrec.table_name);
+12++++++++++else
+13++++++++++++++dbms_output.put_line('Uh+oh!+'%7C%7Clrec.table_name%7C%7C'+has+'%7C%7Cn%7C%7C'+hits!');
+14++++++++++end+if;
+15++++++++++tot+:=+tot+%2B+n;
+16++++++end+loop;
+17++++++if+tot+=+0
+18++++++then
+19++++++++++delete+from+t23+where+col2+=+&&target_val;
+20++++++++++dbms_output.put_line('row+deleted!');
+21++++++else
+22++++++++++dbms_output.put_line('delete+aborted!');
+23++++++end+if;
+24++end;
+25++/
Enter+value+for+target_val:+6
No+impact+on+T34
Uh+oh!+T42+has+2+hits!
No+impact+on+T69
delete+aborted!

PL/SQL+procedure+successfully+completed.

SQL>|code-block|syntax|javascript|1079747|这个例子有点作弊。目标主键的名称是硬编码的，并且引用列在所有依赖表上具有相同的名称。解决这些问题留给读者作为练习;)|1079748|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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:

<pre><code>SQL&gt; declare
 2 n pls_integer;
 3 tot pls_integer := 0;
 4 begin
 5 for lrec in ( select table_name from user_constraints
 6 where r_constraint_name = 'T23_PK' )
 7 loop
 8 execute immediate 'select count(*) from '||lrec.table_name
 9 ||' where col2 = :1' into n using &amp;&amp;target_val;
 10 if n = 0 then
 11 dbms_output.put_line('No impact on '||lrec.table_name);
 12 else
 13 dbms_output.put_line('Uh oh! '||lrec.table_name||' has '||n||' hits!');
 14 end if;
 15 tot := tot + n;
 16 end loop;
 17 if tot = 0
 18 then
 19 delete from t23 where col2 = &amp;&amp;target_val;
 20 dbms_output.put_line('row deleted!');
 21 else
 22 dbms_output.put_line('delete aborted!');
 23 end if;
 24 end;
 25 /
Enter value for target_val: 6
No impact on T34
Uh oh! T42 has 2 hits!
No impact on T69
delete aborted!

PL/SQL procedure successfully completed.

SQL&gt;
</code></pre>

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 ;)

blocks|key|1294753|text|下面是我的解决方案，列出一个表的所有引用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1294754|select
++src_cc.owner+as+src_owner,
++src_cc.table_name+as+src_table,
++src_cc.column_name+as+src_column,
++dest_cc.owner+as+dest_owner,
++dest_cc.table_name+as+dest_table,
++dest_cc.column_name+as+dest_column,
++c.constraint_name
from
++all_constraints+c
inner+join+all_cons_columns+dest_cc+on
++c.r_constraint_name+=+dest_cc.constraint_name
++and+c.r_owner+=+dest_cc.owner
inner+join+all_cons_columns+src_cc+on
++c.constraint_name+=+src_cc.constraint_name
++and+c.owner+=+src_cc.owner
where
++c.constraint_type+=+'R'
++and+dest_cc.owner+=+'MY_TARGET_SCHEMA'
++and+dest_cc.table_name+=+'MY_TARGET_TABLE'
++--and+dest_cc.column_name+=+'MY_OPTIONNAL_TARGET_COLUMN'
;|code-block|syntax|javascript|1294755|使用这个解决方案，您还可以了解哪个表的哪一列正在引用您的目标表的哪一列(并且您可以对其进行过滤)。|1294756|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Here is my solution to list all references to a table:

<pre><code>select
 src_cc.owner as src_owner,
 src_cc.table_name as src_table,
 src_cc.column_name as src_column,
 dest_cc.owner as dest_owner,
 dest_cc.table_name as dest_table,
 dest_cc.column_name as dest_column,
 c.constraint_name
from
 all_constraints c
inner join all_cons_columns dest_cc on
 c.r_constraint_name = dest_cc.constraint_name
 and c.r_owner = dest_cc.owner
inner join all_cons_columns src_cc on
 c.constraint_name = src_cc.constraint_name
 and c.owner = src_cc.owner
where
 c.constraint_type = 'R'
 and dest_cc.owner = 'MY_TARGET_SCHEMA'
 and dest_cc.table_name = 'MY_TARGET_TABLE'
 --and dest_cc.column_name = 'MY_OPTIONNAL_TARGET_COLUMN'
;
</code></pre>

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).

blocks|key|1294799|text|我最近遇到了一个类似的问题，但很快就体验到了，找到直接依赖项是不够的。因此，我编写了一个查询来显示多级外键依赖关系树：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1294800|SELECT+LPAD('+',4*(LEVEL-1))+%7C%7C+table1+%7C%7C+'+<--+'+%7C%7C+table2+tables,+table2_fkey
FROM
++(SELECT+a.table_name+table1,+b.table_name+table2,+b.constraint_name+table2_fkey
++FROM+user_constraints+a,+user_constraints+b+
++WHERE+a.constraint_type+IN('P',+'U')+
++AND+b.constraint_type+=+'R'+
++AND+a.constraint_name+=+b.r_constraint_name+
++AND+a.table_name+!=+b.table_name
++AND+b.table_name+<>+'MYTABLE')
CONNECT+BY+PRIOR++table2+=+table1+AND+LEVEL+<=+5
START+WITH+table1+=+'MYTABLE';|code-block|syntax|javascript|1294801|当在我的数据库中使用using作为MYTABLE时，它会给出这样的结果：|1294802|SHIPMENT+<--+ADDRESS
SHIPMENT+<--+PACKING_LIST
++++PACKING_LIST+<--+PACKING_LIST_DETAILS
++++PACKING_LIST+<--+PACKING_UNIT
++++++++PACKING_UNIT+<--+PACKING_LIST_ITEM
++++PACKING_LIST+<--+PO_PACKING_LIST
...|1294803|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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:

<pre><code>SELECT LPAD(' ',4*(LEVEL-1)) || table1 || ' &lt;-- ' || table2 tables, table2_fkey
FROM
 (SELECT a.table_name table1, b.table_name table2, b.constraint_name table2_fkey
 FROM user_constraints a, user_constraints b 
 WHERE a.constraint_type IN('P', 'U') 
 AND b.constraint_type = 'R' 
 AND a.constraint_name = b.r_constraint_name 
 AND a.table_name != b.table_name
 AND b.table_name &lt;&gt; 'MYTABLE')
CONNECT BY PRIOR table2 = table1 AND LEVEL &lt;= 5
START WITH table1 = 'MYTABLE';
</code></pre>

It gives a result like this, when using SHIPMENT as MYTABLE in my database:

<pre><code>SHIPMENT &lt;-- ADDRESS
SHIPMENT &lt;-- PACKING_LIST
 PACKING_LIST &lt;-- PACKING_LIST_DETAILS
 PACKING_LIST &lt;-- PACKING_UNIT
 PACKING_UNIT &lt;-- PACKING_LIST_ITEM
 PACKING_LIST &lt;-- PO_PACKING_LIST
...
</code></pre>

blocks|key|1294855|text|我惊讶地发现，根据外键关系查找表的依赖顺序是多么困难。我需要它，因为我想从所有表中删除数据，然后再次导入。下面是我编写的按依赖关系顺序列出表的查询。我能够使用下面的查询编写删除脚本，并使用查询的结果以相反的顺序再次导入。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1294856|+++SELECT+referenced_table
+++++++++,MAX(lvl)+for_deleting
+++++++++,MIN(lvl)+for_inserting
+++FROM
+++++++++(+--+Hierarchy+of+dependencies
+++++++++SELECT+LEVEL+lvl
+++++++++++++++,t.table_name+referenced_table
+++++++++++++++,b.table_name+referenced_by
+++++++++FROM+user_constraints+A
+++++++++JOIN+user_constraints+b+
+++++++++++++++ON++A.constraint_name+=+b.r_constraint_name
+++++++++++++++and+b.constraint_type+=+'R'
+++++++++RIGHT+JOIN+user_tables+t
+++++++++++++++ON++t.table_name+=+A.table_name
+++++++++START+WITH+b.table_name+IS+NULL
+++++++++CONNECT+BY+b.table_name+=+PRIOR+t.table_name
+++++++++)
+++GROUP+BY+referenced_table
+++ORDER+BY+for_deleting,+for_inserting;|code-block|syntax|javascript|1294857|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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. 

<pre><code> SELECT referenced_table
 ,MAX(lvl) for_deleting
 ,MIN(lvl) for_inserting
 FROM
 ( -- Hierarchy of dependencies
 SELECT LEVEL lvl
 ,t.table_name referenced_table
 ,b.table_name referenced_by
 FROM user_constraints A
 JOIN user_constraints b 
 ON A.constraint_name = b.r_constraint_name
 and b.constraint_type = 'R'
 RIGHT JOIN user_tables t
 ON t.table_name = A.table_name
 START WITH b.table_name IS NULL
 CONNECT BY b.table_name = PRIOR t.table_name
 )
 GROUP BY referenced_table
 ORDER BY for_deleting, for_inserting;
</code></pre>

blocks|key|1294903|text|也有类似的情况。在我的例子中，我有两个记录，它们最终都有相同的ID，只是大小写不同。我想检查存在哪些从属记录，以便了解哪些记录最容易删除/更新|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1294904|下面打印出每个子表中所有指向给定记录的子记录，以及每个表/主记录组合的计数|1294905|declare
++--
++--+Finds+and+prints+out+how+many+children+there+are+per+table+and+value+for+each+value+of+a+given+field
++--

++--+Name+of+the+table+to+base+the+query+on++
++cTable++++++constant+varchar2(20)+:=+'FOO';
++--+Name+of+the+column+to+base+the+query+on
++cCol++++++++constant+varchar2(10)+:=+'ID';
++--+Cursor+to+find+interesting+values+(e.g.+duplicates)+in+master+table
++cursor+cVals+is
++++select+id+
++++from+foo+f
++++where+exists+(++select+1+from+foo+f2+
++++++++++++++++++++where+upper(f.id)+=+upper(f2.id)
++++++++++++++++++++and+f.rowid+!=+f2.rowid+);

++--+Everything+below+here+should+just+work
++vNum++++++++number(18,0);
++vSql++++++++varchar2(4000);

++cOutColSize+++number(2,0)+:=+30;

++cursor+cReferencingTables+is
++++select
++++++consChild.table_name,
++++++consChild.constraint_name,
++++++colChild.column_name+++++
++++from+user_constraints+consMast
++++inner+join+user_constraints+consChild+on+consMast.constraint_name+=+consChild.r_constraint_name++
++++inner+join+USER_CONS_COLUMNS+colChild+on+consChild.CONSTRAINT_NAME+=+colChild.CONSTRAINT_NAME
++++inner+join+USER_CONS_COLUMNS+colMast+on+colMast.CONSTRAINT_NAME+=+consMast.CONSTRAINT_NAME
++++where+consChild.constraint_type+=+'R'++++++
++++++and+consMast.table_name+=+cTable
++++++and+colMast.column_name+=+cCol
++++order+by+consMast.table_name,+consChild.table_name;
begin

++dbms_output.put_line(
++++rpad('Table',+cOutColSize)+%7C%7C+
++++rpad('Column',+cOutColSize)+%7C%7C+
++++rpad('Value',+cOutColSize)+%7C%7C+
++++rpad('Number',+cOutColSize)
++);
++for+rRef+in+cReferencingTables+loop
++++for+rVals+in+cVals+loop
++++++vSql+:=+'select+count(1)+from+'+%7C%7C+rRef.table_name+%7C%7C+'+where+'+%7C%7C+rRef.column_name+%7C%7C+'+=+'''+%7C%7C+rVals.id+%7C%7C+'''';
++++++execute+immediate+vSql+into+vNum;
++++++if+vNum+>+0+then
++++++++dbms_output.put_line(
++++++++++rpad(rRef.table_name,+cOutColSize)+%7C%7C+
++++++++++rpad(rRef.column_name,+cOutColSize)+%7C%7C+
++++++++++rpad(rVals.id,+cOutColSize)+%7C%7C+
++++++++++rpad(vNum,+cOutColSize)+);
++++++end+if;
++++end+loop;
++end+loop;
end;|code-block|syntax|javascript|1294906|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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

<pre><code>declare
 --
 -- Finds and prints out how many children there are per table and value for each value of a given field
 --

 -- Name of the table to base the query on 
 cTable constant varchar2(20) := 'FOO';
 -- Name of the column to base the query on
 cCol constant varchar2(10) := 'ID';
 -- Cursor to find interesting values (e.g. duplicates) in master table
 cursor cVals is
 select id 
 from foo f
 where exists ( select 1 from foo f2 
 where upper(f.id) = upper(f2.id)
 and f.rowid != f2.rowid );

 -- Everything below here should just work
 vNum number(18,0);
 vSql varchar2(4000);

 cOutColSize number(2,0) := 30;

 cursor cReferencingTables is
 select
 consChild.table_name,
 consChild.constraint_name,
 colChild.column_name 
 from user_constraints consMast
 inner join user_constraints consChild on consMast.constraint_name = consChild.r_constraint_name 
 inner join USER_CONS_COLUMNS colChild on consChild.CONSTRAINT_NAME = colChild.CONSTRAINT_NAME
 inner join USER_CONS_COLUMNS colMast on colMast.CONSTRAINT_NAME = consMast.CONSTRAINT_NAME
 where consChild.constraint_type = 'R' 
 and consMast.table_name = cTable
 and colMast.column_name = cCol
 order by consMast.table_name, consChild.table_name;
begin

 dbms_output.put_line(
 rpad('Table', cOutColSize) || 
 rpad('Column', cOutColSize) || 
 rpad('Value', cOutColSize) || 
 rpad('Number', cOutColSize)
 );
 for rRef in cReferencingTables loop
 for rVals in cVals loop
 vSql := 'select count(1) from ' || rRef.table_name || ' where ' || rRef.column_name || ' = ''' || rVals.id || '''';
 execute immediate vSql into vNum;
 if vNum &gt; 0 then
 dbms_output.put_line(
 rpad(rRef.table_name, cOutColSize) || 
 rpad(rRef.column_name, cOutColSize) || 
 rpad(rVals.id, cOutColSize) || 
 rpad(vNum, cOutColSize) );
 end if;
 end loop;
 end loop;
end;
</code></pre>

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!

How to find foreign-key dependencies pointing to one record in Oracle?

Oracle数据库

数据库

我有一个非常大的Oracle数据库，有许多许多表和数百万行。我需要删除其中的一个，但要确保删除它不会破坏指向它作为外键记录的任何其他依赖行。有没有办法获得指向此行的所有其他记录的列表，或者至少是表模式？我知道我可以尝试自己删除它，并捕获异常，但我不会自己运行脚本，需要它第一次运行干净。我有来自Oracle的工具SQL ...

问如何在Oracle中查找指向一条记录的外键依赖项？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中查找指向一条记录的外键依赖项？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中查找指向一条记录的外键依赖项？
EN