Конфликт инструкции delete с ограничением same table reference

01.11.2023

Думаю каждый, кто начинает работать с планами обслуживания в Microsoft SQL сервере, столкнётся с ошибкой при удалении ненужного вам плана.
И звучит она примерно так: 
Действие Удалить завершилось неудачно для объекта Задание

Конфликт инструкции DELETE с ограничением REFERENCE ... (ошибка: 547)

Эту проблему мы с вами и будем решать.

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

Картинка с сайта: ptkit.ru

Вкратце: Действие Удалить завершилось неудачно для объекта Задание

Конфликт инструкции DELETE с ограничением REFERENCE … (ошибка: 547)

Для удаления данного задания нам понадобится открыть шелл MS SQL сервера, где мы сможем выполнять команды. Для этого закрываем окна с ошибкой и кликаем на верхней панели «Создать запрос»:

Картинка с сайта: ptkit.ru

В правой части у нас появляется форма, где мы вводим следующую команду:
select * from msdb.dbo.sysmaintplan_plans

И нажимаем сверху на кнопку «Выполнить»:

Картинка с сайта: ptkit.ru

После нажатия на кнопку «Выполнить», снизу окна у нас появится информация о текущих планах обслуживания и будет столбец с указанием ID каждого плана, он нам и нужен:

Картинка с сайта: ptkit.ru

В списке мы находим название того, плана, что мы хотим удалить и видим его ID, в нашем случае это задание под номером 2 с ID: A3550947-8DDC-4D25-8E97-05DC17FB53DC. Копируем этот ID и формируем следующую команду в шелле SQL, предыдущую команду удаляем.
Получаем примерно следующее:

delete from msdb.dbo.sysmaintplan_subplans where plan_id = 'A3550947-8DDC-4D25-8E97-05DC17FB53DC'

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

delete from msdb.dbo.sysmaintplan_plans where id = 'A3550947-8DDC-4D25-8E97-05DC17FB53DC'
Опять жмём «Выполнить» и опять должны увидеть надпись «Запрос успешно выполнен». 
Отлично. Теперь мы спокойно можем удалить наше задание без всяких ошибок!

Напоследок хочется отметить один момент

: если задание создано через «Мастер планов обслуживания», то задания нужно удалять именно через каталог объектов «Управление» — «Планы обслуживания», в этом случае ошибки изначально не будет. Если же задание создавалось вручную, то, скорее всего, получите ошибку и решить её можно вышеприведённым методом.

На этом всё, удачи в администрировании!

Re: Проблемы с триггером каскадного удаления

Здравствуйте, spy__, Вы писали:

_>Проблема в том, что если выполнить запрос
__>

__>delete from Table4 where id = 2;
__>

__>то удалится все, кроме строки 2 | null. А ведь изначально цель удалить именно ее + всех, кто из этой таблицы на нее ссылается. В чем может быть косяк?

Тело триггера по идее должно быть таким:

begin
    delete from Table4 WHERE x in (select id from deleted)
    delete from Table4 WHERE id in (select id from deleted)
end;

Re[3]: Проблемы с триггером каскадного удаления

Здравствуйте, spy__, Вы писали:

S>>Ну дык триггер то istead of. Данные, которые в deleted из таблицы не удаляются. Делайте триггер after или удаляйте руками.

__>1. for/after я уже пробовал. При выполнении запроса на удаление:
__>

Сообщение 547, уровень 16, состояние 0, строка 1
__>Конфликт инструкции DELETE с ограничением SAME TABLE REFERENCE «FK_TABLE4_2». Конфликт произошел в базе данных «TEST1», таблица «dbo.Table4», column ‘x’.
__>Выполнение данной инструкции было прервано.

__>Это подкидывает свинью наличие FK.

Ну да. Вообще имхо плохая идея делать такое каскадное удаление через триггер. Надо сначала удалить все записи, на которые есть FK, а потом PK

__>2. «Удаляйте руками». Можно конкретный пример? Т.к. у меня само создание триггера ругается на то, что не удается обновить deleted и inserted (это когда пробую выполнить больше одного запроса в триггере).

Как-то так

create trigger casc_table4_1 on Table4 instead of delete as
begin
delete from Table4 WHERE x in (select id from deleted)

delete from Table4 WHERE id in (select id from deleted)
end;

У меня пример прошел успешно

Шурыгин Сергей

«Не следует преумножать сущности сверх необходимости» (с) Оккам

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

Добрый день.

У меня есть MS SQL 2008 и табличка такого плана:

create table Table4
(
id integer not null identity(1, 1) constraint PK_Table4_1 primary key,
x integer constraint FK_Table4_2 references Table4(id) on delete no action on update no action
);

Данные в ней, скажем, такие:

id | x
2 | null
3 | 2
4 | 2
5 | 2

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

create trigger casc_table4_1 on Table4 instead of delete as
begin
delete from Table4 WHERE x in (select id from deleted)
end;

Проблема в том, что если выполнить запрос

delete from Table4 where id = 2;

то удалится все, кроме строки 2 | null. А ведь изначально цель удалить именно ее + всех, кто из этой таблицы на нее ссылается. В чем может быть косяк?

Re: Проблемы с триггером каскадного удаления

Здравствуйте, spy__, Вы писали:

__>Проблема в том, что если выполнить запрос
__>

__>delete from Table4 where id = 2;
__>

__>то удалится все, кроме строки 2 | null. А ведь изначально цель удалить именно ее + всех, кто из этой таблицы на нее ссылается. В чем может быть косяк?

Ну дык триггер то istead of. Данные, которые в deleted из таблицы не удаляются. Делайте триггер after или удаляйте руками.

Шурыгин Сергей

«Не следует преумножать сущности сверх необходимости» (с) Оккам

Re[2]: Проблемы с триггером каскадного удаления

Здравствуйте, Sshur, Вы писали:

S>Здравствуйте, spy__, Вы писали:

__>>Проблема в том, что если выполнить запрос
__>>

__>>delete from Table4 where id = 2;
__>>

__>>то удалится все, кроме строки 2 | null. А ведь изначально цель удалить именно ее + всех, кто из этой таблицы на нее ссылается. В чем может быть косяк?

S>Ну дык триггер то istead of. Данные, которые в deleted из таблицы не удаляются. Делайте триггер after или удаляйте руками.

1. for/after я уже пробовал. При выполнении запроса на удаление:

Сообщение 547, уровень 16, состояние 0, строка 1
Конфликт инструкции DELETE с ограничением SAME TABLE REFERENCE «FK_TABLE4_2». Конфликт произошел в базе данных «TEST1», таблица «dbo.Table4», column ‘x’.
Выполнение данной инструкции было прервано.

Это подкидывает свинью наличие FK.

2. «Удаляйте руками». Можно конкретный пример? Т.к. у меня само создание триггера ругается на то, что не удается обновить deleted и inserted (это когда пробую выполнить больше одного запроса в триггере).

Re[4]: Проблемы с триггером каскадного удаления

Аноним, Sshur, благодарю, господа. Это рабочий вариант.

Вообще очень мутные вещи творятся с этими FK и триггерами. Т.е. таким же образом (instead of) нужно поступать и в ситуации, когда есть вторая таблица с not null FK на id первой таблицы: удалять сначала из второй, а потом и из первой.

Re[5]: Проблемы с триггером каскадного удаления

Здравствуйте, spy__, Вы писали:

__>Аноним, Sshur, благодарю, господа. Это рабочий вариант.

__>Вообще очень мутные вещи творятся с этими FK и триггерами. Т.е. таким же образом (instead of) нужно поступать и в ситуации, когда есть вторая таблица с not null FK на id первой таблицы: удалять сначала из второй, а потом и из первой.

Для второй таблицы на FK можно поставить on cascade delete

Если вы все чистите, то можно временно «выключить» связь (alter table nocheck constaraint …)

Шурыгин Сергей

«Не следует преумножать сущности сверх необходимости» (с) Оккам

Re[6]: Проблемы с триггером каскадного удаления

S>Для второй таблицы на FK можно поставить on cascade delete

S>Если вы все чистите, то можно временно «выключить» связь (alter table nocheck constaraint …)

Можно, но, к сожалению, не везде. В некоторых случаях MS SQL ругается на появление множественных каскадных путей, а исправлять структуру БД нельзя.

Re[7]: Проблемы с триггером каскадного удаления

Здравствуйте, spy__, Вы писали:

S>>Для второй таблицы на FK можно поставить on cascade delete

S>>Если вы все чистите, то можно временно «выключить» связь (alter table nocheck constaraint …)

__>Можно, но, к сожалению, не везде. В некоторых случаях MS SQL ругается на появление множественных каскадных путей, а исправлять структуру БД нельзя.

Ну да. Эта проверка на каскадное удаление в MS SQL меня тоже иногда раздражает

Шурыгин Сергей

«Не следует преумножать сущности сверх необходимости» (с) Оккам

From Stack Overflow:

I have an SQL Server table defined as below:

TestComposite

id (PK)	siteUrl (PK)	name	parentId
1	site1	Item1	NULL
2	site1	Item2	NULL
3	site1	Folder1	NULL
4	site1	Folder1.Item1	3
5	site1	Folder1.Item2	3
6	site1	Folder1.Folder1	3
7	site1	Folder1.Folder1.Item1	6

Items and folders are stored inside the same table

If an item is inside a folder, the parentID column is the id of the folder.

I would like to be able to DELETE CASCADE items/folders when I delete a folder.

I tried to define a constraint similar to:

ALTER TABLE [TestComposite]
ADD CONSTRAINT fk_parentid
FOREIGN KEY (ParentID, SiteUrl)
REFERENCES [TestComposite] (ID, SiteUrl) ON DELETE CASCADE

, but it gives me this error:

Introducing FOREIGN KEY constraint 'fk_parentid' on table 'TestComposite' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

SQL Server does support chained CASCADE updates, but does not allow one table to participate more that once in a chain (i. e. does not allow loops).

SQL Server, unlike most other engines, optimizes cascading DML operations to be set-based which requires building a cycle-free DML order (which you can observe in the execution plan). With the loops, that would not be possible.

However, it is possible to define such a constraint without cascading operations, and with a little effort it is possible to delete a whole tree branch at once.

Let’s create a sample table:

Table creation details

This table contains 50,000 records forming a hierarchy.

If we try to delete an entry that has some children, we’ll fail:

DELETE
FROM    [20100303_cascade].TestComposite
WHERE   id = 42
AND siteUrl = 'site1'

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "fk_TestComposite_self". The conflict occurred in database "test", table "20100303_cascade.TestComposite".
The statement has been terminated.

To delete an item and all of its children, we should build a hierarchical query to retrieve the whole branch, and delete the branch all at once.

To do it, we just semi-join the table to the results of the recursive CTE:

WITH    q AS
(
SELECT  id, siteUrl
FROM    [20100303_cascade].TestComposite
WHERE   id = 42
AND siteUrl = 'site1'
UNION ALL
SELECT  tc.id, tc.siteUrl
FROM    q
JOIN    [20100303_cascade].TestComposite tc
ON      tc.parentID = q.id
AND tc.siteUrl = q.siteUrl
)
DELETE
FROM    [20100303_cascade].TestComposite
OUTPUT  DELETED.*
WHERE   EXISTS
(
SELECT  id, siteUrl
INTERSECT
SELECT  id, siteUrl
FROM    q
)

View query results

We see that the whole branch of 31 records was deleted all at once, without violating the FOREIGN KEY.

Unlike some other systems, we don’t have to worry about the order the records are deleted, since all SQL Server referential constraints are deferred till the end of the query.