0

In out DB we have a table called "AGENCY" with AGENCY_ID as primary key (PK). There are also about 30 tables that use AGENCY_ID as a foreign key (FK) which references PK in "AGENCY" table.

Is there a way to count how many times a specific PK value AGENCY_ID (i.e. 1004) is referenced as FK in all of the 30 "referencing" tables without checking each individual linked table.

Referential Integrity exception gets thrown when you try to delete row with PK referenced in another table, so I assume there must be a way to check if references in other tables exist.

I tried looking at all_constraints and all_tab_columns tables, but they do not solve the problem. Any ideas how to solve it? Thanks

ukie
  • 188
  • 2
  • 15
  • 1
    You mean looking at the actual AGENCY_ID value? No, you would have to query those 30 tables. Write a script. – OldProgrammer Dec 30 '14 at 16:22
  • 1
    Question is nearly answered if you know this : http://www.techonthenet.com/oracle/questions/find_pkeys.php - Also this : http://stackoverflow.com/questions/1729996/list-of-foreign-keys-and-the-tables-they-reference – tvCa Dec 30 '14 at 18:26
  • thanks @tvCa, it is somewhat helpful, but we are interested in the actual PK value being referenced, and not so much the PK info itself – ukie Jan 07 '15 at 15:30

1 Answers1

0

I don't think any data dictionary view is available for achieving your goal but you should need to create procedure or function to achieve this goal. Without this you cannot get result of PK value AGENCY_ID (i.e. 1004) referenced or not if referenced then how many times.

doc123
  • 106
  • 6
  • thanks, @doc123. So the procedure would have to loop through the data dictionary of FK tables/columns and look/compare the value? – ukie Jan 07 '15 at 15:29