1

Using PHP, when I'm about to delete a row from a table I want to able to determine if it's PK value is used by any other table

I have a table where it's PK may be FK in one or several tables. At first hand I don't know how many tables are related.

What could be the most efficient and shortest way to achieve what I'm looking for?

My first approach is to get which tables are related to, doing something like:

select table_name, column_name from information_schema.key_column_usage where 
  table_schema = 'my_schema' and referenced_table_name = 'my_table' and 
  referenced_column_name = 'id_field'

Then loop through that list and for each table do something like:

select count(*) as total from table_name_from_loop where id_field = my_value

So if I any of those count() is greater than 0, then that row is in use.

Any suggestion on how to perform this task? I prefer some kind of check instead of trying to delete and catch some error

EDIT 201309091540: Anyone?

Matías Cánepa
  • 5,770
  • 4
  • 57
  • 97
  • I think this other question will be helpful http://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column – Brook Julias Sep 05 '13 at 14:28
  • if you don't care if there are or not any FK and just want to leave everything related to that row deleted you could just do 2 DELETE query, the second one using the PK of the 1st. If it's an indexed field (a FK should be) it will be pretty fast. If there are no FK the second query won't do anything. – Naryl Sep 05 '13 at 14:40
  • What's wrong with deleting and then catching the error? The database will do exactly the same work as if you did the check manually. If you provide proper names for the FK constraints you can even extract the information *where* the row is referenced. –  Sep 05 '13 at 16:32
  • This is dangerous: there might be tables with foreign keys referencing your table without specified foreign key constrains. This is sad, but still possible, if you have a legacy database. – proskor Sep 05 '13 at 16:33
  • @BrookJulias That's what I'm doing in the fisrt query – Matías Cánepa Sep 06 '13 at 12:38
  • @a_horse_with_no_name The reason for doing this is to provide a more friendly message to the user on which tables are related. – Matías Cánepa Sep 06 '13 at 12:42
  • @proskor All tables has the proper FK and constrains – Matías Cánepa Sep 06 '13 at 12:43
  • @Matías: that's what I meant with "proper names for the FK constraints". If you stick to a certain naming schema, you should be able to generate the "friendly message" to the user from MySQL's error message. –  Sep 06 '13 at 12:50

0 Answers0