Find Foreign Key Tables in Oracle

1 year ago
William Carter
1 minute

You can use the following SQL query to search for the table containing the foreign key.

SELECT
    UC.CONSTRAINT_NAME AS FOREIGN_KEY_NAME,
    UC.TABLE_NAME AS CHILD_TABLE,
    UCC.COLUMN_NAME AS CHILD_COLUMN,
    UR.TABLE_NAME AS PARENT_TABLE,
    UCR.COLUMN_NAME AS PARENT_COLUMN
FROM
    USER_CONSTRAINTS UC
JOIN
    USER_CONS_COLUMNS UCC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
JOIN
    USER_CONSTRAINTS UR ON UC.R_CONSTRAINT_NAME = UR.CONSTRAINT_NAME
JOIN
    USER_CONS_COLUMNS UCR ON UR.CONSTRAINT_NAME = UCR.CONSTRAINT_NAME
WHERE
    UC.CONSTRAINT_TYPE = 'R'
    AND UC.TABLE_NAME = 'YOUR_TABLE_NAME';

In this query, you will find the names of foreign key constraints in a specified table, as well as the child table name, child table foreign key column name, parent table name, and parent table foreign key column name. You can replace ‘YOUR_TABLE_NAME’ with the table name you want to query for foreign keys.

bannerAds