Одновременное обновление родительской и дочерней таблицы по ключу

#sql #oracle

Есть table1 c tab1_id primary key  и table2 c tab1_id foreign key. 
Нужно изменить table1.tab1_id.
При попытке изменить tab1_id в любой из таблиц ругается на нарушение целостности:

Update table1 set tab1_id = 22 where tab1_id = 11



  ORA-02292: "обнаружена порожденная запись"


Update table2 set tab1_id = 22 where tab1_id = 11



  ORA-02292: "Исходный ключ не найден".


Можно ли сделать апдейт сразу в 2 таблицы? Или как обойти это ограничение не внося
ограничение в структуру таблицы (без alter table)?
    

Ответы

Ответ 1

Вариант 1: 

шаг 1. выключаем внешние ключи(alter table disable constraint)

шаг 2. обновляем данные для обоих таблиц(update table1 set tab1_id = .., update table2)

шаг 3. включаем обратно внешние ключи(alter table enable constraint)

Вариант 2:

Объявляем наш constraint как defferable.

alter table table2 
add constraint constraint_name foreign key (tab1_id)
references table1(tab1_id) deferrable;


Обновление таблицы выполняем в PL\SQL блоке:

begin
  execute immediate 'set constraint constraint_name deferred';
  update table1 ..;
  update table2 ..;
  commit;
end;


PS:

У вас что-то странное с архитектурой БД. При правильной архитектуре не должно возникать
ситуации, когда необходимо править ID.


Ответ 2

Если возник такой вопрос, значит скорее всего вследствии ошибки "поломан" не один
единственный ключ. В этом случае, надо:


ограничить доступ к БД переведя её в restricted session mode
отключить внешние ключи с alter table t2 modify constraint t2_fk1 disable;
изменить ключи 
снова включить ключи и перевести БД в нормальный режим.




Если это всё-таки единичный случай, можно сделать move (скопировать-удалить в одной
транзакции), например:

create table t1 (id number primary key);
create table t2 (
    id number, memo varchar2 (32), constraint t2_fk1 foreign key (id) references t1(id)
);

insert into t1 values (11); 
insert into t2 select 11, 'memo'||rownum from xmlTable ('1 to 2');

create or replace procedure moveKeys (oldId number, newId number) is
    newRow t1%rowtype;  
begin
    select * into newrow from t1 where id = oldId;
    newRow.id := newId;
    insert into t1 values newRow;
    update t2 set id = newId where id = oldId;
    delete t1 where id = oldId;  
end;
/
exec moveKeys (11, 22); commit;

select * from t1 join t2 on t2.id = t1.id;

        ID         ID MEMO                           
---------- ---------- --------------------------------
        22         22 memo1                           
        22         22 memo2