У меня есть таблица с тремя столбцами:
ID, PARENT_ID, NAME
PARENT_ID имеет отношение внешнего ключа с ID в той же таблице. Эта таблица моделирует иерархию.
Иногда меняется ID записи. Я хочу иметь возможность обновить запись ID, а затем обновить зависимые записи PARENT_ID, чтобы указать на новый ID.
Проблема заключается в том, что когда я пытаюсь обновить ID записи, она нарушает целостность и немедленно сбой.
Я понимаю, что могу вставить новую запись с новым ID, а затем обновить дочерние элементы, а затем удалить старую запись, но у нас есть много триггеров, которые могут быть испорчены, если я это сделаю.
Есть ли способ временно обновить родителя с обещанием обновить дочерние элементы (очевидно, что это не сработает при фиксации) без кратковременного отключения внешнего ключа?
То, что вы хотите, это " отложенное ограничение ".
Вы можете выбрать между двумя типами отложенных ограничений, "INITIALLY IMMEDIATE" и "INITIALLY DEFERRED", чтобы управлять поведением по умолчанию - должна ли база данных по умолчанию проверять ограничение после каждого оператора, или если по умолчанию должна использоваться проверка только ограничений в конце сделки.
Отвечало медленнее, чем Чи, но считал, что было бы неплохо включить образец кода, чтобы ответ можно найти в SO.
Как ответил Чи, отложенные ограничения делают это возможным.
SQL> drop table t;
Table dropped.
SQL> create table T (ID number
2 , parent_ID number null
3 , name varchar2(40) not null
4 , constraint T_PK primary key (ID)
5 , constraint T_HIREARCHY_FK foreign key (parent_ID)
6 references T(ID) deferrable initially immediate);
Table created.
SQL> insert into T values (1, null, 'Big Boss');
1 row created.
SQL> insert into T values (2, 1, 'Worker Bee');
1 row created.
SQL> commit;
Commit complete.
SQL> -- Since initially immediate, the following statement will fail:
SQL> update T
2 set ID = 1000
3 where ID = 1;
update T
*
ERROR at line 1:
ORA-02292: integrity constraint (S.T_HIREARCHY_FK) violated - child record found
SQL> set constraints all deferred;
Constraint set.
SQL> update T
2 set ID = 1000
3 where ID = 1;
1 row updated.
SQL> update T
2 set parent_ID = 1000
3 where parent_ID = 1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from T;
ID PARENT_ID NAME
---------- ---------- ----------------------------------------
1000 Big Boss
2 1000 Worker Bee
SQL> -- set constraints all deferred during that transaction
SQL> -- and the transaction has commited, the next
SQL> -- statement will fail
SQL> update T
2 set ID = 1
3 where ID = 1000;
update T
*
ERROR at line 1:
ORA-02292: integrity constraint S.T_HIREARCHY_FK) violated - child record found
Я верю, но не смог найти ссылку, что отсрочка определяется во время создания ограничения и не может быть изменена позже. Значение по умолчанию не откладывается. Чтобы перейти к отложенным ограничениям, вам нужно сделать однократное падение и добавить ограничение. (Правильно запланированное, контролируемое и т.д.)
SQL> drop table t;
Table dropped.
SQL> create table T (ID number
2 , parent_ID number null
3 , name varchar2(40) not null
4 , constraint T_PK primary key (ID)
5 , constraint T_HIREARCHY_FK foreign key (parent_ID)
6 references T(ID));
Table created.
SQL> alter table T drop constraint T_HIREARCHY_FK;
Table altered.
SQL> alter table T add constraint T_HIREARCHY_FK foreign key (parent_ID)
2 references T(ID) deferrable initially deferred;
Table altered.
Общий совет со сценариями, подобными этому, заключается в использовании отложенных ограничений. Тем не менее, я думаю, что эти ситуации почти всегда являются провалом логики приложения или модели данных. Например, вставка дочерней записи и родительской записи в одной транзакции может быть проблемой, если мы выполним ее как два оператора:
Мои тестовые данные:
SQL> select * from t23 order by id, parent_id
2 /
ID PARENT_ID NAME
---------- ---------- ------------------------------
110 parent 1
111 parent 2
210 110 child 0
220 111 child 1
221 111 child 2
222 111 child 3
6 rows selected.
SQL>
Неправильный способ сделать что-то:
SQL> insert into t23 (id, parent_id, name) values (444, 333, 'new child')
2 /
insert into t23 (id, parent_id, name) values (444, 333, 'new child')
*
ERROR at line 1:
ORA-02291: integrity constraint (APC.T23_T23_FK) violated - parent key not
found
SQL> insert into t23 (id, parent_id, name) values (333, null, 'new parent')
2 /
1 row created.
SQL>
Однако Oracle поддерживает многозадачный INSERT synatx, который позволяет нам вставлять родительские и дочерние записи в один и тот же оператор, тем самым устраняя необходимость в отложенных ограничениях:
SQL> rollback
2 /
Rollback complete.
SQL> insert all
2 into t23 (id, parent_id, name)
3 values (child_id, parent_id, child_name)
4 into t23 (id, name)
5 values (parent_id, parent_name)
6 select 333 as parent_id
7 , 'new parent' as parent_name
8 , 444 as child_id
9 , 'new child' as child_name
10 from dual
11 /
2 rows created.
SQL>
Ситуация, в которой вы находитесь, похожа: вы хотите обновить первичный ключ родительской записи, но не можете из-за существования дочерних записей: и вы не можете обновлять дочерние записи, потому что нет родительского ключа, Catch-22:
SQL> update t23
2 set id = 555
3 where id = 111
4 /
update t23
*
ERROR at line 1:
ORA-02292: integrity constraint (APC.T23_T23_FK) violated - child record found
SQL> update t23
2 set parent_id = 555
3 where parent_id = 111
4 /
update t23
*
ERROR at line 1:
ORA-02291: integrity constraint (APC.T23_T23_FK) violated - parent key not
found
SQL>
Вновь решение должно сделать это в одном выражении:
SQL> update t23
2 set id = decode(id, 111, 555, id)
3 , parent_id = decode(parent_id, 111, 555, parent_id)
4 where id = 111
5 or parent_id = 111
6 /
4 rows updated.
SQL> select * from t23 order by id, parent_id
2 /
ID PARENT_ID NAME
---------- ---------- ------------------------------
110 parent 1
210 110 child 0
220 555 child 1
221 555 child 2
222 555 child 3
333 new parent
444 333 new child
555 parent 2
8 rows selected.
SQL>
Синтаксис в инструкции UPDATE немного неуклюжие, но обычно kludges. Дело в том, что нам не нужно часто обновлять столбцы первичного ключа. Действительно, поскольку неизменность является одной из характеристик "первичного ключа", нам не нужно действительно обновлять их вообще. Необходимость этого - отказ модели данных. Один из способов избежать таких сбоев - использовать синтетический (суррогатный) первичный ключ и просто обеспечить уникальность естественного ключа (aka business) с уникальным ограничением.
Итак, почему Oracle предлагает отложенные ограничения? Они полезны, когда мы проводим миграцию данных или загружаем массовые данные. Они позволяют нам очищать данные в базе данных без промежуточных таблиц. Нам действительно не нужны они для обычных задач приложения.
Рекомендации по использованию суррогатного ключа превосходны, ИМО.
В целом проблема с этой таблицей заключается в том, что ей не хватает первичного ключа. Напомним, что первичным ключом должно быть три вещи:
Базы данных Я знаком с соблюдением (1) и (2), но я не верю, что они соблюдают (3), что является неудачным. И то, что пинает вас в прикладе - если вы меняете свой "первичный ключ", вам нужно преследовать все ссылки на это ключевое поле и делать эквивалентные изменения, если вы не хотите нарушать целостность. Решение, как говорили другие, состоит в том, чтобы иметь истинный первичный ключ - тот, который является уникальным, не равным нулю, и который не изменяется.
Есть причины для всех этих маленьких правил. Это отличная возможность понять "неизменную" часть правил первичных ключей.
Поделитесь и наслаждайтесь.
Вам нужно использовать отложенное ограничение (см. ответ Chi).
В противном случае, чтобы добавить значение, которое приведет к сбою ограничения внешнего ключа, вам необходимо либо отключить, либо сбросить и заново создать ограничение внешнего ключа.
Ситуации, подобные этим, используют суррогатный ключ, который может быть изменен пользователями по мере необходимости, не влияя на ссылочную целостность. Чтобы расширить эту идею, в настоящее время настройка:
.. и бизнес-правила - это идентификатор, который может измениться. Что принципиально плохо с точки зрения дизайна - первичный ключ неизменен, уникален и не может быть нулевым. Таким образом, решение ситуации, когда вы строите свою модель данных, заключается в использовании:
SURROGATE_KEY - это столбец, который поддерживает изменения, не затрагивая ссылочную целостность - родительское и дочернее отношения не повреждены. Это означает, что пользователь может настраивать суррогатный ключ в их сердцах без необходимости отложенных ограничений, включать/отключать или отбрасывать/воссоздавать ограничения внешнего ключа, Вкл. ОБНОВЛЕНИЕ CASCADE...
Как правило, при моделировании данных вы НИКОГДА отображаете значения первичного ключа для пользователя из-за подобных ситуаций. Например, у меня есть клиент, который хочет, чтобы их номер задания изменился в начале года, с годом в начале номера (IE: 201000001 будет первым заданием, созданным в 2010 году). Что происходит, когда клиент продает компанию, а новому владельцу нужна другая схема для их учета? Или, что, если нумерация не может быть сохранена при переходе к другому поставщику базы данных?
Если бы это была другая база данных, кроме Oracle, вы могли бы объявить внешний ключ с помощью ON UPDATE CASCADE. Затем, если вы измените родительский идентификатор, он будет распространять изменение атомарно на родительский родитель.
К сожалению, Oracle реализует каскадные удаления, но не каскадные обновления.
(Этот ответ предназначен только для информационных целей, поскольку он фактически не решает вашу проблему.)