Определить программно, какое ограничение FOREIGN KEY не удалось
Итак, предположим следующую (упрощенную) настройку:
CREATE TABLE Table1(
`a` int PRIMARY KEY
) ENGINE=INNODB;
INSERT INTO Table1 (`a`) VALUES (1),(2),(3);
CREATE TABLE Table2(
`a` int,
FOREIGN KEY (`a`) REFERENCES Table1(`a`) ON DELETE CASCADE
) ENGINE=INNODB;
У нас есть две таблицы, из которых вторая имеет внешний ключ, ссылающийся на первую.
Если я выполню что-то вроде
INSERT INTO `Table2` VALUES ( 1 );
Все отлично. Но если я выполню
INSERT INTO `Table2` VALUES ( 5 );
Я получаю сообщение об ошибке вроде
# 1452 — Невозможно добавить или обновить дочернюю строку: ограничение внешнего ключа не выполняется (
table2, ОГРАНИЧЕНИЕtable2_ibfk_1ИНОСТРАННЫЙ КЛЮЧ (a) РЕКОМЕНДАЦИИtable1(a) НА УДАЛЕННОМ КАСКАДЕ)
что правильно.
Пока у меня есть только один внешний ключ, нет проблем, чтобы увидеть, что это не удалось. Но при условии, что у меня есть более одного ограничения внешнего ключа:
Как я могу программно определить в PHP, какой из них не удалось?
Единственный способ, о котором я могу думать, — это анализ строки ошибки и попытка извлечь соответствующие столбцы, но это очень нестабильно, если MySQL решает изменить синтаксис своих сообщений об ошибках.
Некоторый фон: есть базовая модель данных, такая как звезда-схема, который просто представлен в базе данных. Я не хочу проверять все ограничения в PHP, так как это потребует либо удаления большого количества данных из базы данных (список возможных значений для внешних ключей довольно длинный), либо запускает много запросов, чтобы увидеть, если значения существуют в соответствующих таблицах измерений. Оба не кажутся хорошим подходом. Тем более, что MySQL все равно проверяет эти ограничения.
Решение
Самый простой способ — назвать ваши ограничения:
CREATE TABLE Table2(
`a` int,
CONSTRAINT FK_table2_a FOREIGN KEY (`a`) REFERENCES Table1(`a`) ON DELETE CASCADE
) ENGINE=INNODB;
Тогда сообщение об ошибке скажет что-то вразумительное.
Другие решения
Ваша единственная задача — выяснить, INSERT потерпит неудачу или нет. Не уверен, насколько эффективно вы думаете, но вы можете
определить BEFORE INSERT триггер на вашей таблице и заставить триггер выдать специальное предупреждающее сообщение или зарегистрировать это сообщение в таблице журнала ошибок. Нечто подобное (грубая идея)
DELIMITER $$
CREATE TRIGGER `test_before_insert` BEFORE INSERT ON `Table2`
FOR EACH ROW
BEGIN
DECLARE @row_count INT;
SELECT COUNT(*) INTO @row_count FROM Table1 WHERE `a` = NEW.a;
IF @row_count <= 0 THEN
INSERT INTO Error_Log(`detail`, `value`)
VALUES('Insert failed for value ', NEW.a);
END IF;
END$$
DELIMITER ;
Затем, если вообще для некоторого значения (й) a операция вставки завершается неудачно, вы уверены, что та же информация будет доступна в Error_Log Таблица; который может напрямую получать из вашего кода приложения (PHP).