How to find foreign keys while scripting MySQL Workbench

Question

I have created a DB Model and EER diagram in MySQL Workbench. I am using the workbench scripting feature to write a script to generate code to use the database after it is exported and created on a MySQL server.

As I loop over the tables, is there any way I can find foreign keys that link to the current table?

There is a 'foreignKeys' attribute on the table object, but it contains only the foreign keys pointing AWAY from the table, not those pointing to it. I want to know what other tables have foreign keys that link to the primary key of the current table (ideally without looping over every column in every other table in the model).

Answer 1

You can use below query to retrieve all the constraint/foreign key in DB..

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = 'database_name';

to be more specific to your question, you can use below query..

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = 'database_name' AND
  REFERENCED_TABLE_NAME = 'table_name';