Реализация условного внешнего ключа в Oracle

У меня есть требование, как указано ниже:

table1
------------------------
A    B     C    D   E    
------------------------
1    2     *    P    Q
1    2     A    Q    P
1    3     B    W    U
-----------------------

колонка A B C являются primary key на table1

table2
------------------------
A    B     C    
------------------------
1    2     1   
1    2     2   
1    2     A    
------------------------

колонка A B C должен быть реализован в виде составного внешнего ключа для table2 для столбца A B C в таблице 1 единственным исключением является столбец C в родительской таблице (таблица 1) есть *тогда любое значение может появиться в column C детского стола (table2) при условии column A а также column B одинаковы в обеих таблицах.

Мы ищем реализацию с минимальным триггером. В настоящее время мы не можем создать внешний ключ из-за этого странного требования.

Пожалуйста, предложите любой альтернативный подход и сделайте мой день:)

Источник

2 ответа

Решение

Эта проблема может быть решена без необходимости какой-либо новой структуры. просто введя new_column (NEW_C) в дочернюю таблицу (table2) и используя этот столбец в противоположном ключе внешнего ключа вместо "столбца C". Шаги ниже:

1>alter table2 add (new_c varchar2(1));

2>update table2 set new_c= c;

3> Используйте недавно введенный столбец NEW_C вместо colum C в ФК

alter table table2
  add constraint
  fk_ref_table1 FOREIGN KEY (A,B,**NEW_C**)    
  references table1 (A,B,C);

4> Создайте простой триггер вставки / обновления для дочерней таблицы, чтобы сохранить новый столбец NEW_C,

CREATE OR REPLACE maintain_new_c
 BEFORE
  INSERT OR UPDATE
  ON TABLE2 REFERENCING NEW AS NEW OLD AS OLD
 FOR EACH ROW
DECLARE
v_count pls_integer;
BEGIN
 SELECT COUNT(1) 
   INTO v_count
   FROM table1 t1    
  WHERE t1.c='*'
    AND t1.a= :NEW.a 
    AND t1.b= :NEW.b;

  IF v_count=0 THEN
    new_c := :NEW.c ;
  ELSE
    new_c := '*';
  END IF;

EXCEPTION
   WHEN OTHERS THEN
   RAISE_APPLICATION_ERROR..........
END;

Альтернативой является введение нового виртуального столбца (oracle 11g) в дочернюю таблицу. Используйте детерминистическую функцию pl / sql для получения этого виртуального столбца, а затем создайте ограничение внешнего ключа с использованием виртуального столбца.

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

Источник

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

Я думаю, что вы должны разделить обе таблицы на две, например:

table1_noC
------------------------
A    B     D   E    
------------------------
1    2     P    Q

table2_noC (with a FK to table1_noC)
------------------------
A    B     C    
------------------------
1    2     1   
1    2     2   
------------------------

table1_C
------------------------
A    B     C    D   E    
------------------------
1    2     A    Q    P
1    3     B    W    U
-----------------------

table2_C (with a FK to table1_C)
------------------------
A    B     C    
------------------------
1    2     A    
------------------------

Затем вы можете воссоздать свой оригинальный дизайн с видом, который сочетает в себе table1_noC + table1_C и другой взгляд, который объединяет table2_noC + table2_C, При необходимости вы даже можете добавить триггеры "вместо" к представлениям, чтобы преобразовать DML для представлений в необходимый DML для базовых таблиц.

Источник
Другие вопросы по тегам