Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
topic starter
Member

Откуда:
Сообщений: 245
Добрый день! Срочно нужна помощь.

С некоторых пор стала регулярно валиться эта ошибка, а почему возникает - непонятно.

Есть 2 таблицы, назовем их Parent (родительская) и Сhild (дочерняя).
В Parent по трем полям (f1, f2, f3) создан уникальный индекс.
В Child есть три поля с такими же именами и типами, по которым создан внешний ключ на Parent с именем FK_Child_f1_f2_f3.

Есть ХП, где в транзакции сначала производится удаление из Child, затем из Parent. И транзакция иногда валится с ошибкой "Конфликт инструкции DELETE с ограничением REFERENCE "FK_Child_f1_f2_f3".

Почему это вообще может происходить?

Если профайлером смотреть, то какие события отлавливать?
15 июн 12, 11:44    [12718367]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
А триггер есть?
15 июн 12, 11:45    [12718393]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
topic starter
Member

Откуда:
Сообщений: 245
iap, и на той и на другой есть по 3 триггера: ins, upd, del. Пишут в логи.
15 июн 12, 11:46    [12718408]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
topic starter
Почему это вообще может происходить?
Потому что из Child удаляются не все записи, соответсвующие значению FK.
15 июн 12, 11:49    [12718442]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
topic starter,

а пример скрипта с удалением можно посмотреть?
15 июн 12, 11:49    [12718443]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
f1, f2, f3 допускают NULL?
Есть в них реально значения NULL?
15 июн 12, 11:51    [12718470]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
invm
topic starter
Почему это вообще может происходить?
Потому что из Child удаляются не все записи, соответсвующие значению FK.
Или в триггере на удаление Parent удаляются-таки ещё какие-нибудь записи Parent
15 июн 12, 11:52    [12718494]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
iap
invm
пропущено...
Потому что из Child удаляются не все записи, соответсвующие значению FK.
Или в триггере на удаление Parent удаляются-таки ещё какие-нибудь записи Parent
Или не в триггере.
В любом случае, ждем код процедуры и триггеров.
15 июн 12, 12:12    [12718769]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
topic starter
Member

Откуда:
Сообщений: 245
iap, в триггере на удаление Parent ничего не удаляется, там делается только несколько insert-ов в таблицу лога.

В обеих таблицах поля f1, f2, f3 все NOT NULL.

Вот часть процедуры удаления. В процедуре используется временная таблица #tmp, в которой лежат копии записей из Child, и некоторые из них помечены на удаление.
-- удаляем из Child записи, помеченные на удаление
DELETE FROM С
    FROM Child AS C
    JOIN #tmp AS T ON T.id = C.id
    WHERE T.is_deleted = 1;	

-- теперь удаляем из Parent те записи, на которые в Child больше нет ссылок
DELETE FROM P
    FROM Parent AS P
    WHERE NOT EXISTS 
        (SELECT id FROM Child WHERE p_id = P.id);


invm
Потому что из Child удаляются не все записи, соответсвующие значению FK.
Это странно, потому что ошибка начала вылезать только что. А ХП не менялась уже несколько месяцев. И клиентская часть тоже.
15 июн 12, 12:18    [12718841]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
topic starter,

Как известно, чудес на свете не бывает.
Речь шла про FK по (f1, f2, f3), а удаляете вы по id...
15 июн 12, 12:22    [12718882]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
topic starter,

что-то я не вижу трёх полей f1, f2, f3.
Зато появились какие-то id
15 июн 12, 12:25    [12718908]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
topic starter
Member

Откуда:
Сообщений: 245
Я удаляю по id, потому что это первичный ключ. Client.id = FK на Parent.id.
А FK по полям f1+f2+f3 - это альтернативный ключ, описывает некоторое бизнес-правило.
15 июн 12, 12:28    [12718937]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
topic starter
Member

Откуда:
Сообщений: 245
Извините, опечатка.
Вот так правильно:
Client.p_id = FK на Parent.id.
15 июн 12, 12:30    [12718962]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
Есть ли Client.p_id IS NULL?
15 июн 12, 12:32    [12718979]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
И вообще, правильно ли я понял, что одна и та же таблица имеет два FK на одну и ту же другую таблицу?
15 июн 12, 12:34    [12719002]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
invm
Member

Откуда: Москва
Сообщений: 9845
topic starter,

1. Как гарантируется, что в Child одной комбинации (f1, f2, f3) соответствует ровно один id?
2. Как гарантируется, что на удаление помечены все записи с одинаковым значением (f1, f2, f3)?
15 июн 12, 12:36    [12719017]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
И правда, говорилось, что из Child удаляются все записи,
а оказалось, что только is_deleted = 1
15 июн 12, 12:39    [12719045]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
topic starter
Member

Откуда:
Сообщений: 245
Я тут заметил, то вместо Child написал Client (слова похожи), извините еще раз.

iap
правильно ли я понял, что одна и та же таблица имеет два FK на одну и ту же другую таблицу?
Да.
iap
Есть ли Child.p_id IS NULL?
Нет. Поле p_id NOT NULL.
iap
И правда, говорилось, что из Child удаляются все записи,
а оказалось, что только is_deleted = 1
Этого не говорилось.

invm
1. Как гарантируется, что в Child одной комбинации (f1, f2, f3) соответствует ровно один id?
2. Как гарантируется, что на удаление помечены все записи с одинаковым значением (f1, f2, f3)?
Хорошие вопросы, попробую разобраться.
15 июн 12, 12:47    [12719124]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
К примеру, можно удалять все записи, относящиеся к одному и тому же Parent,
если среди них есть хоть одна с is_deleted = 1
-- удаляем из Child записи, помеченные на удаление
DELETE FROM С
    FROM Child AS C
    JOIN #tmp AS T ON T.p_id = C.p_id
    WHERE T.is_deleted = 1;
15 июн 12, 12:47    [12719126]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
topic starter
iap
И правда, говорилось, что из Child удаляются все записи,
а оказалось, что только is_deleted = 1
Этого не говорилось.
А как же тогда из Parent удалять, если есть ещё в Child
записи, ссылающиеся на удаляемого родителя, но с is_deleted = 0?

FK не должен этого позволять, он и не позволяет!
15 июн 12, 12:49    [12719145]     Ответить | Цитировать Сообщить модератору
 Re: "Конфликт инструкции DELETE с ограничением REFERENCE" - хелп!  [new]
topic starter
Member

Откуда:
Сообщений: 245
Проблема решилась добавлением дополнительного условия where при удалении.
Спасибо iap и invm за оперативность и точно поставленные вопросы.
15 июн 12, 13:51    [12719783]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить