查询数据库所有的外键

1.查询数据库某表的外键
select
CONCAT(table_name,'.',column_name) as 'foreign key',
CONCAT(referenced_table_name,'.',referenced_column_name) as 're key',
CONSTRAINT_name as 'c name'
from information_schema.KEY_COLUMN_USAGE
where referenced_table_name is not null
and CONSTRAINT_SCHEMA = '数据库名称'
and referenced_table_name = '表的名称'

select * from information_schema.KEY_COLUMN_USAGE

 

2.查询数据库所有的的外键
select
CONCAT(table_name,'.',column_name) as 'foreign key',
CONCAT(referenced_table_name,'.',referenced_column_name) as 're key',
CONSTRAINT_name as 'c name'
from information_schema.KEY_COLUMN_USAGE
where referenced_table_name is not null
and CONSTRAINT_SCHEMA = '数据库名称'

注：有时候删除表的时候提示有外键，但已经把该表相关的外键删了，有可能是复制的该表与别的表有关联，所以还要把复制的表的外键删掉才行。