how to handle deletion of many objects from a certain table

Question

We have a user table which contains all the users from the company. As an administrator you have the permission to delete user entries. The UI shows all the users with a checkbox per row to indicate if it needs to be deleted. It's quite possible that other tables have foreign key relationship(s) with the user table and hence would prevent deletion, by throwing a foreign key constraint violation.

What would be the best practice to handle deletion in such cases, should the UI not show the checkbox if there could be other records which are dependent on this user which actually means that while rendering this user list page you need to do additional checks per row to find out if the checkbox needs to be enabled or not

What would be the best practice in this case.

Answer 1

First, I'd be surprised if you are allowed to DELETE the user records. It's more likely that you mark them locked or no-login-allowed or something. Sometimes it's important to preserve the data for historical purposes.

Second, if you really do need to delete the rows (in this scenario or some other scenario) you should read about foreign keys with the ON DELETE CASCADE or other options.

See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

When you delete the parent row (users), you can declare your desired behavior in foreign key constraints in dependent tables:

• RESTRICT, meaning don't delete the parent if there are dependent rows
• CASCADE, meaning delete any dependent rows automatically and atomically
• SET NULL, meaning change the value in the foreign key column in the child table to NULL
• SET DEFAULT, meaning change the value in the foreign key column to the default defined for that column

Of course these constraints work only in InnoDB. Foreign keys are not supported in MyISAM.

---

Re your comment: No, there's no way to quickly check for the presence of dependent rows referencing a given user row. These sort of stats would have to be managed on a user-by-user basis. So it would contain data on the order of replicating the dependent rows themselves. At that point, you might as well query the data directly, instead of the hypothetical statistics. Here's how I'd do it:

SELECT u.*
FROM Users u
LEFT OUTER JOIN SomeChildTable1 c1 ON (u.user_id = c1.user_id)
LEFT OUTER JOIN SomeChildTable2 c2 ON (u.user_id = c2.user_id)
...other tables
WHERE c1.user_id IS NULL AND c2.user_id IS NULL AND ...other terms

By using outer join, the columns in c1, c2, etc. are NULL when there's no match, and the columns in Users are returned whether there's a match or not. So the query returns only those Users that have no dependent rows.

Some people shy away from joins because they were told in some magazine article that joins are costly, but compared to other solutions to accomplish what you want, the joins don't look so bad. In this case, it can take advantage of an index on the user_id column in those foreign keys and not have to read the row data at all.