Get MySQL primary key and foreign keys constraints from PHP?

Question

I'm building a datagrid application integrated with Smarty, using PDO/PHP/MySQL.

Is there a fast and convenient way (using db schema or some PDO command) to obtain in PHP:

• a table's primary key
• a table's foreign key constraints list?

(I'm using InnoDB as schema).

Answer 1

You can get key information by querying the information_schema table:

SELECT * 
FROM information_schema.KEY_COLUMN_USAGE 
WHERE REFERENCED_TABLE_NAME = 'my_table_name'

MySQL: How to I find all tables that have foreign keys that reference particular table.column AND have values for those foreign keys?

You can get the primary key by using SHOW TABLE:

SHOW INDEX FROM my_table_name 
WHERE key_name = 'PRIMARY'

MySQL: Determine Table's Primary Key Dynamically

Quering the information_schema table can be slow on server with lots of databases/tables.

You can use this to speed it up:

SET GLOBAL innodb_stats_on_metadata = 0

http://www.mysqlperformanceblog.com/2011/12/23/solving-information_schema-slowness/