En MySQL, ¿cómo puedo obtener una lista de todas las restricciones de clave externa que apunta a una tabla en particular? una columna en particular? Esta es la misma cosa como este Oráculo pregunta, pero para MySQL.

11 Comentarios

  1. 676

    De una Tabla:

    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, hemos cambiado REFERENCED_TABLE_NAME con REFERENCED_COLUMN_NAME en la cláusula where.

    • esto siempre me da un conjunto vacío, mientras que la consulta propuesta por el Nodo de abajo funciona bien
    • Está usted seguro de que usted está preguntando acerca de la tabla correcta? Si el Nodo del trabajo de consulta, entonces usted está probablemente preguntando sobre la otra dirección (es decir, las claves DE mytable, no de las teclas PARA mytable.) Esta esperando que usted escribió ‘<table>’ con el nombre de la tabla y sin el «< » y » >’?
    • Parece que no he entendido consulta, porque yo estaba en la consulta de claves de referencia DE la <table> 🙂 (sí, escribí el nombre de la tabla en lugar de «<table>» XD)
    • A menos que estés seguro de que tu nombre de tabla es única, es probable que desee para restringir su búsqueda a una determinada base de datos. Cambio de la cláusula where para esto: where REFERENCED_TABLE_SCHEMA = 'mydatabase' and REFERENCED_TABLE_NAME = 'mytable'
    • Esto no funciona en el caso de no ser usuario root aunque el usuario tiene todos los permisos a la base de datos
    • Esto funciona a la perfección para mí con un usuario con privilegios que no es el usuario root.
    • De acuerdo, yo sólo obtener un conjunto vacío aquí! Andy respuesta funcionado mejor para mí.
    • ¿Por qué es necesario tanto? ¿Por qué no hacer que sea tan fácil como la forma de seleccionar las filas?
    • Esto funcionó perfecto para mí. Tengo la mayoría de los privilegios, pero no de la raíz. Usted es un protector de la vida.
    • He añadido TABLE_SCHEMA a la lista de columnas ( SELECT TABLE_SCHEMA, TABLE_NAME, … ), que es útil en el caso de que existan múltiples bases de datos que tienen las tablas relativas a nuestra tabla/columna de interés.

  2. 238

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

    SHOW CREATE TABLE `<yourtable>`;

    He encontrado esta respuesta aquí:
    MySQL : mostrar las limitaciones de las tablas de comandos

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

    • Esto muestra todas las restricciones en <yourtable>, no todas las restricciones que señale <yourtable>.
    • Como @Barmar dice, esto es totalmente equivocado; se mostrarán las claves externas pertenecientes a la tabla especificada, pero no mostrará las claves externas apuntando A de la tabla, que es lo que pide la pregunta. Ni idea de cómo esto tiene 50 upvotes; supongo que la gente termina aquí cuando realmente estaban buscando la respuesta a la pregunta contraria, encuentra su respuesta aquí de todos modos, y no se molestaba en leer la pregunta original (o incluso su título) antes de upvoting.
    • este es el primer resultado de display foreign keys mysql en google, puede ser por eso 😉
    • thisi realidad muestra todas las restricciones que existen, phpmyadmin sólo se mostrará el uno apuntando a su mesa. parece lo suficientemente bueno como para evitar duplicados con una herramienta ORM
    • Esto es lo que yo estaba buscando, así que gracias por publicar, a pesar de no respuesta OP pregunta. Nunca está de más saber cómo buscar en ambas direcciones en una relación!
    • Me encanta este tipo de respuestas sir.

  3. 72

    Si usar InnoDB y definido FK usted puede consultar la base de datos information_schema 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';
    • en realidad, que apunta en la dirección equivocada. que la consulta muestra todas las claves externas que apuntan DESDE la ‘mitabla’, no todas las claves que apunta A ‘mitabla’.
    • Esto funciona mejor en mi caso. Necesito gota cada restricción foreign key (y sólo ellas), a partir de una tabla para ser capaz de cambiar el motor InnoDB MyISAM o NDB.
    • Usted puede obtener las claves externas en ambas direcciones desde el REFERENTIAL_CONSTRAINTS mesa – he añadido otra respuesta a la consulta.
  4. 43

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

    He tenido un problema similar, pero yo también quería ver la CONSTRAINT_TYPE junto con la REFERENCIA a nombres de tabla y columna. Así,

    1. Para ver todos los FKs en la 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 FKs 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 todas las FKs en la 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';

    Recordar!

    Esto es utilizando el motor de almacenamiento InnoDB. Si usted no puede conseguir que cualquier extranjero claves para aparecer después de la adición de ellos es probablemente debido a que las tablas están usando MyISAM.

    Para comprobar:

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

    A revisión, utilice esto:

    ALTER TABLE `<yourtable>` ENGINE=InnoDB;
  5. 22

    Como una alternativa a la del Nodo de respuesta, si usted usar InnoDB y definido FK usted puede consultar la base de datos information_schema 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 desea ellos.

    • Yo personalmente prefiero esta respuesta como el uso de la REFERENTIAL_CONSTRAINTS mesa le da la actualización y la cascada de la regla. +1
    • Haciendo descubrimiento sobre una mesa en la que no hay que olvidar que las claves externas pueden ser establecidos en AMBOS sentidos!
  6. 9

    Esta solución no sólo mostrar todas las relaciones, pero también el nombre de la restricción, que es necesario en algunos casos (por ejemplo, caída de restricción):

    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 usted quiere comprobar las tablas de una base de datos específica, al final de la consulta agregar el nombre del esquema:

    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 determinado nombre de la columna, agregar

    y table_name = ‘nombre_tabla

    al final de la consulta.

    Inspirado por este post aquí

  7. 5

    Utilizando REFERENCED_TABLE_NAME no siempre funciona y puede ser un valor NULO. La siguiente consulta puede trabajar en su lugar:

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

    Una manera rápida a la lista de su FKs (Clave externa referencias) 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 no asumir que las limitaciones y todas las que se hace referencia y hace referencia a las tablas están en el mismo esquema.

    Añadir su propio comentario.

    Fuente: el oficial de mysql manual.

  9. 3

    La solución que se me ocurrió es frágil; se basa en django la convención de nomenclatura para claves foráneas.

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

    A continuación, en la shell,

    grep 'refs_tablename_id' mysql_output
  10. 2

    Para encontrar todas las tablas que contiene un determinado clave externa como employee_id

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

    Si usted 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;
    • Gracias gran solución! Necesitaba el nombre de la columna así, para conseguir que añadir: ‘k.COLUMN_NAME’

Dejar respuesta

Please enter your comment!
Please enter your name here