How to get list of database's table's foreign keys?

[jambrek]

Hi,

I build application which can read all tables on all databases on specified server (MySQL 5.0), so I don't know how selected table look like. I have to get list of all foreign keys (if is there any) for selected table. I found class QSqlRelationalTableModel, but there I must to setRelation with exact names of columns, but in my case I don't know which columns are fk and on which table's column pointing at. In database are defined fk's and when I open database in MySQL-Front I can see all relations. I have to see relations from my Qt application.

Thanks.

[jacek]

You can query information_schema.table_constraints table.

[Auryn]

This PostgreSQL view helps me a lot when I want more information about foreign keys:

CREATE OR REPLACE VIEW my_foreign_keys AS 
 SELECT s1.constraint_name, s1.table_name, s1.column_name, s1.ordinal_position, s2.table_name_ref, s2.column_name_ref, s2.ordinal_position_ref
   FROM ( SELECT key_column_usage.constraint_name, key_column_usage.table_name, key_column_usage.column_name, columns.ordinal_position
           FROM information_schema.key_column_usage
      JOIN information_schema.columns USING (table_name, column_name)) s1
   JOIN ( SELECT constraint_column_usage.constraint_name, constraint_column_usage.table_name AS table_name_ref, constraint_column_usage.column_name AS column_name_ref, cols_ref.ordinal_position AS ordinal_position_ref
           FROM information_schema.constraint_column_usage
      JOIN information_schema.columns cols_ref ON cols_ref.table_name::text = constraint_column_usage.table_name::text AND cols_ref.column_name::text = constraint_column_usage.column_name::text) s2 ON s1.constraint_name::text = s2.constraint_name::text AND NOT s1.table_name::text = s2.table_name_ref::text;

CREATE OR REPLACE VIEW my_foreign_keys AS 
 SELECT s1.constraint_name, s1.table_name, s1.column_name, s1.ordinal_position, s2.table_name_ref, s2.column_name_ref, s2.ordinal_position_ref
   FROM ( SELECT key_column_usage.constraint_name, key_column_usage.table_name, key_column_usage.column_name, columns.ordinal_position
           FROM information_schema.key_column_usage
      JOIN information_schema.columns USING (table_name, column_name)) s1
   JOIN ( SELECT constraint_column_usage.constraint_name, constraint_column_usage.table_name AS table_name_ref, constraint_column_usage.column_name AS column_name_ref, cols_ref.ordinal_position AS ordinal_position_ref
           FROM information_schema.constraint_column_usage
      JOIN information_schema.columns cols_ref ON cols_ref.table_name::text = constraint_column_usage.table_name::text AND cols_ref.column_name::text = constraint_column_usage.column_name::text) s2 ON s1.constraint_name::text = s2.constraint_name::text AND NOT s1.table_name::text = s2.table_name_ref::text;

To copy to clipboard, switch view to plain text mode 

The table obtained is:
constraint_name | table_name | column_name | ordinal_position | table_name_ref | column_name_ref | ordinal_position_ref

I am not a guru, so use it with care.

[daggilli]

If you are using a 4.x version of MySQL, you don't have the luxury of INFORMATION_SCHEMATA, but you can get at a table's foreign keys by issuing a SHOW CREATE TABLE xxx; query and parsing the return values for rows which look like CONSTRAINT xxx_ibfk_n FOREIGN KEY (`abc`) REFERENCES `yyy`(`def`), where abc is the foreign key column in your xxx table and def is the referenced column in your yyy table. With judicious use of Qt or STL container classes you can easily build the referential structure as a tree, which then lends itself very well to post-processing in order to extract the reference hierarchy. I have a home-brewed tool which uses this exact approach to document a corporate database (it goes one step further by generating LaTeX documentation and then calling pdflatex to build a document the developers can see via the intranet).