Foreign Key Index Check

What is a Foreign Key? It is a point in one table that links to a Primary Key(PK) of another table. The relationship between these tables is also called a Parent-Child relationship or Foreign Key Relationship. The table with PK is a Parent and the table with Foreign Key is a Child. The purpose of FK is to maintain the data Integrity. Data which is not in Parent table cannot be inserted into a Child table, this is how Foreign Key protects the data integrity in a Database.
List all parents and their children:

 SELECT p.owner parent_owner,
   p.constraint_type AS parent_constraint_type,
   p.table_name PARENT_TABLE,
   c.owner child_owner,
   c.table_name CHILD_TABLE,
   c.constraint_type AS child_constraint_type
 FROM dba_constraints p,
   dba_constraints c
 WHERE (p.constraint_type = 'P'
 OR p.constraint_type     = 'U')
 AND c.constraint_type    = 'R'
 AND p.constraint_name    = c.r_constraint_name;

Who is your parent?

 
 SELECT p.owner parent_owner,
   p.constraint_type AS parent_constraint_type,
   p.table_name PARENT_TABLE,
   c.owner child_owner,
   c.table_name CHILD_TABLE,
   c.constraint_type AS child_constraint_type
 FROM dba_constraints p,
   dba_constraints c
 WHERE (p.constraint_type = 'P'
 OR p.constraint_type     = 'U')
 AND c.constraint_type    = 'R'
 AND p.constraint_name    = c.r_constraint_name
 AND c.table_name = UPPER('&CHILD_table_name_goes_here');

Who is your child?

 SELECT p.owner parent_owner,
   p.constraint_type AS parent_constraint_type,
   p.table_name PARENT_TABLE,
   c.owner child_owner,
   c.table_name CHILD_TABLE,
   c.constraint_type AS child_constraint_type
 FROM dba_constraints p,
   dba_constraints c
 WHERE (p.constraint_type = 'P'
 OR p.constraint_type     = 'U')
 AND c.constraint_type    = 'R'
 AND p.constraint_name    = c.r_constraint_name
 AND p.table_name = UPPER('&PARENT_table_name_goes_here');

Finding Foreign Key columns with missing Index; Why are we concerns with this? who cares? what impact can it make? Unlike some other SQL databases, Oracle does not automatically create Indexes on the "child" (in formal language "referencing") side of a foreign key. There are some good reasons for this (see below), but it does give users another opportunity to forget something, which is indexing the foreign keys (FKs) that need it. But which ones need it? Well, fortunately, you can interrogate the system views and find out.

I have added a query for finding foreign keys without Indexes. Now, in order to understand how to interpret this, you have to understand why you would or would not have an index on an FK, and what sort of indexes are valid. There's two times that indexes on the child side of FKs are used: when doing JOIN and lookup queries using the FK column when updating or deleting a row from the "parent" table. The second occasion is news to some DBAs. The way it works is this: before letting you delete or change a row in the "parent" table, Oracle has to verify that there are no rows in the "child" table referencing the FK value that might be going away. If there are, it needs to perform the action you have defined (such as CASCADE, SET NULL or RESTRICT). If the "child" table is large, this can be substantially speeded up by having an index on the FK column.

This means that it's important to have an index on the child side of the FK if any of the following are true:
The child table is large and the parent table gets updates/deletes.
The parent table is large and the FK is used for JOINs.

The child table is large and the FK is used to filter (WHERE clause) records on the child table This means most FKs, but not all of them. If both tables are small, or if the parent table is small and the FK is used only to prevent bad data entry, then there's no reason to index it. Also, if the FK is very low cardinality (like, say, only four possible values) then it's probably also a waste of resources to Index it.
Query to find FK columns with missing Indexes:

SELECT
  table_name,
  constraint_name,
  cname1 || 
  NVL2( cname2, ',' || cname2, NULL ) ||
  NVL2( cname3, ',' || cname3, NULL ) ||
  NVL2( cname4, ',' || cname4, NULL ) ||
  NVL2( cname5, ',' || cname5, NULL ) ||
  NVL2( cname6, ',' || cname6, NULL ) ||
  NVL2( cname7, ',' || cname7, NULL ) ||
  NVL2( cname8, ',' || cname8, NULL ) columns
FROM
(
   SELECT
      b.table_name,
      b.constraint_name,
      MAX( DECODE( position, 1, column_name, NULL ) ) cname1,
      MAX( DECODE( position, 2, column_name, NULL ) ) cname2,
      MAX( DECODE( position, 3, column_name, NULL ) ) cname3,
      MAX( DECODE( position, 4, column_name, NULL ) ) cname4,
      MAX( DECODE( position, 5, column_name, NULL ) ) cname5,
      MAX( DECODE( position, 6, column_name, NULL ) ) cname6,
      MAX( DECODE( position, 7, column_name, NULL ) ) cname7,
      MAX( DECODE( position, 8, column_name, NULL ) ) cname8,
      COUNT(*) col_cnt
   FROM
   (
      SELECT 
         SUBSTR( table_name, 1, 30 ) table_name,
         SUBSTR( constraint_name, 1, 30 ) constraint_name,
         SUBSTR( column_name, 1, 30 ) column_name,
         position
      FROM user_cons_columns
   ) a,
   user_constraints b
   WHERE a.constraint_name = b.constraint_name
    AND b.constraint_type = 'R'
   GROUP BY b.table_name, b.constraint_name
) cons
WHERE col_cnt > ALL
(
   SELECT COUNT(*)
   FROM user_ind_columns i
   WHERE i.table_name = cons.table_name
   AND i.column_name IN 
   (
      cname1, cname2, cname3, cname4,
      cname1, cname2, cname3, cname4
   )
   AND i.column_position <= cons.col_cnt
   GROUP BY i.index_name
) ;

As a Bonus, I will also talk about how to delete records from Parents table? There are various ways you can delete records from parent table. I will discuss few simple and common methods Delete corresponding child records first and then delete record from parent table. Use ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table.

This above delete statement made me think, can I truncate records from a parent table? You can definitely truncate or delete the records from child table. This is a no brainier to any database personnel. The million dollar question is; can I truncate a parent table after truncating Child table? If no, why not?

I was under the assumption we can truncate the parent table but that is not the case. So what is going to block when the child has no record? You can't truncate the Parent table without disabling the Foreign Key Constraints because truncate will violate the F.K. Constraints.