Como vejo todas as foreign keys para uma tabela ou coluna?

No MySQL, como faço para obter uma lista de todas as restrições de foreign keys apontando para uma tabela específica? uma coluna particular? Esta é a mesma coisa que esta questão do Oracle , mas para o MySQL.

Por uma tabela:

SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '' AND REFERENCED_TABLE_NAME = '';

Para uma coluna:

 SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = '' AND REFERENCED_TABLE_NAME = '' AND REFERENCED_COLUMN_NAME = '';

Basicamente, alteramos REFERENCED_TABLE_NAME com REFERENCED_COLUMN_NAME na cláusula where.

EDIT: Como apontado nos comentários, esta não é a resposta correta para a pergunta de OPs, mas é útil saber este comando. Esta pergunta apareceu no Google para o que eu estava procurando, e pensei em deixar essa resposta para os outros encontrarem.

 SHOW CREATE TABLE ``; 

Eu encontrei esta resposta aqui: MySQL: show constraints on tables command

Eu precisava desse jeito porque queria ver como o FK funcionava, em vez de apenas ver se existia ou não.

Se você usar o InnoDB e os FKs definidos, poderá consultar o database information_schema, por exemplo:

 SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE information_schema.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'FOREIGN KEY' AND information_schema.TABLE_CONSTRAINTS.TABLE_SCHEMA = 'myschema' AND information_schema.TABLE_CONSTRAINTS.TABLE_NAME = 'mytable'; 

Postar em uma resposta antiga para adicionar algumas informações úteis.

Eu tive um problema semelhante, mas também queria ver o CONSTRAINT_TYPE junto com os nomes da tabela e da coluna REFERENCED. Assim,

  1. Para ver todos os FKs na sua mesa:

     USE ''; SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME FROM information_schema.TABLE_CONSTRAINTS i LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' AND i.TABLE_SCHEMA = DATABASE() AND i.TABLE_NAME = ''; 
  2. Para ver todas as tabelas e FKs no seu esquema:

     USE ''; SELECT i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME FROM information_schema.TABLE_CONSTRAINTS i LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY' AND i.TABLE_SCHEMA = DATABASE(); 
  3. Para ver todos os FKs em seu database:

     SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME FROM information_schema.TABLE_CONSTRAINTS i LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'; 

Lembrar!

Isso está usando o mecanismo de armazenamento InnoDB. Se você não consegue obter quaisquer foreign keys para aparecer depois de adicioná-las, provavelmente é porque suas tabelas estão usando o MyISAM.

Checar:

 SELECT * TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ''; 

Para corrigir, use isto:

 ALTER TABLE `` ENGINE=InnoDB; 

Como alternativa à resposta do Node, se você usar o InnoDB e os FKs definidos, poderá consultar o database information_schema, por exemplo:

 SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = '' AND TABLE_NAME = ''

para foreign keys de

, ou

 SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = '' AND REFERENCED_TABLE_NAME = ''

para foreign keys para

Você também pode obter o UPDATE_RULE e o DELETE_RULE, se desejar.

Essa solução não só exibirá todas as relações, mas também o nome da restrição, que é necessário em alguns casos (por exemplo, contraint drop):

 select concat(table_name, '.', column_name) as 'foreign key', concat(referenced_table_name, '.', referenced_column_name) as 'references', constraint_name as 'constraint name' from information_schema.key_column_usage where referenced_table_name is not null; 

Se você quiser verificar as tabelas em um database específico, no final da consulta, adicione o nome da tabela:

 select concat(table_name, '.', column_name) as 'foreign key', concat(referenced_table_name, '.', referenced_column_name) as 'references', constraint_name as 'constraint name' from information_schema.key_column_usage where referenced_table_name is not null and table_schema = 'database_name'; 

Da mesma forma, para um nome de coluna específico, adicione

e table_name = ‘table_name

no final da consulta.

Inspirado por este post aqui

Uma maneira rápida de listar seus FKs (referências de chave estrangeira) usando o

 KEY_COLUMN_USAGE view: SELECT CONCAT( table_name, '.', column_name, ' -> ', referenced_table_name, '.', referenced_column_name ) AS list_of_fks FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = (your schema name here) AND REFERENCED_TABLE_NAME is not null ORDER BY TABLE_NAME, COLUMN_NAME; 

Essa consulta supõe que as restrições e todas as tabelas referenciadas e de referência estejam no mesmo esquema.

Adicione seu próprio comentário.

Fonte: o manual oficial do mysql.

Para localizar todas as tabelas que contêm uma chave estrangeira específica , como employee_id

 SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('employee_id') AND TABLE_SCHEMA='table_name'; 

O uso de REFERENCED_TABLE_NAME nem sempre funciona e pode ser um valor NULL. A consulta a seguir pode funcionar em vez disso:

 select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = '';

A solução que encontrei é frágil; Ele depende da convenção de nomenclatura do django para foreign keys.

 USE information_schema; tee mysql_output SELECT * FROM TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_SCHEMA = 'database_name'; notee 

Então, no shell,

 grep 'refs_tablename_id' mysql_output 

Se você também quiser obter o nome da coluna de chave estrangeira:

 SELECT i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME, k.COLUMN_NAME, k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME FROM information_schema.TABLE_CONSTRAINTS i LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME WHERE i.TABLE_SCHEMA = '' AND i.CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY i.TABLE_NAME;