Удалите все записи, которые не имеют никаких ограничений внешнего ключа
У меня есть таблица SQL 2005 с миллионами строк в нем, которая поражается пользователями весь день и ночь. На эту таблицу ссылаются приблизительно 20 других таблиц, которые имеют ограничения внешнего ключа. Что я должен сделать, регулярно, удаляют все записи из этой таблицы, где поле "Active" имеет значение false И нет никаких других записей ни в одной из дочерних таблиц, которые ссылаются на родительскую запись. Каков самый эффективный способ сделать это за исключением попытки удалить каждого за один раз и разрешение этому вызвать ошибки SQL на тех, которые нарушают ограничения? Также это не опция отключить ограничения, и я не могу вызвать, соединяет родительскую таблицу для любого существенного количества времени.
3 ответа
Если маловероятно, что неактивные строки, которые не связаны, станут связанными, вы можете выполнить (или даже динамически построить, основываясь на метаданных внешнего ключа):
SELECT k.*
FROM k WITH(NOLOCK)
WHERE k.Active = 0
AND NOT EXISTS (SELECT * FROM f_1 WITH(NOLOCK) WHERE f_1.fk = k.pk)
AND NOT EXISTS (SELECT * FROM f_2 WITH(NOLOCK) WHERE f_2.fk = k.pk)
...
AND NOT EXISTS (SELECT * FROM f_n WITH(NOLOCK) WHERE f_n.fk = k.pk)
И вы можете довольно легко превратить это в DELETE. Но большое удаление может содержать много блокировок, поэтому вы, возможно, захотите поместить это в таблицу и затем удалять партиями - партия не должна быть неудачной, если запись не связана.
Чтобы это было эффективно, вам действительно нужно иметь индексы на столбцах FK в связанных таблицах.
Вы также можете сделать это с помощью левых объединений, но тогда вам (иногда) придется де-пупировать с помощью DISTINCT или GROUP BY, а план выполнения обычно не лучше, и это не так благоприятно для генерации кода:
SELECT k.*
FROM k WITH(NOLOCK)
LEFT JOIN f_1 WITH(NOLOCK) ON f_1.fk = k.pk
LEFT JOIN f_2 WITH(NOLOCK) ON f_2.fk = k.pk
...
LEFT JOIN f_n WITH(NOLOCK) ON f_n.fk = k.pk
WHERE k.Active = 0
AND f_1.fk IS NULL
AND f_2.fk IS NULL
...
AND f_n.fk IS NULL
Пусть у нас есть родительская таблица с именем Parent и в ней есть поле "id" любого типа и поле "Active" типа bit. Также у нас есть вторая таблица Child со своим полем "id" и полем "fk", которое является ссылкой на поле "id" таблицы Parent. Затем вы можете использовать следующий оператор:
DELETE Parent
FROM Parent AS p LEFT OUTER JOIN Child AS c ON p.id=c.fk
WHERE c.id IS NULL AND p.Active=0
Немного запутался в вашем вопросе. Но вы можете сделать LeftOuterJoin из вашей основной таблицы в таблицу, которая предположительно должна иметь внешний ключ. Затем вы можете использовать оператор Where для проверки нулевых значений в соединяющей таблице.
Посмотрите здесь о внешних соединениях: http://en.wikipedia.org/wiki/Join_%28SQL%29#Left_outer_join
Вы также должны написать триггеры, которые будут делать все это за вас, когда запись удаляется или устанавливается в false и т.д.