Recipe5.5.Listing Foreign Keys Without Corresponding Indexes

Recipe 5.5. Listing Foreign Keys Without Corresponding Indexes

Problem

You want to list tables that have foreign key columns that are not indexed. For example, you want to determine if the foreign keys on table EMP are indexed.

Solution

DB2

Query SYSCAT.TABCONST, SYSCAT.KEYCOLUSE, SYSCAT.INDEXES, and SYSCAT.INDEXCOLUSE:

  1  select fkeys.tabname,  2         fkeys.constname,  3         fkeys.colname,  4         ind_cols.indname  5    from (  6  select a.tabschema, a.tabname, a.constname, b.colname  7    from syscat.tabconst a,  8         syscat.keycoluse b  9  where a.tabname    = 'EMP' 10    and a.tabschema  = 'SMEAGOL' 11    and a.type       = 'F' 12    and a.tabname    = b.tabname 13    and a.tabschema  = b.tabschema 14        ) fkeys 15        left join 16        ( 17  select a.tabschema, 18         a.tabname, 19         a.indname, 20         b.colname 21    from syscat.indexes a, 22         syscat.indexcoluse b 23  where a.indschema  = b.indschema 24    and a.indname    = b.indname 25        ) ind_cols 26     on (     fkeys.tabschema = ind_cols.tabschema 27          and fkeys.tabname   = ind_cols.tabname 28          and fkeys.colname   = ind_cols.colname ) 29  where ind_cols.indname is null 

Oracle

Query SYS.ALL_CONS_COLUMNS, SYS.ALL_CONSTRAINTS, and SYS.ALL_ IND_COLUMNS:

  1  select a.table_name,  2         a.constraint_name,  3         a.column_name,  4         c.index_name  5    from all_cons_columns a,  6         all_constraints b,  7         all_ind_columns c  8  where a.table_name      = 'EMP'  9    and a.owner           = 'SMEAGOL' 10    and b.constraint_type = 'R' 11    and a.owner           = b.owner 12    and a.table_name      = b.table_name 13    and a.constraint_name = b.constraint_name 14    and a.owner           = c.table_owner (+) 15    and a.table_name      = c.table_name (+) 16    and a.column_name     = c.column_name (+) 17    and c.index_name      is null 

PostgreSQL

Query INFORMATION_SCHEMA.KEY_COLUMN_USAGE, INFORMATION_ SCHEMA.REFERENTIAL_CONSTRAINTS, INFORMATION_SCHEMA.COL-UMNS, and PG_CATALOG.PG_INDEXES:

  1  select fkeys.table_name,  2         fkeys.constraint_name,  3         fkeys.column_name,  4         ind_cols.indexname  5    from (  6  select a.constraint_schema,  7         a.table_name,  8         a.constraint_name,  9         a.column_name 10    from information_schema.key_column_usage a, 11         information_schema.referential_constraints b 12   where a.constraint_name   = b.constraint_name 13     and a.constraint_schema = b.constraint_schema 14     and a.constraint_schema = 'SMEAGOL' 15     and a.table_name        = 'EMP' 16         ) fkeys 17         left join 18         ( 19  select a.schemaname, a.tablename, a.indexname, b.column_name 20    from pg_catalog.pg_indexes a, 21         information_schema.columns b 22   where a.tablename  = b.table_name 23     and a.schemaname = b.table_schema 24         ) ind_cols 25      on (  fkeys.constraint_schema = ind_cols.schemaname 26           and fkeys.table_name     = ind_cols.tablename 27           and fkeys.column_name    = ind_cols.column_name ) 28   where ind_cols.indexname is null 

MySQL

You can use the SHOW INDEX command to retrieve index information such as index name, columns in the index, and ordinal position of the columns in the index. Additionally, you can query INFORMATION_SCHEMA.KEY_COLUMN_USAGE to list the foreign keys for a given table. In MySQL 5, foreign keys are said to be indexed automatically, but can in fact be dropped. To determine whether a foreign key column's index has been dropped you can execute SHOW INDEX for a particular table and compare the output with that of INFORMATION_SCHEMA.KEY_ COLUMN_USAGE.COLUMN_NAME for the same table. If the COLUMN_NAME is listed in KEY_COLUMN_USAGE but is not returned by SHOW INDEX, you know that column is not indexed.

SQL Server

Query SYS.TABLES, SYS.FOREIGN_KEYS, SYS.COLUMNS, SYS.INDEXES, and SYS.INDEX_COLUMNS:

  1  select fkeys.table_name,  2         fkeys.constraint_name,  3         fkeys.column_name,  4         ind_cols.index_name  5    from (  6  select a.object_id,  7         d.column_id,  8         a.name table_name,  9         b.name constraint_name, 10         d.name column_name 11    from sys.tables a 12         join 13         sys.foreign_keys b 14      on ( a.name          = 'EMP' 15           and a.object_id = b.parent_object_id 16         ) 17         join 18         sys.foreign_key_columns c 19     on (  b.object_id = c.constraint_object_id ) 20        join 21        sys.columns d 22     on (    c.constraint_column_id = d.column_id 23         and a.object_id            = d.object_id 24        ) 25        ) fkeys 26        left join 27        ( 28 select a.name index_name, 29        b.object_id, 30        b.column_id 31   from sys.indexes a, 32        sys.index_columns b 33  where a.index_id = b.index_id 34        ) ind_cols 35     on (     fkeys.object_id = ind_cols.object_id 36          and fkeys.column_id = ind_cols.column_id ) 37  where ind_cols.index_name is null 

Discussion

Each vendor uses its own locking mechanism when modifying rows. In cases where there is a parent-child relationship enforced via foreign key, having indexes on the child column(s) can reducing locking (see your specific RDBMS documentation for details). In other cases, it is common that a child table is joined to a parent table on the foreign key column, so an index may help improve performance in that scenario as well.