mysql - foreign - ¿Cómo veo todas las claves externas de una tabla o columna?

on delete no action on update no action

Question

En MySQL, ¿cómo obtengo una lista de todas las restricciones de clave externa que apuntan a una tabla en particular? una columna en particular? Esto es lo mismo que esta pregunta de Oracle , pero para MySQL.

Answer 1

Como alternativa a la respuesta de Node, si utiliza InnoDB y FK definidos, puede consultar la base de datos del esquema de información, por ejemplo:

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = ''<schema>''
AND TABLE_NAME = ''<table>''

para claves foráneas de <table>, o

SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = ''<schema>''
AND REFERENCED_TABLE_NAME = ''<table>''

para claves foráneas a <table>

También puede obtener el UPDATE_RULE y DELETE_RULE si lo desea.

Answer 2

EDITAR: Como se señaló en los comentarios, esta no es la respuesta correcta a la pregunta de OP, pero es útil conocer este comando. Esta pregunta apareció en Google para lo que estaba buscando, y pensé que dejaría esta respuesta para que la encontraran los demás.

SHOW CREATE TABLE `<yourtable>`;

Encontré esta respuesta aquí: MySQL: mostrar restricciones en el comando de tablas

Necesitaba esto porque quería ver cómo funcionaba el FK, en lugar de solo ver si existía o no.

Answer 3

El uso de REFERENCED_TABLE_NAME no siempre funciona y puede ser un valor NULO. La siguiente consulta puede funcionar en su lugar:

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME = ''<table>'';

Answer 4

Esta solución no solo mostrará todas las relaciones, sino también el nombre de la restricción, que se requiere en algunos casos (p. Ej., Contraposición de caída):

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;

Si desea verificar las tablas en una base de datos específica, al final de la consulta agregue el nombre de la tabla:

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'';

Del mismo modo, para un nombre de columna específico, agregue

y table_name = ''table_name

Al final de la consulta.

Inspirado por este post here

Answer 5

La solución que encontré es frágil; se basa en la convención de nomenclatura de django para claves externas.

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

Luego, en la concha,

grep ''refs_tablename_id'' mysql_output

Answer 6

Para buscar todas las tablas que contienen una clave externa particular , como employee_id

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

Answer 7

Publicar en una respuesta anterior para agregar alguna información útil.

Tuve un problema similar, pero también quería ver CONSTRAINT_TYPE junto con la tabla y los nombres de columna REFERENCED. Asi que,

1. Para ver todos los FKs en tu tabla:

   USE ''<yourschema>'';
   
   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 = ''<yourtable>'';

2. Para ver todas las tablas y FK en su esquema:

   USE ''<yourschema>'';
   
   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 los FKs en tu base de datos:

   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'';

¡Recuerda!

Esto está utilizando el motor de almacenamiento InnoDB. Si parece que no puede mostrar ninguna clave externa después de agregarlas, es probable que las tablas estén usando MyISAM.

Verificar:

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

Para arreglarlo, usa esto:

ALTER TABLE `<yourtable>` ENGINE=InnoDB;

Answer 8

Si también desea obtener el nombre de la columna de clave externa:

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 = ''<TABLE_NAME>'' AND i.CONSTRAINT_TYPE = ''FOREIGN KEY'' 
 ORDER BY i.TABLE_NAME;

Answer 9

Si usa InnoDB y FK definidos, puede consultar la base de datos del esquema de información, por ejemplo:

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'';

Answer 10

Una forma rápida de enumerar sus FK (referencias de clave externa) utilizando el

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;

Esta consulta asume que las restricciones y todas las tablas a las que se hace referencia y que hacen referencia están en el mismo esquema.

Añade tu propio comentario.

Fuente: el manual oficial de mysql.

Answer 11

Para una mesa:

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

Para una columna:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = ''<database>'' AND
  REFERENCED_TABLE_NAME = ''<table>'' AND
  REFERENCED_COLUMN_NAME = ''<column>'';

Básicamente, cambiamos REFERENCED_TABLE_NAME con REFERENCED_COLUMN_NAME en la cláusula where.