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.

Try to delete it. If the db complains, it wasn't possible. ;) — BoltBait, Sep 14 '10 at 18:41
You can always delete the admin by deleting the connected stuff first then deleting the admin. — Bot, Sep 14 '10 at 18:44
Um, yeah, but then it will already be deleted, which might or might not be what the admin wanted :p I just want to display if it is a possiblity. — Svish, Sep 14 '10 at 18:59

score 4 · Answer 1 · edited Dec 21 '18 at 18:00

4

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 wouldn't you know in the first place if you could delete the row or not?

edited Dec 21 '18 at 18:00

Darryl Hein

142,451
95
218
261

answered Sep 14 '10 at 18:42

GrandmasterB

3,396
1
23
22

3

Because it could have various restrictions attached to it through foreign keys and I'd like to not have to manually check all of those because they could potentially change if I added or removed a table later. – Svish Sep 12 '12 at 09:25
You have a failure in business logic then. That type of verification should be done in your business layer, which would perform the necessary queries to ensure the user wasnt connected to anything. – GrandmasterB Sep 12 '12 at 18:53
3

Thought data integrity and checks like that was what foreign key restrictions was for? – Svish Sep 12 '12 at 21:11
1

If some guy is building a database manager (like PhpMyAdmin) where users can create their own tables and foreign keys (and their own business logic failures), he could get this issue, for those people building something like that this answer could be useful. – stramin Jan 22 '20 at 19:31

score 1 · Answer 2 · answered Sep 14 '10 at 18:42

1

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...

answered Sep 14 '10 at 18:42

OMG Ponies

325,700
82
523
502

score 1 · Answer 3 · answered Sep 14 '10 at 19:11

1

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;

answered Sep 14 '10 at 19:11

nos

223,662
58
417
506

score 0 · Answer 4 · edited May 23 '17 at 12:09

0

Try one of the answers here. MySQL: How to I find all tables that have foreign keys that reference particular table.column AND have values for those foreign keys?

edited May 23 '17 at 12:09

Community

1
1

answered Sep 14 '10 at 18:42

Bot

11,868
11
75
131

Check if a row could be deleted in MySQL

4 Answers4

Linked