Как я могу временно сократить ссылочную целостность, в рамках транзакции, не отключая ограничение внешнего ключа?

Ответ 1

То, что вы хотите, это " отложенное ограничение ".

Вы можете выбрать между двумя типами отложенных ограничений, "INITIALLY IMMEDIATE" и "INITIALLY DEFERRED", чтобы управлять поведением по умолчанию - должна ли база данных по умолчанию проверять ограничение после каждого оператора, или если по умолчанию должна использоваться проверка только ограничений в конце сделки.

Ответ 2

Отвечало медленнее, чем Чи, но считал, что было бы неплохо включить образец кода, чтобы ответ можно найти в 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.

Ответ 3

Общий совет со сценариями, подобными этому, заключается в использовании отложенных ограничений. Тем не менее, я думаю, что эти ситуации почти всегда являются провалом логики приложения или модели данных. Например, вставка дочерней записи и родительской записи в одной транзакции может быть проблемой, если мы выполним ее как два оператора:

Мои тестовые данные:

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 предлагает отложенные ограничения? Они полезны, когда мы проводим миграцию данных или загружаем массовые данные. Они позволяют нам очищать данные в базе данных без промежуточных таблиц. Нам действительно не нужны они для обычных задач приложения.

Ответ 4

Рекомендации по использованию суррогатного ключа превосходны, ИМО.

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

• Уникальный
• ненулевыми
• неизменная

Базы данных Я знаком с соблюдением (1) и (2), но я не верю, что они соблюдают (3), что является неудачным. И то, что пинает вас в прикладе - если вы меняете свой "первичный ключ", вам нужно преследовать все ссылки на это ключевое поле и делать эквивалентные изменения, если вы не хотите нарушать целостность. Решение, как говорили другие, состоит в том, чтобы иметь истинный первичный ключ - тот, который является уникальным, не равным нулю, и который не изменяется.

Есть причины для всех этих маленьких правил. Это отличная возможность понять "неизменную" часть правил первичных ключей.

Поделитесь и наслаждайтесь.

Ответ 5

Вам нужно использовать отложенное ограничение (см. ответ Chi).
В противном случае, чтобы добавить значение, которое приведет к сбою ограничения внешнего ключа, вам необходимо либо отключить, либо сбросить и заново создать ограничение внешнего ключа.

Ситуации, подобные этим, используют суррогатный ключ, который может быть изменен пользователями по мере необходимости, не влияя на ссылочную целостность. Чтобы расширить эту идею, в настоящее время настройка:

• ID (pk)
• PARENT_ID (внешний ключ, столбец идентификаторов ссылок - делает его самореляционным)

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

• ID (pk)
• PARENT_ID (внешний ключ, столбец идентификаторов ссылок - делает его самореляционным)
• SURROGATE_KEY (уникальное ограничение)

SURROGATE_KEY - это столбец, который поддерживает изменения, не затрагивая ссылочную целостность - родительское и дочернее отношения не повреждены. Это означает, что пользователь может настраивать суррогатный ключ в их сердцах без необходимости отложенных ограничений, включать/отключать или отбрасывать/воссоздавать ограничения внешнего ключа, Вкл. ОБНОВЛЕНИЕ CASCADE...

Как правило, при моделировании данных вы НИКОГДА отображаете значения первичного ключа для пользователя из-за подобных ситуаций. Например, у меня есть клиент, который хочет, чтобы их номер задания изменился в начале года, с годом в начале номера (IE: 201000001 будет первым заданием, созданным в 2010 году). Что происходит, когда клиент продает компанию, а новому владельцу нужна другая схема для их учета? Или, что, если нумерация не может быть сохранена при переходе к другому поставщику базы данных?

Ответ 6

Если бы это была другая база данных, кроме Oracle, вы могли бы объявить внешний ключ с помощью ON UPDATE CASCADE. Затем, если вы измените родительский идентификатор, он будет распространять изменение атомарно на родительский родитель.

К сожалению, Oracle реализует каскадные удаления, но не каскадные обновления.

(Этот ответ предназначен только для информационных целей, поскольку он фактически не решает вашу проблему.)