Как вы можете проверить ссылки на внешние ключи для списка записей, прежде чем пытаться удалить любую из этих записей в MySQL?

Question

Есть ли способ, когда у вас есть список записей, проверить, есть ли у каждой из этих записей ссылки на внешние ключи, прежде чем пытаться удалить какую-либо из этих записей?

Например, если у меня есть список заемщиков и список книг, вы не сможете удалить заемщика из системы, если у него все еще есть книги в кредит. (Моя настоящая система намного сложнее, чем эта - намного больше таблиц.)

Я хотел бы удалить опцию удаления из всех заемщиков, у которых есть книги в кредит (в этом примере).

Если я пытаюсь удалить запись со ссылками на внешний ключ, я получаю сообщение об ошибке:

Ошибка доступа к базе данных: невозможно удалить или обновить родительскую строку: ограничение внешнего ключа не выполнено (dbname,tablename, ОГРАНИЧЕНИЕ fkfieldid ИНОСТРАННЫЙ КЛЮЧ (fieldid) РЕКОМЕНДАЦИИ tablename (fieldid))

Одно из решений - написать запрос, чтобы проверить, имеет ли каждая запись в списке записей ссылки на внешние ключи в любой из возможных таблиц, на которые она может ссылаться.

Однако, если я хочу отобразить список из 100 записей из таблицы в моей системе управления контентом, и мне нужно выполнить 100 подзапросов, чтобы отобразить этот список, это, очевидно, очень неэффективно!

Конечные пользователи смущаются, когда пытаются удалить запись, но не могут, потому что эти данные "используются" в другом месте, поэтому я бы предпочел удалить опцию "удалить", чтобы избежать путаницы.

Любые идеи о том, что было бы лучше всего сделать? Благодарю.

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) Если ошибки не было -> Подтвердить

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