Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Identify Foreign Keys Column

Ajay GandhiSep 6 2016 — edited Sep 7 2016

Hi,

I have been presented with a little bit of challenge. I came across an oracle database with roughly 50 tables. Unfortunately, whoever created this database did not bother to create foreign keys at the database level. Somehow the data integrity was maintained at the front-end/application layer. My job is to identify the possible foreign keys that can be created with this 50 columns. I know for sure that the first column (column_id = 1) is the Primary key on all tables.

The foreign key column name in the child table are not too user-friendly. The only way to predict which column could be a potential child column is by looking at the column data. I am thinking of looping through all the 50 tables and take the first column PK and then start checking the data against all other 49 tables-columns and identify which column has the related data and create a report of something like this:

Table 1 -> Col1 (PK) ==> Table 3 -> Col 4 (child)

Table 2 -> Col1 (PK) ==> Table 9 -> Col 6 (child), Table 10 -> Col 7 (child)

I am looking for a SQL or PLSQL that can identify the output below.

After that it will be much easier for me to start creating foreign keys. Right now, its a trial and error and it is becoming tedious.

Thanks

AJ

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 5 2016
Added on Sep 6 2016
7 comments
647 views