Как лучше всего обеспечить одноуровневую рекурсию в таблице SQL?
Допустим, у вас есть таблица филиалов в вашей организации. Некоторые из них являются «основными» филиалами, а другие - дополнительными офисами, которые объединяются в основной филиал. Помимо этого различия, которое влияет только на несколько вещей в системе, все ветви являются одноранговыми и имеют одинаковые атрибуты (адрес и т. д.). Один из способов смоделировать это в виде таблицы:
CREATE TABLE Branch (
branch_id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
branch_name VARCHAR(80) NOT NULL,
street VARCHAR(80) NULL,
city VARCHAR(30) NULL,
state CHAR(2) NULL,
zip CHAR(5) NULL,
is_satellite_office BIT NOT NULL DEFAULT(0),
satellite_to_branch_id INT NULL REFERENCES Branch(branch_id)
)
Где is_satellite_office = 1, если эта запись является сателлитом другой ветви, а satellite_to_branch_id относится к той ветви, сателлитом которой вы являетесь, если таковая имеется.
Достаточно легко наложить ограничение на таблицу, чтобы эти два столбца согласовывались с любой записью:
CONSTRAINT [CK_Branch] CHECK
(
(is_satellite_office = 0 AND satellite_to_branch_id IS NULL)
OR (is_satellite_office = 1 AND satellite_to_branch_id IS NOT NULL)
)
Однако на самом деле мне нужен способ гарантировать, что эта рекурсия идет только на уровень один ... то есть, если я указываю на ветку как на своего родителя, она не должна иметь самого родителя, а ее значение для is_satellite_office должно быть 0. Другими словами, мне не нужна полностью рекурсивная древовидная структура, я просто хочу ограничить ее одним отношением родитель / потомок. Вот как я собираюсь написать код, и если есть способ обеспечить его соблюдение в базе данных, который не будет работать как полная чушь, я бы хотел.
Любые идеи? Я работаю над MSSQL 2005, но предпочтительны общие (не зависящие от производителя) решения. И никаких триггеров применять не нужно, если действительно нет другого способа сделать это.
Обновлено: Для ясности, satellite_to_branch_id - это рекурсивный указатель на другую запись в той же таблице Branch. Я знаю, что мог бы удалить is_satellite_office BIT и полагаться на IsNull(satellite_to_branch_id), чтобы дать мне ту же информацию, но я считаю, что немного яснее быть явным, и, кроме того, это не суть вопроса. Я действительно ищу чистый способ ограничения SQL, чтобы предотвратить глубину рекурсии больше 1.
Ответы 4
Мне кажется, что это бизнес-ограничение, которое сложно обеспечить на уровне определения данных. Я не верю, что реляционная алгебра поддерживает определение предела глубины ссылок на себя.
Мне будет любопытно посмотреть, придумает ли кто-нибудь чисто ограничение определения данных, и я удалю свой ответ, если это решение выдерживает критику.
А как насчет этой немного другой структуры?
CREATE TABLE Branch (
branch_id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
branch_name VARCHAR(80) NOT NULL,
street VARCHAR(80) NULL,
city VARCHAR(30) NULL,
state CHAR(2) NULL,
zip CHAR(5) NULL,
parent_id int NULL
)
PARENT_ID просто указывает на BRANCH_ID другой записи в той же таблице. Если он равен нулю, то вы знаете, что у него нет родителей.
Затем, чтобы получить один уровень рекурсии, вы можете просто один раз присоединить таблицу к самой себе, например:
SELECT
PARENT.BRANCH_NAME AS PARENT_BRANCH
,CHILD.BRANCH_NAME AS CHILD_BRANCH
FROM
BRANCH PARENT
,BRANCH CHILD
WHERE CHILD.PARENT_ID PARENT.BRANCH_ID
Если вы хотите обеспечить один уровень глубины в своем дереве, создайте триггер при вставке / обновлении, который вызовет исключение, если этот запрос что-либо вернет.
SELECT *
FROM
BRANCH B1
,BRANCH B2
,BRANCH B3
WHERE B1.PARENT_ID = :NEW.NEW_PARENT_ID
AND B2.PARENT_ID = B1.BRANCH_ID
AND B2.PARENT_ID = B3.BRANCH_ID;
Извините, если мое описание не было ясным; это именно то, что он уже делает в табличной форме (я просто назвал его иначе, чем "parent_id"). Вопрос состоял только в том, как обеспечить выполнение одинарной рекурсии в SQL, в идеале без триггера. Но спасибо за триггерное решение!
Разве вам не разрешено ссылаться на хранимую процедуру в вашем ограничении? Вы можете в PostgreSQL, поэтому я был бы удивлен, если бы 2005 г. не позволял этого.
Это не должно сильно повредить производительности. В конце концов, ваш SP выйдет из строя максимум после одной итерации, и как часто вы будете вставлять / обновлять ветки? (Если чаще, чем один раз в день, дайте мне знать, чтобы я мог покупать акции.)
Согласен ... Я не видел, чтобы это было сделано в MS SQL Server, и я не знаю, разрешено ли это. Кто-нибудь еще знает?
Вы можете привязать контрольное ограничение к возвращаемому значению UDF. Создайте UDF, который принимает столбцы, участвующие в качестве входных параметров, а затем проверьте желаемое состояние, используя выбор в UDF.
Одна ошибка, которая меня замедлила, UDF увидит, что строка была вставлена.
Просто попробовал, и это сработало. Мое ограничение проверки передает значение столбца родительского указателя в UDF, который проверяет, есть ли у записи, на которую указывает этот идентификатор, родительский элемент, и возвращает соответствующий результат. Спасибо!
+1. Вроде того, чего я боялся, но я надеюсь, что у кого-то есть крутой трюк, чтобы это сделать.