SQL Query to Get Tables Which are Not Having Index on Foreign Key(FK) Column

To identify tables in an Oracle database that do not have an index on their foreign key columns, you can use the USER_CONSTRAINTS and USER_INDEXES views. Here's a SQL query to retrieve such information 

 We are querying the USER_CONSTRAINTS view to get information about constraints in the current user's schema. Specifically, we focus on constraints with CONSTRAINT_TYPE = 'R', which indicates foreign key constraints. 

 We perform a LEFT JOIN with the USER_INDEXES view, which contains information about indexes in the current user's schema. 

 The join condition is based on the R_CONSTRAINT_NAME from USER_CONSTRAINTS and the INDEX_NAME from USER_INDEXES. 

 The WHERE clause filters the results to show only foreign key constraints (CONSTRAINT_TYPE = 'R') that do not have an associated index (UI.INDEX_NAME IS NULL).

advertisements

 

SELECT
  UC.TABLE_NAME AS table_name,
  UC.CONSTRAINT_NAME AS foreign_key_name
FROM
  USER_CONSTRAINTS UC
LEFT JOIN
  USER_INDEXES UI
ON
  UC.R_CONSTRAINT_NAME = UI.INDEX_NAME
WHERE
  UC.CONSTRAINT_TYPE = 'R'
  AND UI.INDEX_NAME IS NULL;

Sample Output

TABLE_NAME   | FOREIGN_KEY_NAME
-------------|-----------------
EMPLOYEES    | FK_DEPT_ID
ORDERS       | FK_CUSTOMER_ID

In the sample output, the query identifies two tables, EMPLOYEES and ORDERS, that have foreign key constraints without an associated index on their respective foreign key columns (FK_DEPT_ID and FK_CUSTOMER_ID).