Запрос, при наличии unique constraint, выдаёт ошибку ORA-01779: нельзя изменить столбец, кот.отображает non key-preserved таблицу

#sql #oracle #constraints

В таблице user1.btable заведён unique constraint на 4 поля: c, d, e, f.

Запрос

update (select a.bid a$bid, b.bid b$bid
       from user1.atable a
       join user1.btable b
         on a.c = b.c
        and a.d = b.d
        and a.e = b.e
        and b.f = 0)
set a$bid = b$bid; 


выдаёт ошибку   


  ORA-01779: нельзя изменить столбец, кот.отображает non key-preserved таблицу


Почему? Ведь обычно такая ошибка возникает по следующей причине


  Ораклу надо уникально идентифицировать запись которую надо поменять. А для этого
подзапрос должен использовать все столбцы в уникальном ключе. Кроме того у вас в первом
случае может возникнуть проблема с тем, что для одной записи из таблицы A будет выбрано
несколько строк из балицы B и тогда ораклу будет не ясно а из какой из записей B взять
bid - Mike


А если в таблицу user1.atable добавить поле f, и изменить условие соединения на 

     on a.c = b.c
    and a.d = b.d
    and a.e = b.e
    and b.f = a.f


запрос ошибки не выдаёт.

Выдача SELECT * FROM V$VERSION


  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
     PL/SQL Release 11.2.0.1.0 - Production
     CORE   11.2.0.1.0  Production
     TNS for Linux: Version 11.2.0.1.0 - Production
     NLSRTL Version 11.2.0.1.0 - Production

    

Ответы

Ответ 1

В описанной Вами схеме все должно работать создал подобную тестовую схему:

CREATE TABLE TEST_TABLEA(  
  "C" VARCHAR2(10), 
  "D" VARCHAR2(10), 
  "E" VARCHAR2(10), 
  "F" VARCHAR2(10), 
  "BID" VARCHAR2(10));

CREATE TABLE TEST_TABLEB(
  "C" VARCHAR2(10), 
  "D" VARCHAR2(10), 
  "E" VARCHAR2(10), 
  "F" VARCHAR2(10), 
  "BID" VARCHAR2(10));

ALTER TABLE TEST_TABLEB
  add constraint CONSTR unique (C, D, E, F)
  using index;


И запрос (который Вы указывали в вопросе) на ней работает правильно, если есть constraint,
но если его нет то выбрасывается исключение ORA-01779.

Что бы избежать появление ошибки попробуйте следующий запрос:

update test_tablea 
set bid = 
   (select b.bid
    from   test_tableb b
    where  test_tablea.c = b.c
    and    test_tablea.d = b.d
    and    test_tablea.e = b.e
    and    b.f = 0)
where exists(
    select b.bid
    from   test_tableb b
    where  test_tablea.c = b.c
    and    test_tablea.d = b.d
    and    test_tablea.e = b.e
    and    b.f = 0)


или его аналог с nvl предложенный в комментариях, но тогда у Вас будет лишние update(ы).



После проверки на разных версиях БД удалось установить следующее join update НЕ заработал
на Oracle версий: 


Oracle Database 10g Enterprise Edition Release 10.2.0.4.0,
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0.


Работает на:


Oracle Database 11g Enterprise Edition Release 11.2.0.3.0,
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0,
Oracle Database 12c Standard Edition Release 12.1.0.2.0.


Ответ 2

В дополнение к ответу Mikhailov Valentine. На 10-ке запрос не работает, а на  SE
11.2.0.3.0 - работает. Судя по всему, в более новой версии при разборе запроса допускаются
некоторые классы выражений, помимо прямых отсылок к полям базовой таблицы. Так, для
простых выражений, вычисляемых на этапе разбора, и в итоге порождающих константу, запрос
срабатывает: b.f = 0 или case when 0 is null then 0 else 0 end. Но если это выражение
содержит отсылки к другим полям, то не сработает: b.f = a.f - a.f, case when a.f is
null then a.f else a.f end

Работает для условия b.f = a.f, но при этом не работает для условий: b.f = a.f +
0, b.f = nvl(a.f, 0)