SQL Server: как игнорировать ссылочную целостность до COMMIT?

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

Поскольку вся операция происходит в транзакции1, я хочу, чтобы SQL Server игнорировал сбои ссылочной целостности, пока я не вызову COMMIT TRANSACTION.

Например2:

   Table: Turboencabulators         Table: Marselvanes
   =========================        =======================
PK TurboencabulatorID int    /-> PK MarselvaneID       int
^  MarselvanesID      int --/       HasGrammeter       bit
|                                   PantametricFan     varchar(50)
+-------------------------------    TurboencabulatorID int

Если я попытаюсь вставить turboencabulator в новую таблицу, произойдет сбой без уже существующего marselvane. Та же проблема с обратным порядком.

При попытке удалить старые строки я не могу удалить одну, пока не будет удалена другая.

я пробовал использовать n-этапную систему, где все строки вставляются с любыми столбцами, находящимися под ограничением внешнего ключа, установленным в null. Затем я обновляю все вставленные строки, помещая правильные отсутствующие значения. Затем, чтобы удалить исходные строки, я обнуляю все столбцы, затронутые FK, а затем удаляю фактические строки3.

Что бы я действительно предпочел, так это просто выполнять свои операции T-SQL, и чтобы SQL Server не сообщал мне, пока я не попытаюсь вызвать фиксацию.

Примечания

1распределенный
2надуманный гипотетический
3что я больше не делаю


sql-server foreign-keys data-integrity sql-server-2000
person Ian Boyd    schedule 26.02.2010    source источник
comment
Почему у вас есть циклическая ссылка между этими двумя таблицами? Кажется, это настоящая проблема.   -  person Samuel Neff    schedule 27.02.2010
comment
@ Сэм: согласен. Я предполагаю, что @Ian упростил проблему для представления здесь, и фактическая циклическая ссылка требует нескольких шагов для реализации. Что скажешь, Ян?   -  person Bob Kaufman    schedule 27.02.2010
comment
@Sam, @Bob: см. сноску 2 - в случае, если турбоэнкабулятор с опорной плитой из предварительно изготовленного амулита, увенчанный податливым логарифмическим кожухом таким образом, что два цилиндрических подшипника находились на прямой линии с пентаметрическим вентилятором. достаточно подсказки.   -  person Ian Boyd    schedule 27.02.2010

Ответы (2)


arrow_upward
11
arrow_downward

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

ALTER TABLE whatever_table NOCHECK CONSTRAINT ALL

чтобы удалить проверку ограничений, прежде чем вы начнете

и когда закончите, включите его снова с помощью ...

ALTER TABLE whatever_table CHECK CONSTRAINT ALL

это то, что я бы сделал в любом случае.

-Дон

person Don Dickinson    schedule 26.02.2010
comment
Да, это то, о чем я думал. я просто не хочу :( - person Ian Boyd; 27.02.2010

arrow_upward
2
arrow_downward

Представьте, как бы вы это реализовали.

Если результат внешнего ключа будет отложен до фиксации транзакции, фиксация должна будет выполнить все операции поиска/проверки/каскада, которые не выполнялись во время вставки/удаления/обновления. Подумайте, что на самом деле означает ограничение FK: ваш план выполнения вставки «аннотируется» дополнительной операцией для проверки и обеспечения соблюдения ограничения FK. Если вы отложите ограничение, дополнительная логика в плане запроса должна быть отвязана от момента выполнения и помещена в некоторый контекст транзакции, чтобы она выполнялась во время фиксации. Внезапная фиксация превращается из короткой операции «отметить транзакцию как зафиксированную в журнале» в операцию, которая делает все, что было пропущено во время фактической транзакции. Хуже всего то, что ограничение может не сработать, и подумайте, как приложение отреагирует на сбой? С ограничением, применяемым в момент выполнения вставки, приложение может поймать ошибку и предпринять корректирующие действия: оно точно знает, что не удалось. Но если вы откладываете это до коммита, вы пытаетесь зафиксировать и поймать исключение, то теперь вам нужно как-то по пойманному исключению понять, что не удалось. Подумайте, насколько сложной была бы в таком случае жизнь разработчиков приложений.

Вторая причина, по которой это не сработает, заключается в том, что вы все еще не решили проблему. У вас есть таблица A с ограничением FK в B. Вы начинаете транзакцию, вставляете в B, затем вставляете в A, затем удаляете из A, затем удаляете из B, затем фиксируете. Все операции удовлетворяли FK в момент их выполнения, база данных удовлетворяла FK во время фиксации. Тем не менее, если вы отложите проверки ограничений, они провалятся во время фиксации!!

Так что я бы сказал, что ссылочная целостность работает нормально, как есть, но она разработана для каскадной иерархии, свободной от циклов. Как и многие структуры данных и алгоритмы CS, он ломается, когда вводятся циклы. Лучшим решением было бы проанализировать схему и посмотреть, действительно ли циклы неизбежны. Если не считать этого, лучшим решением будет вставка NULL и обновление пост-вставки.

К сожалению, отключение ограничения и повторное включение — это большой запрет: при повторном включении придется проверять каждую строку в таблице, чтобы проверить ограничение, и это будет длиться вечно. В противном случае ограничение помечается как «ненадежное» в метаданных базы данных, и оптимизатор в основном игнорирует его (все равно будет применяться, но вы не получите от этого преимуществ оптимизации плана).

person Remus Rusanu    schedule 26.02.2010
comment
я вижу, что я не был ясен в моем примере; Я вижу, как кто-то может неправильно понять. Виноват. - person Ian Boyd; 27.02.2010
comment
Я не уверен, что вижу проблему. Предположим, что транзакция имеет простой хэш-набор таблица-ключ-значение. Кажется простым поддерживать это для каждой вставки и удаления вместо немедленного применения (поддержание хэш-набора кажется не более сложным, чем текущее применение). Любой сбой просто откатывает всю транзакцию, поэтому нет проблем с тем, что происходит в случае сбоя. Нет проблем с вставкой, а затем удалением, потому что удаление удалило бы ключи из набора хэшей. - person BlueMonkMN; 09.07.2018