Determine name of table referenced by a foreign key from another table using PHP?

Just learning mysql and php and having to create a test project for my exam.

How do I determine the name of a table (target) a foreign key from another table (source) references, and how would I go about that with PHP?

I have been googling this as well as browsing SO for it for hours, but have not found a working solution.

Edit: I see I need to be more precise. I want to look at a foreign key column definition in table A and find out which other table in my database it references, all this without knowing the referenced table’s name. I also want this to work when table A is empty. So I guess I somehow need to access the definition / description (scheme?) of table A, look at what is stored there for the foreign key column in question and determine the target table that way.

Example:

I have a table “products” (source) and a table “manufacturers” (target).

“products” has a column “manufacturer” which is a foreign key referencing “manufacturers”.

Now I want to create some php code that looks at the column “manufacturers” of table “products” and finds out that foreign keys stored in this column would reference table “manufacturers”. All this without the php knowing the referenced table in advance. This should also work if the table “products” is empty, i.e. does not contain any foreign key values.

So any SQL query already requiring me to enter the referenced table’s name seems pointless to me.

I tried

CREATE TABLE manufacturers (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100)
    );

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100),
    manufacturer INTEGER
    );

ALTER TABLE products ADD FOREIGN KEY(manufacturer) REFERENCES manufacturers(id);

SELECT TABLE_NAME,
       COLUMN_NAME,
       REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = "products"
  AND REFERENCED_TABLE_NAME IS NOT NULL;

But the result was empty.

Edit 2:

I found out that INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn’t exist in my entire sql installation. Or at least I can see it nowhere.

Edit 3:

I found my mistake. Instead of “DATABASE()”, I have to specify my actual database name.

Thanks for all the help. 🙂

Answer

SELECT CONSTRAINT_SCHEMA,
       CONSTRAINT_NAME,
       TABLE_SCHEMA,
       TABLE_NAME,
       COLUMN_NAME,
       REFERENCED_TABLE_SCHEMA,
       REFERENCED_TABLE_NAME,
       REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = slave_database
  AND TABLE_NAME = slave_table
  AND REFERENCED_TABLE_NAME IS NOT NULL;

The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

---

I want to look at a foreign key in table A and find out which other table in my database it references without knowing that table’s name.

What a problem? Absolutely the same:

SELECT CONSTRAINT_SCHEMA,
       CONSTRAINT_NAME,
       TABLE_SCHEMA,
       TABLE_NAME,
       COLUMN_NAME,
       REFERENCED_TABLE_SCHEMA,
       REFERENCED_TABLE_NAME,
       REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = main_database
  AND REFERENCED_TABLE_NAME = main_table;