Вставьте самореферентные данные в таблицу копирования

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

CREATE TABLE test(
  id INT NOT NULL PRIMARY KEY,
  parent INT,
  FOREIGN KEY (parent) REFERENCES test(id)
);

CREATE TABLE copy(
  id INT NOT NULL PRIMARY KEY,
  parent INT,
  FOREIGN KEY (parent) REFERENCES copy(id) 
);

Однако при вставке данных:

INSERT INTO copy(id, parent) SELECT id, parent FROM test;

MySQL дает мне общую ошибку целостности:

Error Code: 1452. Cannot add or update a child row:
    a foreign key constraint fails (`test`.`copy`, CONSTRAINT `copy_ibfk_1`
    FOREIGN KEY (`parent`) REFERENCES `copy` (`id`))

Похоже, что MySQL проверяет ограничение после вставки каждой строки, а не после всей вставки. Точно такой же пример отлично работает в PostgreSQL.

Есть ли другой способ вставить эти данные, или я застрял с этим в два этапа?

INSERT INTO copy(id) SELECT id FROM test;
UPDATE copy
JOIN test ON test.id = copy.id
SET copy.parent = test.parent;

mysql sql foreign-keys foreign-key-relationship
person r0estir0bbe    schedule 21.07.2014    source источник
comment
С MySQL у вас есть только эти два варианта: отключить ограничения (как показано VMai) или выполнить вставку/обновление в одной транзакции (как вы делаете сейчас).   -  person a_horse_with_no_name    schedule 21.07.2014

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете использовать

SET FOREIGN_KEY_CHECKS=0;

перед вашим INSERT и

SET FOREIGN_KEY_CHECKS=1;

after отключить проверку ограничения внешнего ключа, поэтому вам не нужно беспокоиться о порядке вставок:

См. документацию по foreign_key_checks.

external_key_checks

Если установлено значение 1 (по умолчанию), проверяются ограничения внешнего ключа для таблиц InnoDB. Если установлено значение 0, такие ограничения игнорируются. [...] Обычно вы оставляете этот параметр включенным во время нормальной работы, чтобы обеспечить ссылочную целостность. Отключение проверки внешнего ключа может быть полезно для перезагрузки таких таблиц в порядке, отличном от порядка, требуемого их отношениями родитель/потомок. См. Раздел 14.6.6, «Ограничения InnoDB и FOREIGN KEY». [...]

Примечание

Установка для external_key_checks значения 1 не запускает сканирование существующих данных таблицы. Таким образом, строки, добавленные в таблицу, когда Foreign_key_checks = 0, не будут проверяться на согласованность.

CREATE TABLE test(
  id INT NOT NULL PRIMARY KEY,
  parent INT,
  FOREIGN KEY (parent) REFERENCES test(id) 
);

CREATE TABLE copy(
  id INT NOT NULL PRIMARY KEY,
  parent INT,
  FOREIGN KEY (parent) REFERENCES copy(id) 
);

INSERT INTO test(id, parent) VALUES(1, null);
INSERT INTO test(id, parent) VALUES(2, 1);
INSERT INTO test(id, parent) VALUES(3, null);
INSERT INTO test(id, parent) VALUES(4, 2);
UPDATE test SET parent=3 WHERE id=1;

SET FOREIGN_KEY_CHECKS=0;

-- Success with MySQL
INSERT INTO copy(id, parent) SELECT id, parent FROM test;

SET FOREIGN_KEY_CHECKS=1;

Ваша обновленная скрипта: http://sqlfiddle.com/#!2/ae623/1

person VMai    schedule 21.07.2014
comment
Есть ли в MySQL транзакционная поддержка команд SET? (Я предполагаю, что нет). Другими словами: что произойдет, если транзакция завершится ошибкой до/во время/после вставки? Я бы предпочел не отказываться от гарантии ограничения... - person r0estir0bbe; 21.07.2014
comment
@ r0estir0bbe Я не вижу никаких проблем с разовой работой. - person VMai; 21.07.2014