SQL 列举非索引外键|极客教程

SQL 列举非索引外键，你想列出含有非索引外键的表。例如，你希望确认 EMP 表的外键是否加入了索引。

SQL 列举非索引外键问题描述

你想列出含有非索引外键的表。例如，你希望确认 EMP 表的外键是否加入了索引。

SQL 列举非索引外键解决方案

DB2
查询 SYSCAT.TABCONST、SYSCAT.KEYCOLUSE、SYSCAT.INDEXES 和 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
查询 SYS.ALL_CONS_COLUMNS、SYS.ALL_CONSTRAINTS 和 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

查询 INFORMATION_SCHEMA.KEY_COLUMN_USAGE、INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS、INFORMATION_SCHEMA.COLUMNS 和 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
使用 SHOW INDEX 命令获取诸如索引名称、索引列和列位置序号之类的索引信息。除此之外，我们还可以通过查询 INFORMATION_SCHEMA.KEY_COLUMN_USAGE 列出表的外键。对于 MySQL 5 而言，外键虽然默认是加入索引的，但事实上却可以被删掉。要确认外键列的索引是否已经被删除，可以针对特定的表执行 SHOW INDEX 命令，并比较其输出结果与 INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME 的异同。如果 KEY_COLUMN_USAGE 里有对应的 COLUMN_NAME，但是 SHOW INDEX 输出的结果里却没有，那么就说明该列没有索引。
SQL Server
查询 SYS.TABLES、SYS.FOREIGN_KEYS、SYS.COLUMNS、SYS.INDEXES 和 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