Как я могу проверить ссылочную целостность в данной строке после добавления строки?

Я пытаюсь узнать, есть ли способ проверить ссылочную целостность одной строки после того, как она была вставлена ​​в таблицу InnoDB, когда foreign_key_checks установлен в 0.

Итак, даны две таблицы:

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `author_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `book_cc846901` (`author_id`),
  CONSTRAINT `author_id_refs_id_7fdd0933` FOREIGN KEY (`author_id`) REFERENCES `person` (`id`)
) ENGINE=InnoDB;


CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Мы выдвигаем против них следующие утверждения:

SET foreign_key_checks=0
INSERT INTO `book` (`id`, `name`, `author_id`) VALUES (1, 'Cryptonomicon', 3)
INSERT INTO `person` (`id`, `name`) VALUES (4, 'Neal Stephenson')
SET foreign_key_checks=1

Итак, мы поместили сюда некорректные данные - author_id относится к несуществующему person.id 3. Я пытаюсь найти способ запустить какую-либо проверку в этой строке, которая выдаст ошибку, если есть проблема со ссылкой. как этот.

Подход, который я изначально пытался использовать, заключался в обновлении строки с теми же данными. Я пробовал это:

UPDATE `book` SET `name` = 'Cryptonomicon', `author_id` = 3 WHERE `book`.`id` = 1

Я ожидал, что это вызовет ошибку, но этого не произошло. Поскольку данные не менялись, видимо проверка целостности не производится? Например, установка author_id на 2 приводит к ошибке.

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

Любые идеи или другие подходы, которые ускользают от меня, будут оценены.

(Если вам интересно, почему я пытаюсь сделать все это, потому что я пытаюсь помочь решить проблему открытая проблема в платформе Django, из-за которой фикстуры с прямыми ссылками не могут быть загружены. Предлагаемое решение - отключить проверку внешнего ключа при загрузке фикстур и повторно включить их после завершения загрузки. Я пытаюсь выяснить, как в этот момент вернуться и проверить ссылочную целостность строк, которые были добавлены, когда проверки внешнего ключа были отключены, и вызвать ошибку, если есть проблема.)


mysql innodb referential-integrity
person jsdalton    schedule 24.06.2011    source источник
comment
Вы смотрели здесь: stackoverflow.com/questions/3727905/ Короче говоря, отложенная проверка ограничений недоступна в MySQL AFAIK.   -  person Mike    schedule 24.06.2011
comment
@ Майк, я видел это. Я понимаю, что нет ни одной команды, которая будет выполнять проверку, но я надеюсь найти какую-то альтернативу, которая вызовет ее (например, взлом UPDATE, который я показал выше - я даже не совсем уверен, почему эта команда не выполняет не работает).   -  person jsdalton    schedule 24.06.2011
comment
Ничего не встречал. Возможность отключить ограничения внешнего ключа без возможности проверки целостности после их включения - это одна из тех вещей, которые иногда начинают священную войну на некоторых форумах!   -  person Mike    schedule 24.06.2011

Ответы (2)


arrow_upward
8
arrow_downward

Я нашел решение, которое я получил благодаря ответу на этот вопрос: Заставить InnoDB перепроверить внешние ключи в таблице / таблицах?

Я видел этот вопрос раньше, но, наконец, я потратил около 20 минут, распаковывая все абстракции и пытаясь понять, что он делает в конкретных терминах. На самом деле это не так уж и сложно. Формулируя суть решения в терминах примера, который я привел в этом вопросе, у вас в основном есть два оператора SELECT:

SELECT *
FROM information_schema.KEY_COLUMN_USAGE
WHERE
    `CONSTRAINT_SCHEMA` LIKE `test`
    AND `TABLE_NAME` = `book`

Это вернет все ключевые столбцы в таблице book. Мы можем перебирать эти результаты и проверять их ссылки следующим образом - в этом примере с помощью author_id:

SELECT * FROM `book` as REFERRING
LEFT JOIN `person` as REFERRED
ON (REFERRING.`author_id` = REFERRED.`id`)
WHERE REFERRING.`author_id` IS NOT NULL
AND REFERRED.`id` IS NULL

По сути, этот оператор будет возвращать любые строки, в которых есть значение в author_id, но нет соответствующего значения для id в связанной таблице. Это «плохие строки».

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

person jsdalton    schedule 24.06.2011
comment
... но какая польза от выбора из информационной схемы, если вы уже знаете столбцы с ограничениями? - person Mike; 25.06.2011
comment
@Mike Да, извините, эта часть имела отношение ко мне, но не обязательно была частью того, как я сформулировал вопрос. По сути, мне нужно применить этот шаблон к любой таблице и набору данных, поэтому сначала мне нужно каким-то образом установить, какие столбцы имеют внешние ключи, а оттуда определить, являются ли какие-либо из внешних ключей плохими. На самом деле второй оператор SQL - это ядро ​​решения, вы правы. - person jsdalton; 25.06.2011

arrow_upward
1
arrow_downward

Уловка UPDATE не сработала, потому что MySQL проигнорировал обновление, так как ничего не изменилось:

UPDATE `book` SET
  `name` = 'Cryptonomicon',
  `author_id` = 3
WHERE `book`.`id` = 1;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Поэтому вы можете сначала обновить его до другого значения, а затем вернуться к исходному значению, которое затем вызовет желаемую ошибку. Я обернул все это в транзакцию, чтобы ее можно было откатить, не затрагивая базу данных:

START TRANSACTION;

SET foreign_key_checks=0;

INSERT INTO `book` (`id`, `name`, `author_id`)
VALUES (1, 'Cryptonomicon', 3);

SET foreign_key_checks=1;

INSERT INTO `person` (`name`) VALUES ('NULL');

UPDATE `book` SET
  `author_id` = LAST_INSERT_ID()
WHERE `book`.`id` = 1;

UPDATE `book` SET
  `author_id` = 3
WHERE `book`.`id` = 1;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`book`, CONSTRAINT `author_id_refs_id_7fdd0933` FOREIGN KEY (`author_id`) REFERENCES `person` (`id`))

ROLLBACK;

Это кажется беспорядочным и потенциально опасным решением, но я недостаточно знаю об исходной проблеме ... Я также не знаю, действительно ли вы хотите сохранить исходное значение в таблице. Если вы это сделаете, вам придется снова отключить ограничения внешнего ключа, чтобы сбросить его!

person Mike    schedule 24.06.2011
comment
благодаря. Смотрите ответ, который я только что опубликовал - вот как я наконец решил эту проблему. Я тоже обдумывал ваш подход, но тоже решил, что работать с ним немного хакерски. - person jsdalton; 25.06.2011