Каков правильный порядок вставки/удаления/модификации в наборе данных?

MSDN утверждает, что заказ:

Таблица детей: удаление записей.
Родительская таблица: вставка, обновление и удаление записей.
Таблица детей: вставка и обновление записей.

У меня проблема с этим.

Пример: ParentTable имеет две записи parent1 (Id: 1) и parent2 (Id: 2)

ChildTable имеет запись child1 (Id: 1, ParentId: 1)

Если мы обновим child1, чтобы иметь новый родительский родительский элемент2, а затем мы удалим parent1.

Нам нечего удалять в дочерней таблице
Мы удаляем parent1: мы сломали ограничение, потому что дочерний элемент все еще привязан к parent1, если мы сначала не обновим его.

Итак, что такое правильный порядок, и является ли MSDN ложным по этому вопросу?

Мои личные мысли

Таблица детей: удаление записей.
Родительская таблица: вставить, обновить записи.
Таблица детей: вставка и обновление записей.
Родительская таблица: удаление записей.

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

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

Ответ 1

Не поддерживает ли ваш SQL-запрос отложенную проверку ограничений?

Если нет, вы можете попробовать

Удалить все дочерние записи - удалить все родительские записи - вставить все родительские записи - вставить все дочерние записи

где любые UPDATE были разделены на их составные DELETE и INSERT.

Это должно работать правильно во всех случаях, но на приемлемых скоростях, вероятно, нет...

Также можно доказать, что это единственная схема, которая может работать правильно во всех случаях, поскольку:

(a) ключевые ограничения для родителя диктуют, что родительский DELETES должен предшествовать родительским INSERTS,
(b) ключевые ограничения для ребенка диктуют, что дочерние DELETES должны предшествовать дочерним INSERTS,
(c) FK диктует, что дочерние DELETES должны предшествовать родительским DELETES
(d) FK также диктует, что ребенок INSERTS должен следовать родительским ВСТАВКАМ

Данная последовательность является единственно возможной, которая удовлетворяет этим 4 требованиям, а также показывает, что ОБНОВЛЕНИЯ для ребенка делают невозможным решение, несмотря на то, что UPDATE означает "одновременный" DELETE plus INSERT.

Ответ 2

Вы должны учитывать их контекст. MS сказал

При обновлении связанных таблиц в наборе данных важно обновить в правильной последовательности, чтобы уменьшить вероятность нарушения ограничений ссылочной целостности.

в контексте написания прикладного программного обеспечения клиентских данных.

Почему важно уменьшить вероятность нарушения ограничений ссылочной целостности? Поскольку нарушение этих ограничений означает

более круглые поездки между dbms и клиентом, либо для кода клиента для обработки нарушений ограничений, либо для того, чтобы пользователь мог обработать нарушения,
больше времени,
больше нагрузки на сервер,
больше возможностей для человеческой ошибки и
больше шансов на одновременные обновления для изменения базовых данных (возможно, сбивает с толку код приложения, пользователь-пользователь или и то, и другое).

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

Если обновление является операцией DELETE в ссылочной таблице и если внешние ключи в ссылочных таблицах объявлены как ON DELETE CASCADE, то оптимальной является просто удалить ссылочную строку (родительскую строку) и позволить dbms управлять каскад. (Это также оптимальная вещь для ON DELETE SET DEFAULT, а для ON DELETE SET NULL.)
Если обновление является операцией DELETE в ссылочной таблице и если внешние ключи в ссылочных таблицах объявлены как ON DELETE RESTRICT, то оптимальной является удаление всех строк ссылок (дочерних строк), а затем удаление ссылочной строки.

Но при правильном использовании транзакций процедура MS оставляет базу данных в постоянном состоянии независимо. Значение заключается в том, что это единый клиентский процесс для кодирования и поддержки, хотя он не является оптимальным во всех случаях. (Это часто случается при разработке программного обеспечения - выбор одного способа, который не является оптимальным во всех случаях. ActiveRecord бросается в голову.)

Ты сказал

Пример: ParentTable имеет две записи parent1 (Id: 1) и parent2 (Id: 2)

ChildTable имеет запись child1 (Id: 1, ParentId: 1)

Если мы обновим child1, чтобы иметь новый родительский родительский элемент2, и мы удалим parent1.

Нам нечего удалять в дочерней таблице

Мы удаляем parent1: мы сломали ограничение, потому что дочерний элемент все еще привязан к parent1, если мы сначала не обновим его.

Это не проблема ссылочной целостности; это процедурный вопрос. Эта проблема явно требует двух транзакций.

Обновите дочерний элемент, чтобы иметь нового родителя, а затем совершите. Эти данные должны быть исправлены независимо от того, что происходит с первым родителем. В частности, эти данные должны быть исправлены, даже если есть параллельные обновления или другие ограничения, которые делают его временно или постоянно невозможным для удаления первого родителя. (Это не проблема ссылочной целостности, потому что нет ON DELETE SET ДЛЯ СЛЕДУЮЩЕГО ИДЕНТИЛЯТОРА РОДИТЕЛЯ ИЛИ СДЕЛАТЬ ВАШЕГО предложения BEST GUESS в ограничениях внешнего ключа SQL.)
Удалить первого родителя, а затем совершить. Это может потребовать сначала обновления любого числа дочерних строк в любом количестве таблиц. В огромной организации я могу представить, что некоторые удаления, подобные этим, заняли недели, чтобы закончить.

Ответ 3

Мне кажется:

Вставьте parent2. Ребенок все еще указывает на parent1.
Обновите дочерний элемент, чтобы указать parent2. Теперь ничто не указывает parent1.
Удалить parent1.

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

В зависимости от вашей схемы вы также можете расширить это:

Обновите parent1, чтобы указать, что он заблокирован (или заблокирован в БД), тем самым предотвращая обновления.
Вставить parent2
Обновить дочерний элемент, чтобы указать на parent2
Удалить parent1

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

РЕДАКТИРОВАТЬ:

Другой вариант - переместить родительские/дочерние ссылки в другую таблицу, например "ссылки";

CREATE TABLE links (
    link_id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    parent_id INT NOT NULL,
    child_id INT NOT NULL
);

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

UPDATE links
    SET parent_id = @new_parent_id
    WHERE parent_id = @old_parent_id
    AND child_id = @child_id;

Ответ 4

Необходимость УДАЛИТЬ родительскую запись без удаления дочерних записей достаточно необычна, и я уверен, что нормальный порядок операций набора данных, определенный MS, в этом случае не применяется.

Наиболее эффективным методом будет ОБНОВЛЕНИЕ дочерних записей, чтобы отобразить нового родителя, а затем УДАЛИТЬ исходного родителя. Как отмечали другие, эта операция должна выполняться в рамках транзакции.

Ответ 5

Я думаю, что разделение действий на таблицах не является хорошим дизайном, поэтому мое решение

вставить/обновить/удалить родительскую таблицу
вставить/обновить/удалить дочерний стол

ключевым моментом является то, что вы не должны изменять parentId дочерней записи, вы должны удалить дочерний элемент parent1 и добавить нового ребенка в parent2. делая это, вы больше не будете беспокоиться о сломанном ограничении. и, конечно же, вы должны использовать транзакцию.

Ответ 6

Требование MSDN правильное в основе использования зависимостей (внешние ключи). Подумайте о порядке как

Таблица детей (удаление каскада)
Родительская таблица: вставка и/или обновление и/или удаление записи означают заключительный этап удаления каскада.
Таблица детей: вставить или обновить.

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

С другой стороны, вы можете обратиться к вам по-разному. Я думаю, что сценарий реальной жизни (почти) будет более полезен. Предположим, что родительская таблица является главной частью заказов (orderID, clientID и т.д.), А дочерняя таблица - это подробная часть (detailID, orderID, productOrServiceID и т.д.). Таким образом, вы получаете заказ, и у вас есть следующее

Родительский стол

orderID = 1 (auto increment)
...

Детский стол

detailID = 1 (auto increment)
orderID = 1
productOrServiceID = 342

and

detailID = 2
orderID = 1
productOrServiceID = 169

and

detailID = 3
orderID = 1
productOrServiceID = 307

Таким образом, у нас есть один заказ на три продукта/услуги. Теперь ваш клиент хочет, чтобы вы переместили второй продукт или услугу в новый заказ и дополнили его позже. У вас есть два варианта сделать это.

Первый (прямой)

Создайте новый заказ (новая родительская запись), которая получает orderID = 2
Обновить дочернюю таблицу, установив orderID = 2 где orderID = 1 и productOrServiceID = 169

В результате у вас будет

Родительский стол

orderID = 1 (auto increment)
...

and

orderID = 2
...

Детский стол

detailID = 1 (auto increment)
orderID = 1
productOrServiceID = 342

and

detailID = 2
orderID = 2
productOrServiceID = 169

and

detailID = 3
orderID = 1
productOrServiceID = 307

Второй (косвенный)

Храните DataRow второго продукта/службы из дочерней таблицы в качестве переменной
Удалить относительную строку из дочерней таблицы
Создайте новый заказ (новая родительская запись), которая получает orderID = 2
Вставьте сохраненный DataRow в дочернюю таблицу, изменив идентификатор порядка поля от 1 до 2

В результате у вас будет

Родительский стол

orderID = 1 (auto increment)
...

and

orderID = 2
...

Детский стол

detailID = 1 (auto increment)
orderID = 1
productOrServiceID = 342

and

detailID = 3
orderID = 1
productOrServiceID = 307

and

detailID = 4
orderID = 2
productOrServiceID = 169

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

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