CODEWITHSUNDEEP
mysqlforeign-keys
Dan W.
Dan W.

Reputation: 107

Use foreign keys not just for restraints

I want to create a dynamic query that will know what table I am joining based on the foreign key. Is there a way to utilize a foreign key for that? I would like to create a statement that can look up where the foreign key points and use that to select the correct values. Something that would work like this:

"SELECT t.*, f.Name FROM $table t INNER JOIN {foreign table(will change based on the table selected and the foreign key it has)} f WHERE t.key = '$key'";

Upvotes: 0

Views: 38

Answers (1)

Álvaro González
Álvaro González

Reputation: 146660

Foreign key information can be queried from the information_schema.key_column_usage table. Remember that:

  • A foreign key may have several columns.
  • A table may have more than one foreign keys pointing to the same table.

It's kind of tricky to do that in plain SQL. You'd need to write a stored routine and generate dynamic SQL. It's also likely to kill performance. If you have (at it seems) PHP at your disposal the dynamic SQL generation probably becomes easier and you're also able to implement some caching mechanism so information_schema is not queried every time.

Upvotes: 1

Related Questions