systemadmin.es > DBA > Buscar las foreign keys que referencian a una tabla en MySQL

Buscar las foreign keys que referencian a una tabla en MySQL

En MySQL, mediante INFORMATION_SCHEMA podemos listar las tablas que tienen foreign keys hacia la tabla que nos interesa

Para ello deberemos hacer la siguiente query a la tabla KEY_COLUMN_USAGE:

SELECT table_name,column_name,constraint_name, referenced_table_name,referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name = 'tabla';

Por ejemplo, suponiendo que tenemos las siguientes dos tablas:

mysql> CREATE TABLE fc1 (
    ->     i INT PRIMARY KEY,
    ->     j INT
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.25 sec)

mysql> CREATE TABLE fc2 (
    ->     m INT PRIMARY KEY,
    ->     n INT,
    ->     FOREIGN KEY ni (n) REFERENCES fc1 (i)
    ->         ON DELETE CASCADE
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.15 sec)

Mediante dicha query podremos listar las tablas que hacen referencia a fc1, en este caso, fc2:

mysql> SELECT table_name,column_name,constraint_name, referenced_table_name,referenced_column_name
    -> FROM information_schema.key_column_usage
    -> WHERE referenced_table_name = 'fc1';
+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+
| fc2        | n           | fc2_ibfk_1      | fc1                   | i                      |
+------------+-------------+-----------------+-----------------------+------------------------+
1 row in set (2.66 sec)

También podemos listar todas las constraints por schema haciendo el WHERE por la columna TABLE_SCHEMA:

mysql> select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_SCHEMA="fktest";
+------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------+-----------------------+------------------------+
| fc1        | i           | PRIMARY         | NULL                  | NULL                   |
| fc2        | m           | PRIMARY         | NULL                  | NULL                   |
| fc2        | n           | fc2_ibfk_1      | fc1                   | i                      |
+------------+-------------+-----------------+-----------------------+------------------------+
3 rows in set (0.00 sec)

Deja un comentario:

XHTML - Tags permitidos:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>