9

I'm working on implementing a function to prevent removal of certain elements in a database (through the front end) if they have other items associated with them in other tables. Otherwise those other tables are looking for keys that aren't there.

If you understood that my hat is off to you.

I have many sets of tables to look through and need either a SQL query or a MySQL Workbench feature that can tell me, on entry of the primary key (column name, not actual value), if that key is used as a foreign key somewhere else.

Otherwise if anyone knows an offhand workaround, that would be great too!

spuriosity
  • 273
  • 5
  • 16
  • possible duplicate of [MySQL: How to I find all tables that have foreign keys that reference particular table.column AND have values for those foreign keys?](http://stackoverflow.com/questions/806989/mysql-how-to-i-find-all-tables-that-have-foreign-keys-that-reference-particular) – Johan Oct 06 '11 at 10:06
  • Why not simply try to delete the item and catch any error that you get from the FK violation? –  Oct 06 '11 at 10:07
  • See: http://dev.mysql.com/doc/refman/5.0/en/table-constraints-table.html (specific) and http://dev.mysql.com/doc/refman/5.0/en/information-schema.html (in general) – Johan Oct 06 '11 at 10:08
  • @a_horse_with_no_name Tried that, but the tables are all very interdependant and I nearly screwed myself up royally. I crashed the app on tablemapping until I could search through and find the foreign key references, but it took a long time. – spuriosity Oct 06 '11 at 10:12

2 Answers2

9
SELECT 
  table_name, column_name     
FROM
  information_schema.key_column_usage
WHERE
  referenced_table_name = '<table>'
  and referenced_column_name = '<primary key column>'
Gihan
  • 4,163
  • 6
  • 31
  • 49
StevieG
  • 8,639
  • 23
  • 31
  • key_column_usage doesn't exist? – spuriosity Oct 06 '11 at 10:32
  • That is probably because the username you're connecting with doesn't have the access rights to view the table.. – StevieG Oct 06 '11 at 10:43
  • sorry, meant to update, had to reopen my interface, it was stuck inside the schema (randomly, don't ask!). Anyway, found it and used your code, but am getting the following error: Error Code: 1054. Unknown column 'operatingsystem' in 'where clause'. operating system is a table name and is under referenced-table-name in the query... – spuriosity Oct 06 '11 at 10:50
0

A solution is described in this post to retrieve this information from information_Schema table.

1) If you want to work on these tables from your code, then fetch them as a container, for example ArrayList in your code and perform your logic.

2) If you want to work on these tables from your Stored Procedure, then use temporary tables to achive the same work you'd do in your java code through containers.

Community
  • 1
  • 1
Amir Ali
  • 26
  • 3