Профессионалу разработчику

Полтавский П. В.
разработчик ПО
Компания : www.cgprojects.ru
www.ppol.newmail.ru

Отношение “один-к-одному” – реализация в БД.

1. Постановка задачи.

При разработке серверной части приложений СУБД Oracle одной из основных задач является обеспечение ограничений целостности данных, определяемых бизнес-логикой разрабатываемой системы. В некоторых случаях возникает необходимость реализации одной сущности (Entity) посредством двух таблиц, связанных отношением “один-к-одному”. О том, как обеспечить поддержку ограничения целостности в БД для этого отношения и пойдёт речь в данной статье.

2. Описание реализации.

Рассмотрим две таблицы, связанные отношением “один-к-одному” – FM_MESS (Сообщения) и FM_MESS_INVOICE (Счёт сообщения) (см. рис. 1).  

Рис. 1

Первичный ключ для каждой из таблиц определяется столбцом MSG_KEY. Помимо этого, на таблице FM_MESS_INVOICE создан внешний ключ (Foreign Key) по столбцу MSG_KEY, ссылающийся на таблицу FM_MESS. Эти ограничения целостности полностью определяют отношение “один-к-одному”. Предположим, что необходимо произвести удаление записи из той или другой таблицы. Что должно произойти в этом случае? При удалении записи из FM_MESS должна удаляться связанная с ней запись из FM_MESS_INVOICE. И наоборот, при удалении записи из FM_MESS_INVOICE, должна удаляться связанная с ней запись из FM_MESS. Для обеспечения выполнения этих условий предлагается механизм, реализуемый набором из следующих объектов:

- пакет БД FM_MESS_PK_SUPP

CREATE OR REPLACE PACKAGE FM_MESS_PK_SUPP AS      
…
   PROCEDURE Del_Detail (p_msg_key  IN NUMBER,
                         p_msg_type IN VARCHAR2); 
   --
   PROCEDURE Set_FM_Depo_Notif_Del(p_fm_depo_notif_del in varchar2);
   -- 
   FUNCTION Get_FM_Depo_Notif_Del
   RETURN varchar2;  
…
END;     

CREATE OR REPLACE PACKAGE BODY FM_MESS_PK_SUPP AS  

-- Глобальная переменная, определяющая из какой таблицы удаляется запись
fm_mess_inv_del         VARCHAR2(1);

-- Возвращает значение глоб. переменной fm_depo_notif_del в текущей сессии
FUNCTION Get_FM_Mess_Inv_Del
   RETURN varchar2 is
begin
  return fm_mess_inv_del;
end;

-- Устанавливает значение глоб. переменной fm_mess_inv_del в текущей сессии
procedure Set_FM_Mess_Inv_Del(p_fm_mess_inv_del in varchar2) is
begin
 fm_mess_inv_del:=p_fm_mess_inv_del;
end;
…
BEGIN
  fm_mess_inv_del  :='0';  
-- инициализация глобальной переменной по умолчанию
END;

- триггер БД на таблице FM_MESS

create or replace trigger FM_MESS_T2
before delete on FM_MESS
       for each row 
declare  
 v_fm_mess_inv_del    VARCHAR2(1); 
begin    

 v_fm_mess_inv_del:=FM_MESS_PK_SUPP.Get_FM_Mess_Inv_Del;

 if v_fm_mess_inv_del = '1' then   
-- первой удаляется запись FM_MESS_INVOICE
    FM_MESS_PK_SUPP.Set_FM_Mess_Inv_Del('0');
 else  
    FM_MESS_PK_SUPP.Set_FM_Mess_Inv_Del('2');  
-- первой удаляется запись FM_MESS

    delete from 
    fm_mess_invoice 
    where msg_key = p_msg_key;     
 end if;

exception
  when others then       
-- Сброс значения переменной (флаг, указывающий из какой таблицы удаляется запись) 
   FM_MESS_PK_SUPP.Set_FM_Mess_Inv_Del('0');         --      
   raise_application_error(-20010,
     'FM_MESS_T2: сообщ. №  '||to_char(:old.msg_key)||sqlerrm);
end;
/

- триггер БД на таблице FM_MESS_INVOICE

create or replace trigger FM_MESS_INVOICE_T1
after delete on FM_MESS_INVOICE
       for each row
declare
 v_fm_mess_inv_del VARCHAR2(1);
begin   
  v_fm_mess_inv_del:= FM_MESS_PK_SUPP.Get_FM_Mess_Inv_Del;

  if v_fm_mess_inv_del = '2' then 
-- первой удаляется запись FM_MESS
     FM_MESS_PK_SUPP.Set_FM_Mess_Inv_Del('0');
  else  
     FM_MESS_PK_SUPP.Set_FM_Mess_Inv_Del('1'); 
-- первой удаляется запись FM_MESS_INVOICE

     delete from 
     fm_mess
     where msg_key = :old.msg_key;    
  end if;

exception
  when others then
-- Сброс значения переменной (флаг, указывающий из какой таблицы удаляется запись)
     FM_MESS_PK_SUPP.Set_FM_Mess_Inv_Del('0');
   raise_application_error(-20020, 
     'FM_MESS_INVOICE_T1: сообщ. № '||to_char(:old.msg_key)||sqlerrm);
end;
/

При удалении записи из той или другой таблицы (FM_MESS или FM_MESS_INVOICE) срабатывает триггер FM_MESS_T2 или FM _MESS_INVOICE_T1. Функция FM_MESS_PK_SUPP.Get_FM_Mess_Inv_Del считывает значение глобальной переменной пакета fm_mess_inv_del, после чего происходит анализ этого значения. Если один из триггеров срабатывает раньше другого, то в первом сработавшем триггере выполняется удаление записи в связанной таблице и установка нового значения глобальной переменной fm_mess_inv_del (обеспечивается процедурой FM_MESS_PK_SUPP.Set_FM_Mess_Inv_Del). Если один из триггеров срабатывает позже другого, то происходит только установка нового значения глобальной переменной fm_mess_inv_del без удаления записи в связанной таблице. Таким образом, данный механизм позволяет обеспечить синхронизацию удаления записей из связанных таблиц FM_MESS и FM_MESS_INVOICE.

3. Заключение.

Предлагаемый механизм может быть использован для обеспечения поддержки ограничений целостности БД при реализации отношения “один-к-одному”.