Вмъкнете самореферентни данни в таблицата за копиране

Имам саморефериращо ограничение за външен ключ на моята маса. Тъй като трансформирам схемата, бих искал да копирам съществуващите данни в нова таблица с (повече или по-малко) същата структура:

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;

след, за да деактивирате проверката на ограничението за външен ключ, така че не е нужно да се притеснявате за реда на вмъкванията:

Вижте документацията за foreign_key_checks

проверки на чужд_ключ

Ако е зададено на 1 (по подразбиране), ограниченията на външния ключ за InnoDB таблици се проверяват. Ако е зададено на 0, такива ограничения се игнорират. [...] Обикновено оставяте тази настройка активирана по време на нормална работа, за да наложите референтна цялост. Деактивирането на проверката на външен ключ може да бъде полезно за презареждане на такива таблици в ред, различен от този, изискван от техните родителски/детелни взаимоотношения. Вижте раздел 14.6.6, „Ограничения на InnoDB и FOREIGN KEY“. [...]

Забележка

Задаването на Foreign_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