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

Есть таблица

CREATE TABLE [dbo].[Residence](
  [ResidenceID] [int] IDENTITY(1,1) NOT NULL,
  [ResidenceStartDate] [datetime] NULL,
  [ResidenceEndDate] [datetime] NULL,
  [PatientRef] [int] NULL,
  [VenueRef] [int] NULL,
  [UserRef] [nvarchar](200) NULL,
  [PreviousResidenceRef] [int] NULL,
  [FK_Residence_Residence] FOREIGN KEY([PreviousResidenceRef])       
REFERENCES [dbo].[Residence] ([ResidenceID])                               
GO

Нужно удалить записи где ResidenceEndDate < 2017-01-07

Запрос типа

DELETE FROM RESIDENCE
WHERE ResidenceEndDate < '2017-01-07';

генерирует ошибку

Конфликт инструкции DELETE с ограничением SAME TABLE REFERENCE «FK_Residence_Residence». Конфликт произошел в базе данных «Db demo», таблица «dbo.Residence», column ‘PreviousResidenceRef’. Выполнение данной инструкции было прервано.

задан 10 авг 2017 в 5:59

Вам сначала необходимо удалить все зависимые записи

DELETE FROM RESIDENCE
WHERE PreviousResidenceRef IN (select ResidenceID 
                      from Residence 
                      where ResidenceEndDate < '2017-01-07');

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

UPDATE RESIDENCE 
SET PreviousResidenceRef = null
WHERE PreviousResidenceRef IN (select ResidenceID 
                      from Residence 
                      where ResidenceEndDate < '2017-01-07');

Затем уже удалять нужные вам записи.

DELETE FROM RESIDENCE
WHERE ResidenceEndDate < '2017-01-07';

ответ дан 10 авг 2017 в 6:44

JVicJVic

3,2691 золотой знак15 серебряных знаков35 бронзовых знаков

Сначала присвоил все записям где ResidenceEndDate < ‘2017-07-01’ NULL

UPDATE Residence
SET PreviousResidenceRef = NULL
WHERE ResidenceEndDate < '2017-07-01';

Далее использовал процедуру ALL

delete from Residence
where (ResidenceEndDate < '2017-01-07') AND
(ResidenceID != ALL(
    SELECT PreviousResidenceRef FROM Residence
    WHERE PreviousResidenceRef IS NOT NULL));

ответ дан 11 авг 2017 в 11:20

vvo12015vvo12015

251 серебряный знак5 бронзовых знаков

Источник

Remove From My Forums

Question
Executed as user: ****. The DELETE statement
conflicted with the SAME TABLE REFERENCE constraint «FK_PARENT_TASK_REF».
The conflict occurred in database «****», table «****», column
‘PARENT_TASK_ID’. [SQLSTATE 23000] (Error 547) The statement has been
terminated. [SQLSTATE 01000] (Error 3621). The step failed.

Does this error msg indicate the whole script failed to execute or was it just a single step/task that failed ?

What does error msg mean ?

Anyway to prevent this error msg and ensure script runs successfully

Answers

Hi mdavidh,

This error occurs because the record ‘PARENT_TASK_ID’ was referenced by ‘FK_PARENT_TASK_REF’.

Please refer below codes:

CREATE TABLE MyTable (
    ID    INT, primary key(ID),       -- primary key
    ID_Parent INT foreign key(ID_Parent) references MyTable(ID),  -- foreign key reference the same table   
    
)
insert into MyTable(ID,ID_Parent)
values(0,0);
insert into MyTable(ID,ID_Parent)
values(1,0);
insert into MyTable(ID,ID_Parent)
values(2,0);
insert into MyTable(ID,ID_Parent)
values(3,1);
insert into MyTable(ID,ID_Parent)
values(4,3);
insert into MyTable(ID,ID_Parent)
values(5,4);
CREATE TRIGGER MyTrigger
on MyTable
instead of delete
as
    set nocount on
    update MyTable set ID_Parent = null where ID_Parent in (select ID from deleted)
    delete from MyTable where ID in (select ID from deleted)

Now we could delete records.

delete  from MyTable where ID_Parent=0

Thanks,

Candy Zhou

Edited by

Monday, July 29, 2013 12:12 PM
edit
Marked as answer by
Allen Li — MSFT
Saturday, August 3, 2013 10:59 AM

Источник

Remove From My Forums

Question
Executed as user: ****. The DELETE statement
conflicted with the SAME TABLE REFERENCE constraint «FK_PARENT_TASK_REF».
The conflict occurred in database «****», table «****», column
‘PARENT_TASK_ID’. [SQLSTATE 23000] (Error 547) The statement has been
terminated. [SQLSTATE 01000] (Error 3621). The step failed.

Does this error msg indicate the whole script failed to execute or was it just a single step/task that failed ?

What does error msg mean ?

Anyway to prevent this error msg and ensure script runs successfully

Answers

Hi mdavidh,

This error occurs because the record ‘PARENT_TASK_ID’ was referenced by ‘FK_PARENT_TASK_REF’.

Please refer below codes:
```
CREATE TABLE MyTable (
    ID    INT, primary key(ID),       -- primary key
    ID_Parent INT foreign key(ID_Parent) references MyTable(ID),  -- foreign key reference the same table   
    
)
insert into MyTable(ID,ID_Parent)
values(0,0);
insert into MyTable(ID,ID_Parent)
values(1,0);
insert into MyTable(ID,ID_Parent)
values(2,0);
insert into MyTable(ID,ID_Parent)
values(3,1);
insert into MyTable(ID,ID_Parent)
values(4,3);
insert into MyTable(ID,ID_Parent)
values(5,4);
CREATE TRIGGER MyTrigger
on MyTable
instead of delete
as
    set nocount on
    update MyTable set ID_Parent = null where ID_Parent in (select ID from deleted)
    delete from MyTable where ID in (select ID from deleted)
```
Now we could delete records.
```
delete  from MyTable where ID_Parent=0
```
Thanks,

Candy Zhou
- Edited by
  
  Monday, July 29, 2013 12:12 PM
  edit
- Marked as answer by
  Allen Li — MSFT
  Saturday, August 3, 2013 10:59 AM

Источник

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

	От:	Аноним
Дата:	12.12.11 13:54
	Оценка:	3 (1)

Здравствуйте, 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]: Проблемы с триггером каскадного удаления

От:

Sshur

http://shurygin-sergey.livejournal.com

Дата:

12.12.11 13:55

Оценка:

3 (1)

Здравствуйте, 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;

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

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

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

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

	От:	spy__
Дата:	12.12.11 13:23
	Оценка:

Добрый день.

У меня есть 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: Проблемы с триггером каскадного удаления

От:

Sshur

http://shurygin-sergey.livejournal.com

Дата:

12.12.11 13:38

Оценка:

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

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

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

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

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

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

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

	От:	spy__
Дата:	12.12.11 13:46
	Оценка:

Здравствуйте, 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]: Проблемы с триггером каскадного удаления

	От:	spy__
Дата:	12.12.11 14:11
	Оценка:

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

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

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

От:

Sshur

http://shurygin-sergey.livejournal.com

Дата:

12.12.11 14:33

Оценка:

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

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

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

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

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

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

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

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

	От:	spy__
Дата:	12.12.11 14:37
	Оценка:

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

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

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

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

От:

Sshur

http://shurygin-sergey.livejournal.com

Дата:

12.12.11 14:51

Оценка:

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

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

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

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

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

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

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

Wait...

Переместить
Удалить
Выделить ветку

Пока на собственное сообщение не было ответов, его можно удалить.

Источник

I’m trying to delete all users but getting the error:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_M02ArticlePersons_M06Persons". The conflict occurred in database "workdemo.no", table "dbo.M02ArticlePersons", column 'M06PersonId'.
The statement has been terminated.

The query:

DELETE FROM [workdemo.no].[dbo].[M06Persons] 
WHERE ID > '13'
GO

Seems I need to use on delete cascade; but I’m stuck.

asked Apr 4, 2013 at 11:50

You don’t need to use the on delete cascade. Somebody (the schema design author) had made sure you cannot delete a person that is still referenced by an article. It succeeded, you were just trying to do this and was blocked, kudos to the designer.

Now go and talk with that somebody that designed the schema and knows the constraints and ask him how to properly delete the records you’re trying to delete, in the correct order and taking the proper precautions to keep the database consistent.

answered Apr 4, 2013 at 12:02

Remus RusanuRemus Rusanu

51.5k3 gold badges93 silver badges171 bronze badges

You have two real choices here, you can disable constraints on the table. This usually not a great idea as you can end up with a bad data condition if you’re messing with data that relates to other tables, but not know the full extent of your schema and it may suit your purposes:

ALTER TABLE [workdemo.no].[dbo].[M06Persons] NOCHECK CONSTRAINT [FK_M02ArticlePersons_M06Persons]

Remember to turn the constraint back on after the delete with

ALTER TABLE [workdemo.no].[dbo].[M06Persons] WITH CHECK CHECK CONSTRAINT [FK_M02ArticlePersons_M06Persons]

The second choice would be to drop and re-add the constraint with the ON DELETE CASCADE option using:

ALTER TABLE [workdemo.no].[dbo].[M06Persons] DROP CONSTRAINT [FK_M02ArticlePersons_M06Persons]

ALTER TABLE [workdemo.no].[dbo].[M06Persons] WITH NOCHECK ADD CONSTRAINT [FK_M02ArticlePersons_M06Persons] FOREIGN KEY(M06PersonId)
REFERENCES <parent table here> (<parent column here>)
ON DELETE CASCADE

Based on your FK name it looks like your parent table is M02ArticlePersons and the parent column is M06Persons.

If you did not author this schema please try to consider why the constraints may be present, and understand that violating them in this manner may have unintended side effects.

answered Apr 4, 2013 at 12:10

dbo.M02ArticlePersons table of column M06PersonId is reffered in another table.
So before delete statement, disable this relationships and try again

below is for disbling the foreign key

 ALTER TABLE dbo.M02ArticlePersons NOCHECK CONSTRAINT FK_M02ArticlePersons_M06Persons

DELETE FROM [workdemo.no].[dbo].[M06Persons] 
  WHERE ID > '13'
GO

and this is to enable it

ALTER TABLE dbo.M02ArticlePersons CHECK CONSTRAINT FK_M02ArticlePersons_M06Persons

Hope this will work

answered Apr 4, 2013 at 12:04

There is another manual option too:

You can go to the child table and delete the child rows referenced by the parent key. Then you can delete the parent row. This is essentially what the cascade delete does. This way, you do not have to drop/recreate/alter your constraints.

answered Apr 4, 2013 at 14:49

StanleyJohnsStanleyJohns

5,9322 gold badges21 silver badges44 bronze badges

This little code will help for any table that you want to delete records from. It takes care of referential integrity as well …

Below code will generate DELETE statements .. Just specify the schema.table_Name

Declare @sql1 varchar(max)
      , @ptn1 varchar(200)
      , @ctn1 varchar(200)
      , @ptn2 varchar(200)
      , @ctn2 varchar(200)
--
SET @ptn1 = ''
--
SET @ctn1 = ''
--
SET @ptn2 = ''
--
SET @ctn2 = ''
--
SELECT @sql1 = case when (@ptn1 <> OBJECT_NAME (f.referenced_object_id)) then
                         COALESCE( @sql1 + char(10), '') + 'DELETE' + char(10) + ' ' + OBJECT_NAME (f.referenced_object_id) + ' FROM ' + OBJECT_NAME(f.parent_object_id) + ', '+OBJECT_NAME (f.referenced_object_id) + char(10) +' WHERE ' + OBJECT_NAME(f.parent_object_id) + '.' + COL_NAME(fc.parent_object_id, fc.parent_column_id) +'='+OBJECT_NAME (f.referenced_object_id)+'.'+COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
                    else
                         @sql1 + ' AND ' + OBJECT_NAME(f.parent_object_id) + '.' + COL_NAME(fc.parent_object_id, fc.parent_column_id) +'='+OBJECT_NAME (f.referenced_object_id)+'.'+COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
                    end + char(10)
     , @ptn1 = OBJECT_NAME (f.referenced_object_id)
     , @ptn2  = object_name(f.parent_object_id)
FROM   sys.foreign_keys AS f
       INNER JOIN
       sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
WHERE  f.parent_object_id = OBJECT_ID('dbo.M06Persons'); -- CHANGE here schema.table_name
--
print  '--Table Depended on ' + @ptn2 + char(10) + @sql1

answered Apr 4, 2013 at 19:56

Kin ShahKin Shah

61.8k6 gold badges117 silver badges235 bronze badges

Источник

Question

Answers

Question

Answers