Finding "Virtual" Foreign Keys

Question

I'm performing an Oracle-based data load, and an app that is accessing the database is enforcing some virtual foreign keys that the database doesn't enforce via the ALL_CONS_COLUMNS table. Since the app is a black box (meaning I don't have access to the specs or source code), I'm forced to go hunting for these "possible" keys by getting a list of all the primary keys in the DB, and then finding all tables that have the same combination of fields present in their structure. I'm trying to find an efficient and automated way to do this, especially considering the database is huge in size. Any ideas?

Answer 1

Here's something to get started with:

WITH cteIndexes AS (select ui.TABLE_NAME, ui.INDEX_NAME, ui.INDEX_TYPE, ui.UNIQUENESS,
                           ic.COLUMN_NAME, ic.COLUMN_POSITION
                      from user_indexes ui
                      INNER JOIN USER_IND_COLUMNS ic
                        ON ic.TABLE_NAME = ui.TABLE_NAME
                      WHERE ui.uniqueness = 'UNIQUE'),
     cteCandidate_keys AS (SELECT c.TABLE_NAME, c.COLUMN_NAME, c.COLUMN_ID
                             FROM cteIndexes i
                             INNER JOIN USER_TAB_COLUMNS c
                               ON c.COLUMN_NAME = i.COLUMN_NAME
                             WHERE c.TABLE_NAME <> i.TABLE_NAME
                             ORDER BY c.TABLE_NAME, c.COLUMN_ID)
SELECT 'UNIQUE KEYS' AS TABLE_NAME, NULL AS COLUMN_NAME,
       NULL AS COLUMN_POSITION, NULL AS COLUMN_ID
  FROM DUAL
UNION ALL
SELECT TABLE_NAME, COLUMN_NAME, NULL, NULL
  FROM cteIndexes
UNION ALL
SELECT NULL, NULL, NULL, NULL FROM DUAL
UNION ALL
SELECT 'CANDIDATE FOREIGN KEYS', NULL, NULL, NULL FROM DUAL
UNION ALL
SELECT TABLE_NAME, COLUMN_NAME, NULL, COLUMN_ID
  FROM cteCandidate_keys

Fold, spindle, and mutilate as needed. :-)

dbfiddle here