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

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

Ситуация, в которой вы находитесь, похожа: вы хотите обновить первичный ключ родительской записи, но не можете из-за существования дочерних записей: И вы не можете обновить дочерние записи, потому что нет родительского ключа. Ловушка-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 немного неуклюж, но это обычно так. Суть в том, что нам не нужно часто обновлять столбцы первичного ключа. Действительно, поскольку неизменяемость является одной из характеристик "первичного ключа", мы вообще не должны обновлять их. Необходимость в этом - это сбой модели данных. Один из способов избежать таких сбоев - использовать синтетический (суррогатный) первичный ключ и просто обеспечить уникальность естественного (он же рабочий) ключа с помощью уникального ограничения.

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

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

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

1. Уникальный
2. Непустой
3. Неизменяемый

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

У всех этих маленьких правил есть причины. Это прекрасная возможность понять «неизменную» часть правил первичного ключа.

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

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

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

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

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

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

SURROGATE_KEY - это столбец, который поддерживает изменения без нарушения референтной целостности - отношения родителя и ребенка остаются нетронутыми. Это означает, что пользователь может изменять суррогатный ключ по своему усмотрению, не нуждаясь в отложенных ограничениях, включении/отключении или отбрасывании/создании ограничений внешнего ключа, ON UPDATE CASCADE...

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

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

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

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