MySQL check 'deletability' of record

Question

Consider the following table structure (which I have shamelessly nicked from this excellent answer):

categories:     products:
+----+------+   +----+---------+
| id | name |   | id | name    |
+----+------+   +----+---------+
| 1  | red  |   | 1  | mittens |
| 2  | blue |   | 2  | boots   |
+---++------+   +----+---------+

products_categories:
+------------+-------------+
| product_id | category_id |
+------------+-------------+
| 1          | 1           | // red mittens
| 1          | 2           | // blue mittens
| 2          | 1           | // red boots
| 2          | 2           | // blue boots
+------------+-------------+

The foreign keys for products_categories in this scenario are set up as ON DELETE CASCADE, so when I DELETE FROM categories WHERE id = 2; all records having 2 as their category_id will get deleted.

Suppose I alter the foreign key for the category to ON DELETE RESTRICT (which seems to be the default value anyway). Deleting any record from categories will now fail, as long as there is a record in products_categories which references the record in question.

Is there any way to determine if a DELETE on a specific record will fail? The DB clearly is able to evaluate this as part of the DELETE query.

I have a program (C#) which might want to delete a record from a table that is fenced in by a RESTRICT constraint. At the moment I have to try { } catch(Exception ex) { } the DELETE query, which will throw an exception if the record cannot be deleted due to constraints. I feel that this kind flow control through exceptions for this task is not a good practice, and it seems that there is no obvious way around it. Or is there?

I can see, that having an opportunity to query the database if a record can be deleted, might introduce a lot of concurrency issues (Suppose the record cannot be deleted and right after I get my (negative) result, another user deletes the final record that made the test fail. I then have invalid information about the state of the database on my hands.

I consulted this and that neither of which gave me a satisfactory answer.

Answer 1

Here's a prototype for generating queries that check if rows exist in child tables:

Suppose you have a table parent with two child tables child1 and child2, each have a foreign key to the parent.

If you need to check if any rows exist in the child table that reference parent's primary key value 1:

select concat('select exists(select * from `', table_schema, '`.`', table_name,
  '` where `', column_name, '`=1 for update)') AS _sql
from information_schema.key_column_usage
where referenced_table_name='parent';

Output:

+-----------------------------------------------------------------------------+
| _sql                                                                        |
+-----------------------------------------------------------------------------+
| select exists(select * from `test`.`child1` where `parent_id`=1 for update) |
| select exists(select * from `test`.`child2` where `parent_id`=1 for update) |
+-----------------------------------------------------------------------------+

Demo: https://dbfiddle.uk/34gVPVr9

By using for update, this will create a locking read on the rows it finds. This will prevent other sessions from deleting those rows until the end of your transaction.

However, even for update won't prevent another race condition: some concurrent session could insert new rows right after you run your select queries, so you might have an incorrect assumption that a row in parent is ready to be deleted, even though it will have dependent rows by the time you delete it.

The only way to resolve that second race condition is to reserve table locks on all the child table(s), to prevent insertion. You can use LOCK TABLES to do this, but it's a pretty invasive solution, because it creates a concurrency bottleneck in your application.

I recommend that it's simpler and more reliable to do what you originally did, just attempt the delete of a row in parent and rely on an exception to tell you if it was successful.

I understand that there is common advice against using exception handling as flow control, but I think in this case, using exceptions to prevent breaking referential integrity without race conditions, it is the lesser evil.