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