MySQL - Find entries that refer to a specified index.

Question

Hi,

So I have a booking system where I have a 'lesson_type' table with 'lesson_type_id' as PK. I have a constraint in place here so I can't delete a lesson_type if there are bookings made for that lesson_type.

I would like to be able to determine if this lesson_type_id is being referred to by any entries in the bookings table (or any other table for that matter) so I can notify the user gracefully. i.e. not have a mysql error be thrown when they try and delete a record.

What kind of query would I use for this?

Thanks.

Answer 1

SELECT COUNT(*) FROM bookings WHERE lesson_type_id = ?your_id LIMIT 1

The result is 0 if there are no references and 1 if there are some. Repeat this for any other tables.

Another way is to just run the query, catch the error, check its type and display the notice if it's a constraint error. The way to do this depends on what you're using to access MySQL.

Answer 2

delete bookings from 
FROM bookings inner join
(
  select lesson_type_id, count(*) as c
  from bookings group by lesson_type_id
) bookings_count
on bookings.lesson_type_id = bookings_count.lesson_type_id
WHERE bookings.lesson_type_id = ? and bookings_count.c > 0

that way you can do the delete and check in one step. You can then select the record you wanted to delete directly afterwards (or use the resulting rowcount()) to inform the user if the delete was successful of not.