Как предотвратить отображение таблицы саморегуляции

Это довольно распространенная проблема, но я еще не нашел точный вопрос и ответ, который я ищу.

У меня есть одна таблица с FK, указывающая на собственный PK, чтобы включить произвольно глубокую иерархию, такую как классический tblEmployee с столбцом Manager, который является FK с PK tblEmployee.EmployeeID.

Скажем, в моем приложении пользователь

Создает новых сотрудников Alice и Dave без менеджера, потому что они генеральный директор и президент. Таким образом, tblEmployee.Manager имеет значение NULL для этих двух записей.
Создайте нового сотрудника Боба, с Алисой в качестве менеджера. Затем создайте Чарльза с Бобом в качестве его менеджера. Их поля "Менеджер" содержат значение "Первичный ключ" другой записи в tblEmployee.
Редактировать запись сотрудника для Алисы, что означает, что у Дэйва есть ее менеджер (это будет хорошо), но случайно назначить менеджера Алисы Чарльзом, который находится на двух уровнях от Алисы в дереве.

Теперь таблица находится в круглой ссылке вместо правильного дерева.

Каков наилучший способ убедиться, что шаг 3 не может быть выполнен в приложении? Мне просто нужно убедиться, что он откажется выполнять последнее обновление SQL и вместо этого отобразит сообщение об ошибке.

Я не придирчив к тому, что это ограничение базы данных в SQL Server (должно работать в 2008 или 2012 году) или с какой-то подпрограммой проверки на уровне бизнес-логики моего приложения С#.

Ответ 1

Вы можете сделать это с помощью CHECK CONSTRAINT, который проверяет идентификатор менеджера - это не цикл. У вас не может быть сложных запросов в проверочном ограничении, но если вы сначала включите его в функцию, вы можете:

create function CheckManagerCycle( @managerID int )
returns int
as
begin

    declare @cycleExists bit
    set @cycleExists = 0

    ;with cte as (
        select E.* from tblEmployee E where ID = @managerID
        union all
        select E.* from tblEmployee E join cte on cte.ManagerID = E.ID and E.ID <> @managerID
    )
    select @cycleExists = count(*) from cte E where E.ManagerID = @managerID

    return @cycleExists;

end

Тогда вы можете использовать такое ограничение:

alter table tblEmployee
ADD CONSTRAINT chkManagerRecursive CHECK ( dbo.CheckManagerCycle(ManagerID) = 0 )

Это предотвратит добавление или обновление записей для создания цикла из любого источника.

Редактировать: Важное замечание: проверочные ограничения проверяются для столбцов, на которые они ссылаются. Я изначально закодировал это, чтобы проверить циклы на Идентификаторе Сотрудника, а не Идентификаторе Менеджера. Однако это не сработало, поскольку оно вызывалось только при изменении столбца идентификатора. Эта версия работает, потому что она запускается каждый раз, когда изменяется ManagerID.

Ответ 2

Вы можете добавить целочисленный столбец уровня.

У Алисы и Дейва будет уровень == 0 Если вы установите менеджера для сотрудника, его уровень (сотрудник) будет равен + 1 его менеджера.

Во время обновления вы должны проверить, меньше ли уровень менеджера, чем уровень сотрудника...

Это будет быстрее, чем использование процедуры...

Ответ 3

Вы можете включить проверку в свой оператор UPDATE:

DECLARE @Employee INT = 2
       ,@NewManager INT = 4
;WITH cte AS (SELECT *
              FROM tblEmployee
              WHERE Manager = @Employee
              UNION  ALL
              SELECT a.*
              FROM tblEmployee a
              JOIN cte b
                ON a.manager = b.EmployeeID)
UPDATE a
SET a.Manager = @NewManager
FROM tblEmployee a
WHERE EmployeeID = @Employee
    AND NOT EXISTS (SELECT *
                    FROM cte b
                    WHERE a.EmployeeID = b.Manager)

Демо: SQL Fiddle

Ответ 4

Я думаю, что лучший способ сделать это:

Создайте 2 рекурсивные функции (выполняйте лучше, чем грязные циклы) в t-sql, которые будут выполнять работу по возврату как таблицы "менеджеров N + x данного сотрудника", так и "Nx employee of данный менеджер"
Предотвратите шаг 3, используйте функцию GET_MANAGERS_OF и GET_EMPLOYEES_OF, которая будет использоваться в обоих:
- Проверьте свое приложение С#
- Проверить таблицу Employee TRIGGER (лучшая причина безопасности, о которой вы не знаете, будут ли каждый разработчик проверять перед сотрудником по обновлению и если кто-то делает непосредственно обновление sql)

Если менеджер X, который присваивается сотруднику Y, он не сотрудник N-x из Y.

В любом случае функции рекурсивностей thoses будут полезны в ваших SQL-запросах и С# App

FYI, есть способ обработать SQL ERROR TRANSACTION в приложении С# ( "Вы можете сделать это, потому что..." ).