Is there a command to list all foreign keys of a MYSQL table?

Asked

Viewed 517 times

1

I would like to know how to list all existing Foreign Keys in a specific table.

For example:

 usuarios [id, nome, nivel_id*, empresa_id*, cargo_id*]

In the above case, list the fields empresa_id, nivel_id and cargo_id with their respective information.

mysql

1 answer

2


For each instance of MySql there is a database called information_schema. This database contains all the information of all the instance databases.

To recover all foreign keys, just make a SELECT on the table REFERENTIAL_CONSTRAINTS:

SELECT *
FROM information_schema.`REFERENTIAL_CONSTRAINTS`;

If you want to filter foreign keys from a specific table, add the clause WHERE:

SELECT *
FROM information_schema.`REFERENTIAL_CONSTRAINTS` a
WHERE a.`CONSTRAINT_SCHEMA` = 'nome_do_banco' AND a.`TABLE_NAME` = 'nome_da_tabela'

There are many other useful things in this database, see the documentation.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.