How can you check foreign key references for a list of records before trying to delete any of these records in MySQL?

Question

Is there a way, when you have a list of entries, to check if each of these entries has foreign key references before trying to delete any of these entries?

As an example, if I have a list of borrowers and a list of books, you cannot delete a borrower from the system if he still has books on credit. (My actual system is much more complicated than this - many more tables.)

I would like to remove the delete option from any borrowers who have books on credit (in this example).

If I try to delete an entry using foreign key references, I get an error message:

Error accessing database: cannot delete or update parent line: foreign key constraint ends with an error ( dbname. tablename, CONSTRAINT fkfieldidFOREIGN KEY ( fieldid) LINKS tablename( fieldid))

One solution is to write a query to check whether each entry in the list of entries has any links to foreign keys in any of the possible tables that can be referenced.

However, if I want to display a list of 100 records from a table in my content management system, and I need to run 100 subqueries to display this list, this is clearly very inefficient!

End users get confused when they try to delete a record, but they cannot, because this data is “used” elsewhere, so I would rather remove the delete option to avoid confusion.

, ? .

Answer 1

, , .

. -

SELECT B.*, (SELECT COUNT(*) FROM loans L WHERE L.book_id = b.id) loan_count
FROM books B

( , , ):

SELECT B.*, L.book_id AS loaned_out_if_not_null
FROM books B
    LEFT JOIN loans L ON B.id = L.book_id

, , GROUP BY:

SELECT B.id, B.name, COUNT(L.book_id) AS loan_count
FROM books B
    LEFT JOIN loans L ON B.id = L.book_id
GROUP BY B.id, B.name

CASE, , DISTINCT (, DISTINCT ):

SELECT DISTINCT B.*,
    CASE WHEN L.book_id IS NOT NULL THEN 1 ELSE 0 END AS loaned_out_if_one
FROM books B
    LEFT JOIN loans L ON B.id = L.book_id

GROUP BY.

Answer 2

, .

1) .

2a) → 2b) → Commit

http://dev.mysql.com/doc/refman/5.0/en/commit.html