Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Firebird, InterBase Новый топик    Ответить
 FB4: violation of PRIMARY or UNIQUE KEY constraint  [new]
dedRasta
Member

Откуда:
Сообщений: 129
В FB 2.5.8 имею следующие две таблицы:
+
CREATE TABLE EXTDOSES (
    EXTDOSEREC_ID  INTEGER NOT NULL,
    MISSION_ID     INTEGER NOT NULL,
    DDATE          D_DATE /* D_DATE = DATE */,
    BEGDATE        D_DATE /* D_DATE = DATE */,
    ENDDATE        D_DATE /* D_DATE = DATE */,
    YEAR_REP       INTEGER NOT NULL,
    DOSETERM_ID    INTEGER NOT NULL,
    PERSONNEL_ID   INTEGER NOT NULL,
    PERSLOGREC_ID  D_INTEGER DEFAULT 0 /* D_INTEGER = INTEGER */,
    EXPTIME        INTEGER,
    DOSETYPE_ID    D_INTEGER DEFAULT 1 NOT NULL /* D_INTEGER = INTEGER */,
    METHOD_ID      INTEGER DEFAULT 1 NOT NULL,
    DOSEVALUE      FLOAT DEFAULT 0 NOT NULL,
    DOSEERR        FLOAT,
    INDOSE         CHAR(1) DEFAULT '-' NOT NULL
);

+
CREATE TABLE DOSES (
    DOSEREC_ID     INTEGER NOT NULL,
    PERSONNEL_ID   INTEGER NOT NULL,
    YEAR_REP       INTEGER NOT NULL,
    DOSETERM_ID    INTEGER NOT NULL,
    PERSLOGREC_ID  INTEGER NOT NULL,
    METHOD_ID      INTEGER NOT NULL,
    DOSETYPE_ID    INTEGER NOT NULL,
    EXPTIME        INTEGER,
    DOSEVALUE      FLOAT NOT NULL,
    DOSEERR        D_FLOAT /* D_FLOAT = FLOAT */,
    DATESTAMP      TIMESTAMP,
    COMMENT_R      VARCHAR(250)
);
ALTER TABLE DOSES ADD CONSTRAINT UNQ_DOSES UNIQUE (DOSETYPE_ID, PERSONNEL_ID, YEAR_REP, DOSETERM_ID);

Обновляю вторую таблицу данными из первой:
+
execute block
as
declare variable CID integer;
declare variable PID integer;
declare variable YREP integer;
declare variable Q integer;
declare variable PL integer;
declare variable M integer;
declare variable DT integer;
declare variable ET integer;
declare variable DV float;
declare variable DE float;
declare variable TS timestamp;
declare variable DID integer;
begin
  for select
    extdoses.extdoserec_id,
    extdoses.personnel_id,
    extdoses.year_rep,
    extdoses.doseterm_id,
    personal.lcplid as perslogrec_id,
    extdoses.method_id,
    extdoses.dosetype_id,
    extdoses.exptime,
    extdoses.dosevalue,
    extdoses.doseerr,
    extdoses.ddate,
    doses.doserec_id
  from extdoses
    join personal on extdoses.personnel_id = personal.personnel_id
    left join doses on  (extdoses.personnel_id  = doses.personnel_id)
                    and (extdoses.year_rep      = doses.year_rep)
                    and (extdoses.doseterm_id   = doses.doseterm_id)
                    and (extdoses.dosetype_id   = doses.dosetype_id)
  where
    (extdoses.indose = '-')
  into
    :cid,
    :pid,
    :yrep,
    :q,
    :pl,
    :m,
    :dt,
    :et,
    :dv,
    :de,
    :ts,
    :did
  do begin
    if (:did is null) then
      insert into doses (
        personnel_id,
        year_rep,
        doseterm_id,
        perslogrec_id,
        method_id,
        dosetype_id,
        exptime,
        dosevalue,
        doseerr,
        datestamp,
        comment_r
        )
      values (
        :pid,
        :yrep,
        :q,
        :pl,
        :m,
        :dt,
        :et,
        :dv,
        :de,
        :ts,
        'Вставлено из доп. доз'
      );
    else
      update doses
      set doses.exptime   = doses.exptime + :et,
          doses.dosevalue = doses.dosevalue + :dv
      where doses.doserec_id = :did;
    did = null;
    update extdoses set extdoses.indose = '+' where extdoses.extdoserec_id = :cid;
  end
end

Все срабатывает нормально:
2289 записей было обновлено в таблице EXTDOSES
2072 записей было обновлено в таблице DOSES
217 записей было добавлено в таблицу DOSES

То же самое с теми же данными (Restore из того же Backup) делаю в FB 4.0 Beta1 Release и получаю:
Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values.
violation of PRIMARY or UNIQUE KEY constraint "UNQ_DOSES" on table "DOSES".
Problematic key value is ("DOSETYPE_ID" = 18, "PERSONNEL_ID" = 2853, "YEAR_REP" = 2018, "DOSETERM_ID" = 4).
At block line: 55, col: 7.
20 мар 19, 16:11    [21838689]     Ответить | Цитировать Сообщить модератору
 Re: FB4: violation of PRIMARY or UNIQUE KEY constraint  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 27915
dedRasta,

update/insert из for select без plan sort на 2.5 если и работал, то случайно - внешний for select мог увидеть вставляемые или обновляемые записи. в 4.0 - не видит.

используй
https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-merge.html
20 мар 19, 16:45    [21838722]     Ответить | Цитировать Сообщить модератору
 Re: FB4: violation of PRIMARY or UNIQUE KEY constraint  [new]
dedRasta
Member

Откуда:
Сообщений: 129
Вспомнил, эта тема здесь обсуждалась и тогда говорилось, что в дальнейшем
это поведение будет пресечено.

Переделал с использованием MERGE:
+
merge into DOSES D
using (
  select
    extdoses.extdoserec_id,
    extdoses.personnel_id,
    extdoses.year_rep,
    extdoses.doseterm_id,
    personal.lcplid,
    extdoses.method_id,
    extdoses.dosetype_id,
    extdoses.exptime,
    extdoses.dosevalue,
    extdoses.doseerr,
    extdoses.ddate
  from extdoses
    join missions on extdoses.mission_id = missions.mission_id
    join personal on extdoses.personnel_id = personal.personnel_id
  where
    (extdoses.indose = '-')
  and (missions.is_ext <> 1)
) e
on  (e.personnel_id  = d.personnel_id)
  and (e.year_rep      = d.year_rep)
  and (e.doseterm_id   = d.doseterm_id)
  and (e.dosetype_id   = d.dosetype_id)
when matched then
  update
  set d.exptime   = d.exptime   + e.exptime,
      d.dosevalue = d.dosevalue + e.dosevalue
when not matched then
  insert (
    personnel_id,
    year_rep,
    doseterm_id,
    perslogrec_id,
    method_id,
    dosetype_id,
    exptime,
    dosevalue,
    doseerr,
    datestamp,
    comment_r
  )
  values (
    e.personnel_id,
    e.year_rep,
    e.doseterm_id,
    e.lcplid,
    e.method_id,
    e.dosetype_id,
    e.exptime,
    e.dosevalue,
    e.doseerr,
    e.ddate,
    'Вставлено из доп. доз'
  );


Выполняю, получаю:

Invalid insert or update value(s): object columns are constrained - no 2 table rows can have duplicate column values.
violation of PRIMARY or UNIQUE KEY constraint "UNQ_DOSES" on table "DOSES".
Problematic key value is ("DOSETYPE_ID" = 18, "PERSONNEL_ID" = 2853, "YEAR_REP" = 2018, "DOSETERM_ID" = 4).


Получается, что MERGE не видит вновь введенных записей при проверке MATCHING, пытается сделать INSERT вместо UPDATE и обламывается.

В доке написано:

Если условие WHEN MATCHED присутствует, и несколько записей совпадают с записями
в целевой таблице, UPDATE выполнится для всех совпадающих записей источника, и
каждое последующее обновление перезапишет предыдущее. Это нестандартное поведение:
стандарт SQL-2003 требует, чтобы в этой ситуации выдавалось исключение (ошибка).



В принципе мне именно это и нужно (кумулятивное суммирование в найденные записи),
но раз нельзя, значит нельзя.
22 мар 19, 11:03    [21840454]     Ответить | Цитировать Сообщить модератору
 Re: FB4: violation of PRIMARY or UNIQUE KEY constraint  [new]
kdv
Member

Откуда: iBase.ru
Сообщений: 27915
dedRasta,

эээ, то есть, исходная идея, и работает на 2.5, чтобы если записи нет, она была вставлена, а ПОТОМ еще и ОБНОВЛЕНА?
Типа, увидит-не-увидит? Что это за ... алгоритм такой?
dedRasta
Получается, что MERGE не видит вновь введенных записей при проверке MATCHING

да почему он их должен увидеть? Если надо сначала вставить записи, которых нет, а потом обновить, то так и надо делать, в два этапа.
А не ориентироваться на то, что в 2.5 курсор видит вставляемые записи. Закладываться на конкретную реализацию, которая еще и "нестабильна" - это пипец.
22 мар 19, 12:39    [21840606]     Ответить | Цитировать Сообщить модератору
 Re: FB4: violation of PRIMARY or UNIQUE KEY constraint  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 9393
dedRasta
В доке написано:

Если условие WHEN MATCHED присутствует, и несколько записей совпадают с записями
в целевой таблице, UPDATE выполнится для всех совпадающих записей источника, и
каждое последующее обновление перезапишет предыдущее. Это нестандартное поведение:
стандарт SQL-2003 требует, чтобы в этой ситуации выдавалось исключение (ошибка).



В принципе мне именно это и нужно (кумулятивное суммирование в найденные записи),
но раз нельзя, значит нельзя.


это примечание касается многократного UPDATE, когда запись была в целевой таблице изначально. Впрочем даже эта часть будет выполнена не верно
22 мар 19, 12:45    [21840622]     Ответить | Цитировать Сообщить модератору
 Re: FB4: violation of PRIMARY or UNIQUE KEY constraint  [new]
Симонов Денис
Member

Откуда: Рязань
Сообщений: 9393
dedRasta
В принципе мне именно это и нужно (кумулятивное суммирование в найденные записи), но раз нельзя, значит нельзя.


это вы так думаете. На самом деле суммы можно посчитать ещё в USING, сделав таким образом однократный INSERT или UPDATE ровно одной записи.
22 мар 19, 12:49    [21840629]     Ответить | Цитировать Сообщить модератору
 Re: FB4: violation of PRIMARY or UNIQUE KEY constraint  [new]
dedRasta
Member

Откуда:
Сообщений: 129
Грин
Гоп вызвал его в каюту и, раскрыв истрепанную книгу, сказал:
—Слушай внимательно! Брось курить! Начинается отделка щенка под капитана.
И он стал читать, — вернее, говорить и кричать, по книге, древние слова моря.

Спасибо! Теперь работает и в FB4 и в FB25:
+
create or alter procedure EXTDOSES2DOSES
as
begin
  merge into doses
  using (
    select distinct
      extdoses.personnel_id,
      personal.LCPLID,
      extdoses.year_rep,
      extdoses.doseterm_id,
      extdoses.dosetype_id
    from extdoses
      join personal on extdoses.personnel_id = personal.personnel_id
      join missions on extdoses.mission_id = missions.mission_id
    where
      (extdoses.indose = '-')
      and (missions.is_ext <> 1)
  ) e
  on  (e.personnel_id    = doses.personnel_id)
      and (e.year_rep    = doses.year_rep)
      and (e.doseterm_id = doses.doseterm_id)
      and (e.dosetype_id = doses.dosetype_id)
  when not matched then
  insert
  ( personnel_id,
    year_rep,
    doseterm_id,
    perslogrec_id,
    method_id,
    dosetype_id,
    exptime,
    dosevalue,
    doseerr,
    datestamp,
    comment_r
  )
  values (
    e.personnel_id,
    e.year_rep,
    e.doseterm_id,
    e.LCPLID,
    1,
    e.dosetype_id,
    0,
    0,
    null,
    current_timestamp,
    'Вставлено из доп. доз'
  );

  merge into DOSES D
  using (
    select
      extdoses.personnel_id,
      extdoses.year_rep,
      extdoses.doseterm_id,
      extdoses.dosetype_id,
      sum(extdoses.exptime) sumet,
      sum(extdoses.dosevalue) sumdv
    from extdoses
      join missions on extdoses.mission_id = missions.mission_id
      join personal on extdoses.personnel_id = personal.personnel_id
    where
      (extdoses.indose = '-')
      and (missions.is_ext <> 1)
    group by 1, 2, 3, 4
  ) e
  on    (e.personnel_id   = d.personnel_id)
          and (e.year_rep = d.year_rep)
    and (e.doseterm_id   = d.doseterm_id)
    and (e.dosetype_id   = d.dosetype_id)
  when matched then
    update
    set d.exptime   = d.exptime   + e.sumet,
        d.dosevalue = d.dosevalue + e.sumdv;

  update extdoses
  set extdoses.INDOSE = '+'
  where extdoses.INDOSE = '-'
  and (select missions.IS_EXT from MISSIONS where MISSIONS.MISSION_ID = extdoses.MISSION_ID) is distinct from 1;
end
25 мар 19, 09:52    [21842449]     Ответить | Цитировать Сообщить модератору
Все форумы / Firebird, InterBase Ответить