Как решить проблемы со 'Связью с самим собой'?

Question

[WSGlebKavash]

Скриншоты:

В базе данных, работающей в СУБД MS SQL, существует таблица со "Связью с самим собой". Всё казалось бы работает, но при попытке изменить значение поля "PreviousTaskId", связанное с первичным ключом "Id" (смотрите скриншот 1), вылезает ошибка INSERT (смотрите скриншот 2). Зачем Microsoft SQL Server пытается добавить новый объект в таблицу, выполняя INSERT? Как использовать "Связь с самим собой"?

Скрипт:

USE [ProjectOffice_WSR]
GO

/****** Object:  Table [dbo].[Task]    Script Date: 01.07.2023 23:43:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Task](
	[Id] [int] NOT NULL,
	[ProjectId] [int] NOT NULL,
	[FullTitle] [nvarchar](200) NOT NULL,
	[ShortTitle] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
	[ExecutiveEmployeeId] [int] NOT NULL,
	[StatusId] [int] NOT NULL,
	[CreatedTime] [datetime] NOT NULL,
	[UpdatedTime] [datetime] NULL,
	[DeletedTime] [datetime] NULL,
	[Deadline] [datetime] NOT NULL,
	[StartActualTime] [datetime] NULL,
	[FinishActualTime] [datetime] NULL,
	[PreviousTaskId] [int] NULL,
 CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Task] ADD  CONSTRAINT [DF_Task_CreatedTime]  DEFAULT (getdate()) FOR [CreatedTime]
GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Employee] FOREIGN KEY([ExecutiveEmployeeId])
REFERENCES [dbo].[Employee] ([Id])
GO

ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_Task_Employee]
GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Project] FOREIGN KEY([ProjectId])
REFERENCES [dbo].[Project] ([Id])
GO

ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_Task_Project]
GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Task] FOREIGN KEY([PreviousTaskId])
REFERENCES [dbo].[Task] ([Id])
GO

ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_Task_Task]
GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_TaskStatus] FOREIGN KEY([StatusId])
REFERENCES [dbo].[TaskStatus] ([Id])
GO

ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_Task_TaskStatus]
GO

Comments

[Константин Цветков]

"Создать скрипт для таблицы", "Используя CREATE". И скрипт полностью сюда, так как нужна не только таблица, но и свойства связи.

[WSGlebKavash]

Константин Цветков,

Вот:

USE [ProjectOffice_WSR]
GO

/****** Object:  Table [dbo].[Task]    Script Date: 01.07.2023 23:43:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Task](
	[Id] [int] NOT NULL,
	[ProjectId] [int] NOT NULL,
	[FullTitle] [nvarchar](200) NOT NULL,
	[ShortTitle] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](max) NOT NULL,
	[ExecutiveEmployeeId] [int] NOT NULL,
	[StatusId] [int] NOT NULL,
	[CreatedTime] [datetime] NOT NULL,
	[UpdatedTime] [datetime] NULL,
	[DeletedTime] [datetime] NULL,
	[Deadline] [datetime] NOT NULL,
	[StartActualTime] [datetime] NULL,
	[FinishActualTime] [datetime] NULL,
	[PreviousTaskId] [int] NULL,
 CONSTRAINT [PK_Task] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Task] ADD  CONSTRAINT [DF_Task_CreatedTime]  DEFAULT (getdate()) FOR [CreatedTime]
GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Employee] FOREIGN KEY([ExecutiveEmployeeId])
REFERENCES [dbo].[Employee] ([Id])
GO

ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_Task_Employee]
GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Project] FOREIGN KEY([ProjectId])
REFERENCES [dbo].[Project] ([Id])
GO

ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_Task_Project]
GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_Task] FOREIGN KEY([PreviousTaskId])
REFERENCES [dbo].[Task] ([Id])
GO

ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_Task_Task]
GO

ALTER TABLE [dbo].[Task]  WITH CHECK ADD  CONSTRAINT [FK_Task_TaskStatus] FOREIGN KEY([StatusId])
REFERENCES [dbo].[TaskStatus] ([Id])
GO

ALTER TABLE [dbo].[Task] CHECK CONSTRAINT [FK_Task_TaskStatus]
GO

[Константин Цветков]

WSGlebKavash, Как генерится [Task].[Id]?
Триггеры есть?

[Модератор]

В Windows для окон "Диалог" Ctrl+C помещает весь текст с окна в буфер обмена.
Поэтому можно были и структуру, и ошибку поместить в вопрос в виде текста, как того требуют правила.

[WSGlebKavash]

Константин Цветков,

Как генерится [Task].[Id]?

Программно. Приложение само генерирует Id при добавлении новой сущности в базу данных.

[Константин Цветков]

WSGlebKavash,

Программно.

И что там такого, что нельзя доверить серверу?

Answer 1

[Константин Цветков]

Предварительно:

При удалении записи нужен триггер, который должен поддерживать неразрывность цепочки — переносить код предыдущей записи на следующую запись в цепочке.

Ответ на:

Зачем Microsoft SQL Server

Потому что ваша ошибка от SSMS, которой ничего не известно о вашем приложении и она выкручивается как может. Либо переносите нужную логику в базу данных, либо не используйте SSMS.

Или запустите профайлер. Установите фильтр приложения %studio% и анализируйте запросы.

Добавьте в таблицу суррогатный ключ и используйте его в связи.

один из триггеров был неверный

Comments

[WSGlebKavash]

Константин Цветков,

- при удалении записи нужен триггер, который должен поддерживать неразрывность цепочки — переносить код предыдущей записи на следующую запись в цепочке;

Это обязательное условие в рамках ТЗ, будет сделано 100%.
А как можно решить проблему с изменением?

[Константин Цветков]

WSGlebKavash, Ошибся: у замыкании на себя нельзя использовать каскадные действия.

[WSGlebKavash]

Константин Цветков,

Потому что ваша ошибка от SSMS, которой ничего не известно о вашем приложении и она выкручивается как может.

Изначально данная проблема появилась именно в приложении. SSMS использовалась для отладки, и ошибка в обоих случаях совершенно одинаковая.
Я поставил "ALLOW NULL" везде, где только можно. Он зачем-то создал дополнительную строку, где все ячейки были NULL. Зачем?

[Константин Цветков]

WSGlebKavash,

Зачем?

Не знаю. Вы не ответили о триггерах, о причинах генерации ID в приложении.

[WSGlebKavash]

Константин Цветков,

Вы не ответили о триггерах

Эксперт перебрал базу данных. Оказалось, один из триггеров был неверный. Исправили, заработало.
Большое спасибо за ответ, помощь и поддержку!!!