How do I see all foreign keys to a table or column

Question

In MySQL  how do I get a list of all foreign key constraints pointing to a particular table  a particular column  This is the same thing as this Oracle question  but for MySQL

User · Answer

EDIT  As pointed out in the comments  this is not the correct answer to the OPs question  but it is useful to know this command  This question showed up in Google for what I was looking for  and figured I d leave this answer for the others to find   SHOW CREATE TABLE   lt yourtable gt      I found this answer here  MySQL   show constraints on tables command  I needed this way because I wanted to see how the FK functioned  rather than just see if it existed or not

User · Answer

If you use InnoDB and defined FK s you could query the information schema database e g    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

User · Answer

For a Table   SELECT    TABLE NAME COLUMN NAME CONSTRAINT NAME  REFERENCED TABLE NAME REFERENCED COLUMN NAME FROM   INFORMATION SCHEMA KEY COLUMN USAGE WHERE   REFERENCED TABLE SCHEMA     lt database gt   AND   REFERENCED TABLE NAME     lt table gt      For a Column   SELECT    TABLE NAME COLUMN NAME CONSTRAINT NAME  REFERENCED TABLE NAME REFERENCED COLUMN NAME FROM   INFORMATION SCHEMA KEY COLUMN USAGE WHERE   REFERENCED TABLE SCHEMA     lt database gt   AND   REFERENCED TABLE NAME     lt table gt   AND   REFERENCED COLUMN NAME     lt column gt      Basically  we changed REFERENCED TABLE NAME with REFERENCED COLUMN NAME in the where clause

User · Answer

As an alternative to Node   s answer  if you use InnoDB and defined FK   s you could query the information schema database e g    SELECT CONSTRAINT NAME  TABLE NAME  REFERENCED TABLE NAME FROM information schema REFERENTIAL CONSTRAINTS WHERE CONSTRAINT SCHEMA     lt schema gt   AND TABLE NAME     lt table gt     for foreign keys from  lt table gt   or  SELECT CONSTRAINT NAME  TABLE NAME  REFERENCED TABLE NAME FROM information schema REFERENTIAL CONSTRAINTS WHERE CONSTRAINT SCHEMA     lt schema gt   AND REFERENCED TABLE NAME     lt table gt     for foreign keys to  lt table gt   You can also get the UPDATE RULE and DELETE RULE if you want them

User · Answer

For a Table   SELECT    TABLE NAME COLUMN NAME CONSTRAINT NAME  REFERENCED TABLE NAME REFERENCED COLUMN NAME FROM   INFORMATION SCHEMA KEY COLUMN USAGE WHERE   REFERENCED TABLE SCHEMA     lt database gt   AND   REFERENCED TABLE NAME     lt table gt      For a Column   SELECT    TABLE NAME COLUMN NAME CONSTRAINT NAME  REFERENCED TABLE NAME REFERENCED COLUMN NAME FROM   INFORMATION SCHEMA KEY COLUMN USAGE WHERE   REFERENCED TABLE SCHEMA     lt database gt   AND   REFERENCED TABLE NAME     lt table gt   AND   REFERENCED COLUMN NAME     lt column gt      Basically  we changed REFERENCED TABLE NAME with REFERENCED COLUMN NAME in the where clause

User · Answer

The solution I came up with is fragile  it relies on django s naming convention for foreign keys   USE information schema  tee mysql output SELECT   FROM TABLE CONSTRAINTS WHERE CONSTRAINT TYPE    FOREIGN KEY  AND TABLE SCHEMA    database name   notee   Then  in the shell   grep  refs tablename id  mysql output

User · Answer

Posting on an old answer to add some useful information   I had a similar problem  but I also wanted to see the CONSTRAINT TYPE along with the REFERENCED table and column names  So    To see all FKs in your table   USE   lt yourschema gt     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     lt yourtable gt     To see all the tables and FKs in your schema   USE   lt yourschema gt     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     To see all the FKs in your 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      Remember   This is using the InnoDB storage engine  If you can t seem to get any foreign keys to show up after adding them it s probably because your tables are using MyISAM    To check   SELECT   TABLE NAME  ENGINE FROM information schema TABLES WHERE TABLE SCHEMA     lt yourschema gt      To fix  use this   ALTER TABLE   lt yourtable gt   ENGINE InnoDB

User · Answer

EDIT  As pointed out in the comments  this is not the correct answer to the OPs question  but it is useful to know this command  This question showed up in Google for what I was looking for  and figured I d leave this answer for the others to find   SHOW CREATE TABLE   lt yourtable gt      I found this answer here  MySQL   show constraints on tables command  I needed this way because I wanted to see how the FK functioned  rather than just see if it existed or not

User · Answer

To find all tables containing a particular foreign key such as employee id  SELECT DISTINCT TABLE NAME  FROM INFORMATION SCHEMA COLUMNS WHERE COLUMN NAME IN   employee id   AND TABLE SCHEMA  table name

User · Answer

A quick way to list your FKs  Foreign Key references  using the    KEY COLUMN USAGE view   SELECT CONCAT  table name       column name    - gt     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    This query does assume that the constraints and all referenced and referencing tables are in the same schema   Add your own comment   Source  the official mysql manual

User · Answer

If you use InnoDB and defined FK s you could query the information schema database e g    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

User · Answer

Constraints in SQL are the rules defined for the data in a table  Constraints also limit the types of data that go into the table  If new data does not abide by these rules the action is aborted  select   from INFORMATION SCHEMA TABLE CONSTRAINTS where CONSTRAINT TYPE    FOREIGN KEY    You can view all constraints by using select   from information schema table constraints   This will produce a lot of table data   You can also use this for MySQL  show create table tableName

User · Answer

This solution will not only display all relations but also the constraint name  which is required in some cases  e g  drop contraint    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    If you want to check tables in a specific database  at the end of the query add the schema name   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     Likewise  for a specific column name  add     and table name    table name   at the end of the query   Inspired by this post here

User · Answer

If you use InnoDB and defined FK s you could query the information schema database e g    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

User · Answer

If you also want to get the name of the foreign key column   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     lt TABLE NAME gt   AND i CONSTRAINT TYPE    FOREIGN KEY    ORDER BY i TABLE NAME

User · Answer

As an alternative to Node   s answer  if you use InnoDB and defined FK   s you could query the information schema database e g    SELECT CONSTRAINT NAME  TABLE NAME  REFERENCED TABLE NAME FROM information schema REFERENTIAL CONSTRAINTS WHERE CONSTRAINT SCHEMA     lt schema gt   AND TABLE NAME     lt table gt     for foreign keys from  lt table gt   or  SELECT CONSTRAINT NAME  TABLE NAME  REFERENCED TABLE NAME FROM information schema REFERENTIAL CONSTRAINTS WHERE CONSTRAINT SCHEMA     lt schema gt   AND REFERENCED TABLE NAME     lt table gt     for foreign keys to  lt table gt   You can also get the UPDATE RULE and DELETE RULE if you want them

User · Answer

Posting on an old answer to add some useful information   I had a similar problem  but I also wanted to see the CONSTRAINT TYPE along with the REFERENCED table and column names  So    To see all FKs in your table   USE   lt yourschema gt     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     lt yourtable gt     To see all the tables and FKs in your schema   USE   lt yourschema gt     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     To see all the FKs in your 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      Remember   This is using the InnoDB storage engine  If you can t seem to get any foreign keys to show up after adding them it s probably because your tables are using MyISAM    To check   SELECT   TABLE NAME  ENGINE FROM information schema TABLES WHERE TABLE SCHEMA     lt yourschema gt      To fix  use this   ALTER TABLE   lt yourtable gt   ENGINE InnoDB

User · Answer

The solution I came up with is fragile  it relies on django s naming convention for foreign keys   USE information schema  tee mysql output SELECT   FROM TABLE CONSTRAINTS WHERE CONSTRAINT TYPE    FOREIGN KEY  AND TABLE SCHEMA    database name   notee   Then  in the shell   grep  refs tablename id  mysql output

User · Answer

For a Table   SELECT    TABLE NAME COLUMN NAME CONSTRAINT NAME  REFERENCED TABLE NAME REFERENCED COLUMN NAME FROM   INFORMATION SCHEMA KEY COLUMN USAGE WHERE   REFERENCED TABLE SCHEMA     lt database gt   AND   REFERENCED TABLE NAME     lt table gt      For a Column   SELECT    TABLE NAME COLUMN NAME CONSTRAINT NAME  REFERENCED TABLE NAME REFERENCED COLUMN NAME FROM   INFORMATION SCHEMA KEY COLUMN USAGE WHERE   REFERENCED TABLE SCHEMA     lt database gt   AND   REFERENCED TABLE NAME     lt table gt   AND   REFERENCED COLUMN NAME     lt column gt      Basically  we changed REFERENCED TABLE NAME with REFERENCED COLUMN NAME in the where clause

User · Answer

If you use InnoDB and defined FK s you could query the information schema database e g    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

User · Answer

This solution will not only display all relations but also the constraint name  which is required in some cases  e g  drop contraint    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    If you want to check tables in a specific database  at the end of the query add the schema name   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     Likewise  for a specific column name  add     and table name    table name   at the end of the query   Inspired by this post here

User · Answer

I m reluctant to add yet another answer  but I ve had to beg  borrow and steal from the others to get what I want  which is a complete list of all the FK relationships on tables in a given schema  INCLUDING FKs to tables in other schemas  The two crucial recordsets are information schema KEY COLUMN USAGE and information schema referential constraints  If an attribute you want is missing  just uncomment the KCU   RC  to see what s available SELECT DISTINCT KCU TABLE NAME  KCU COLUMN NAME  REFERENCED TABLE SCHEMA  KCU REFERENCED TABLE NAME  KCU REFERENCED COLUMN NAME  UPDATE RULE  DELETE RULE    KCU    RC   FROM information schema KEY COLUMN USAGE KCU INNER JOIN information schema referential constraints RC ON KCU CONSTRAINT NAME   RC CONSTRAINT NAME WHERE TABLE SCHEMA    your schema name  AND KCU REFERENCED TABLE NAME IS NOT NULL ORDER BY KCU TABLE NAME  KCU COLUMN NAME

User · Answer

If you also want to get the name of the foreign key column   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     lt TABLE NAME gt   AND i CONSTRAINT TYPE    FOREIGN KEY    ORDER BY i TABLE NAME

User · Answer

The solution I came up with is fragile  it relies on django s naming convention for foreign keys   USE information schema  tee mysql output SELECT   FROM TABLE CONSTRAINTS WHERE CONSTRAINT TYPE    FOREIGN KEY  AND TABLE SCHEMA    database name   notee   Then  in the shell   grep  refs tablename id  mysql output

User · Answer

For a Table   SELECT    TABLE NAME COLUMN NAME CONSTRAINT NAME  REFERENCED TABLE NAME REFERENCED COLUMN NAME FROM   INFORMATION SCHEMA KEY COLUMN USAGE WHERE   REFERENCED TABLE SCHEMA     lt database gt   AND   REFERENCED TABLE NAME     lt table gt      For a Column   SELECT    TABLE NAME COLUMN NAME CONSTRAINT NAME  REFERENCED TABLE NAME REFERENCED COLUMN NAME FROM   INFORMATION SCHEMA KEY COLUMN USAGE WHERE   REFERENCED TABLE SCHEMA     lt database gt   AND   REFERENCED TABLE NAME     lt table gt   AND   REFERENCED COLUMN NAME     lt column gt      Basically  we changed REFERENCED TABLE NAME with REFERENCED COLUMN NAME in the where clause

User · Answer

Using REFERENCED TABLE NAME does not always work and can be a NULL value  The following query can work instead   select   from INFORMATION SCHEMA KEY COLUMN USAGE where TABLE NAME     lt table gt

User · Answer

Using REFERENCED TABLE NAME does not always work and can be a NULL value  The following query can work instead   select   from INFORMATION SCHEMA KEY COLUMN USAGE where TABLE NAME     lt table gt

User · Answer

A quick way to list your FKs  Foreign Key references  using the    KEY COLUMN USAGE view   SELECT CONCAT  table name       column name    - gt     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    This query does assume that the constraints and all referenced and referencing tables are in the same schema   Add your own comment   Source  the official mysql manual

User · Answer

Constraints in SQL are the rules defined for the data in a table  Constraints also limit the types of data that go into the table  If new data does not abide by these rules the action is aborted  select   from INFORMATION SCHEMA TABLE CONSTRAINTS where CONSTRAINT TYPE    FOREIGN KEY    You can view all constraints by using select   from information schema table constraints   This will produce a lot of table data   You can also use this for MySQL  show create table tableName

User · Answer

The solution I came up with is fragile  it relies on django s naming convention for foreign keys   USE information schema  tee mysql output SELECT   FROM TABLE CONSTRAINTS WHERE CONSTRAINT TYPE    FOREIGN KEY  AND TABLE SCHEMA    database name   notee   Then  in the shell   grep  refs tablename id  mysql output

User · Answer

I m reluctant to add yet another answer  but I ve had to beg  borrow and steal from the others to get what I want  which is a complete list of all the FK relationships on tables in a given schema  INCLUDING FKs to tables in other schemas  The two crucial recordsets are information schema KEY COLUMN USAGE and information schema referential constraints  If an attribute you want is missing  just uncomment the KCU   RC  to see what s available SELECT DISTINCT KCU TABLE NAME  KCU COLUMN NAME  REFERENCED TABLE SCHEMA  KCU REFERENCED TABLE NAME  KCU REFERENCED COLUMN NAME  UPDATE RULE  DELETE RULE    KCU    RC   FROM information schema KEY COLUMN USAGE KCU INNER JOIN information schema referential constraints RC ON KCU CONSTRAINT NAME   RC CONSTRAINT NAME WHERE TABLE SCHEMA    your schema name  AND KCU REFERENCED TABLE NAME IS NOT NULL ORDER BY KCU TABLE NAME  KCU COLUMN NAME

User · Answer

To find all tables containing a particular foreign key such as employee id  SELECT DISTINCT TABLE NAME  FROM INFORMATION SCHEMA COLUMNS WHERE COLUMN NAME IN   employee id   AND TABLE SCHEMA  table name

[mysql] How do I see all foreign keys to a table or column?

Examples related to mysql

Examples related to foreign-keys

Examples related to innodb