Check if a row could be deleted in MySQL

Question

Is there a way I can check if a row potentially could be deleted? That it for example is not currently connected through restricted foreign keys to anything else.

Reason: I am making an admin page with all the users in the system listed. They can always be disabled, but they may also be deleted. However they can only be deleted if they are not connected to anything critical. And I would like to not having to check that manually if it can be done easily in the database.

Note: I do not want to actually delete any user. I just want to display to the admin that a user could be deleted.

Answer 1

You could try deleting it as part of a transaction, and then roll back the transaction if it succeeds. BUT, I guess the immediate followup question is, why wouldnt you know in the first place if you could delete the row or not?

Answer 2

You could use a view to sum up the number of dependencies without having to worry about storing the data & keeping it current. When the number of dependencies is zero, make the delete option available in the UI...

Answer 3

Try one of the answers here. http://stackoverflow.com/questions/806989/mysql-how-to-i-find-all-tables-that-have-foreign-keys-that-reference-particular

Answer 4

You can get all orphaned rows by left joining to the table they're connected to, e.g. this will give you all the user id's that don't have any jobs.

SELECT u.id FROM users u LEFT JOIN jobs j on u.id=j.user_id WHERE j.user_id is null;