I am producing a data dictionary for a particular schema in MySQL. So far, I have the table_name, columm_name, column_type (as below in the SELECT statement). How do I include what the foreign key constraints are as well, in the same data dictionary? I would like to put the foreign key info. into this format:
CONCAT(table_name, '.', column_name, ' -> ', referenced_table_name, '.', referenced_column_name)
select
t.table_name,
c.column_name,
c.column_type
from
information_schema.tables as t
inner join
information_schema.columns as c ON t.table_name = c.table_name
and t.table_schema = c.table_schema
where
t.table_type in ('base table' , 'view')
and t.table_schema like 'mySchema'
order by t.table_schema , t.table_name , c.ordinal_position;
The list of table constraints can be found in table_constraints table. From this table you can fetch all foreign keys by getting all rows with:
constraint_type='FOREIGN KEY'
This will give you just a list of foreign keys, if you need more details (e.g. which table and column they point to) have a look at key_column_usage table:
mysql> describe key_column_usage;
+-------------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------------+--------------+------+-----+---------+-------+
| CONSTRAINT_CATALOG | varchar(512) | NO | | | |
| CONSTRAINT_SCHEMA | varchar(64) | NO | | | |
| CONSTRAINT_NAME | varchar(64) | NO | | | |
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| COLUMN_NAME | varchar(64) | NO | | | |
| ORDINAL_POSITION | bigint(10) | NO | | 0 | |
| POSITION_IN_UNIQUE_CONSTRAINT | bigint(10) | YES | | NULL | |
| REFERENCED_TABLE_SCHEMA | varchar(64) | YES | | NULL | |
| REFERENCED_TABLE_NAME | varchar(64) | YES | | NULL | |
| REFERENCED_COLUMN_NAME | varchar(64) | YES | | NULL | |
+-------------------------------+--------------+------+-----+---------+-------+
12 rows in set (0.00 sec)