Итак, проблема в том, что на вершине иерархии должен быть пользователь, у которого нет менеджера (редактор в вашем примере). Вот почему классическим решением для такой структуры является разрешение нулевых значений. Вы подтверждаете это в своем заключительном абзаце:
«Теоретически имеет смысл, что у всех пользователей (кроме первого) есть создатель и редактор. Есть ли способ добиться этого, не удаляя временно ненулевое ограничение?»
Суть в том, что если у первого пользователя нет СОЗДАТЕЛЯ или РЕДАКТОРА, тогда нет «временного»: вам нужно отказаться от обязательного ограничения. Если вы сделаете это, проблема с ограничением рекурсивного внешнего ключа исчезнет.
Альтернативой является введение того, что Аристотель называл Перводвигателем, Пользователем, Творцом которого является он сам. Учитывая эту таблицу:
create table t72
( userid number not null
, creator number not null
, editor number not null
, constraint t72_pk primary key (userid)
, constraint t72_cr_fk foreign key (creator)
references t72 (userid)
, constraint t72_ed_fk foreign key (editor)
references t72 (userid)
)
/
создать такого пользователя довольно просто:
SQL> insert into t72 values (1,1,1)
2 /
1 row created.
SQL> commit;
Commit complete.
SQL>
Так почему же это не каноническое решение? Ну, это приводит к немного странной модели данных, которая может создать хаос с иерархическими запросами, как только мы добавим еще несколько пользователей.
SQL> select lpad(' ', level-1)|| u.userid as userid
2 , u.name
3 , u.editor
4 from t72 u
5 connect by
6 prior userid = editor
7 start with userid=1
8 /
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected
SQL>
В основном базе данных не нравится, что USERID является ее собственным редактором. Однако есть обходной путь — ключевое слово NOCYCLE (введено в версии 10g). Это говорит базе данных игнорировать циклические ссылки в иерархии:
SQL> select lpad(' ', level-1)|| u.userid as userid
2 , u.name
3 , u.editor
4 from t72 u
5 connect by nocycle
6 prior userid = editor
7 start with userid=1
8 /
USERID NAME EDITOR
---------- ---------- ----------
1 ONE 1
2 TWO 1
3 THREE 2
4 FOUR 2
5 FIVE 2
6 SIX 2
7 SEVEN 6
7 rows selected.
SQL>
Здесь это не имеет значения, потому что данные по-прежнему правильно иерархичны. Но что произойдет, если мы сделаем это:
SQL> update t72 set editor = 7
2 where userid = 1
3 /
1 row updated.
SQL>
Мы теряем связь (1 -> 7). Мы можем использовать псевдостолбец CONNECT_BY_ISNOCYCLE, чтобы увидеть, какая строка циклически повторяется.
SQL> select lpad(' ', level-1)|| u.userid as userid
2 , u.name
3 , u.editor
4 , connect_by_iscycle
5 from t72 u
6 connect by nocycle
7 prior userid = editor
8 start with userid=1
9 /
USERID NAME EDITOR CONNECT_BY_ISCYCLE
---------- ---------- ---------- ------------------
1 ONE 7 0
2 TWO 1 0
3 THREE 2 0
4 FOUR 2 0
5 FIVE 2 0
6 SIX 2 0
7 SEVEN 6 1
7 rows selected.
SQL>
Oracle имеет множество дополнительных функций, облегчающих работу с иерархическими данными в чистом SQL. Это все есть в документации. Узнайте больше.
person
APC
schedule
05.04.2011
