CODEWITHSUNDEEP
phpmysqlforeign-key-relationship
boctulus
boctulus

Reputation: 395

How can I retrieve referenced tables (FKs) using PHP from MySQL?

Using SHOW TABLE and SHOW COLUMNS , I can get info about tables (fields, if they are PRIMARY KEYS, FK, etc...) but NOTHING about outlinks of foreign keys!

EDIT: Thanks a lot... I will have the reference

http://dev.mysql.com/doc/refman/5.0/es/key-column-usage-table.html

at information_schema.KEY_COLUMN_USAGE TABLE_NAME.COLUMN_NAME (links to) REFERENCED_TABLE_NAME.REFERENCED_COLUMN_NAME

Upvotes: 2

Views: 158

Answers (1)

webbiedave
webbiedave

Reputation: 48897

You can use the information_schema.key_column_usage table:

SELECT table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL

If you need to limit your query to a particular database, you can add AND table_schema = 'yourdatabase'.

Upvotes: 3

Related Questions