У меня есть следующая таблица:
CREATE TABLE X (
A SOMETYPE NOT NULL,
B SOMETYPE NOT NULL,
C SOMETYPE NULL,
PRIMARY KEY (A,B),
FOREIGN KEY (A,C) REFERENCES X (A,B)
);
Сущности, хранящиеся в X
, иерархически организованы: Если строка (A1, B1, C1)
существует и C1 НЕ ЯВЛЯЕТСЯ NULL
, тогда он считается «дочерним» для (A1, C1, C2)
независимо от того, является ли C2
. Поскольку элемент не может происходить от самого себя, я хотел бы сделать незаконным существование циклических иерархических последовательностей:
-- legal
INSERT INTO X (A1,B1,NULL);
INSERT INTO X (A1,B2,B1);
INSERT INTO X (A1,B3,B2);
INSERT INTO X (A1,B4,B2);
-- currently legal, but I want to make it illegal
UPDATE X SET C = B1 WHERE B = B1; /* B1-B1 */
UPDATE X SET C = B2 WHERE B = B1; /* B1-B2-B1 */
UPDATE X SET C = B3 WHERE B = B1; /* B1-B2-B3-B1 */
UPDATE X SET C = B4 WHERE B = B1; /* B1-B2-B4-B1 */
UPDATE X SET C = B2 WHERE B = B2; /* B2-B2 */
UPDATE X SET C = B3 WHERE B = B2; /* B2-B3-B2 */
UPDATE X SET C = B4 WHERE B = B2; /* B2-B4-B2 */
UPDATE X SET C = B3 WHERE B = B3; /* B3-B3 */
UPDATE X SET C = B4 WHERE B = B4; /* B4-B4 */
Как мне это сделать?
В качестве альтернативы я мог бы добавить поле, представляющее «уровень» в иерархии, к table:
CREATE TABLE X (
A SOMETYPE NOT NULL,
B SOMETYPE NOT NULL,
C SOMETYPE NULL,
LEVEL INT NOT NULL,
PRIMARY KEY (A,B),
FOREIGN KEY (A,C) REFERENCES X (A,B)
);
Затем я хотел бы потребовать, чтобы LEVEL
был 0
, когда C IS NULL
, и родительский УРОВЕНЬ + 1
в противном случае .
Я использую SQL Server 2008 R2.