Обеспечить уникальность подмножества строк в таблице в БД Oracle

Question

Для таблицы CARS с двумя столбцами, MAKE и MODEL, я хотел бы убедиться, что для некоторых конкретных значений MAKE их значения MODEL должны быть уникальными (при условии, что это база данных Oracle).

Например, я хочу использовать дубликаты для Ford, но не для Toyota. Я не хочу больше чем один ряд с МОДЕЛЬЮ 'F150' и МАРКОЙ 'Форда'. Тем не менее, можно иметь два ряда с моделью Rav4 и маркой Toyota. Также хорошо иметь ряд с МОДЕЛЬЮ F150 и МОДЕЛЕМ Ford и еще один ряд с МОДЕЛЬЮ F150 и МОДОЙ Тойоты.

Я исследовал различные стратегии с триггерами, ограничениями или индексами, но пока не нашел ничего, что дало бы мне необходимый уровень контроля.

Спасибо!

Answer 1

Вы можете создать уникальный индекс на основе функций для обеспечения уникальности пар (make,model) только тогда, когда make='Ford':

create table cars (make varchar2(255) not null, model varchar2(255) not null);

create unique index ford_index on cars (
  case make 
  when 'Ford' then make
  else null
  end,
  case make 
  when 'Ford' then model
  else null
  end  );


insert into cars (make, model) values ('Ford', 'F150');
insert into cars (make, model) values ('Ford', 'Fiesta');
insert into cars (make, model) values ('Toyota', 'Camry');
insert into cars (make, model) values ('Toyota', 'Camry');
insert into cars (make, model) values ('Ford', 'F150'); --- FAILS with unique constraint violation

ford_index индексирует (make,model) только в том случае, если make='Ford' - все остальное исключено из индекса и поэтому не подпадает под ограничение уникальности.

Answer 2

Создайте материализованное представление с быстрым обновлением при коммите. Определение MV может быть просто select make, model from <your_table> where make in (select make from <small_table>), <small_table> перечислит марки, для которых вы хотите применить эту уникальность. А затем создать уникальный индекс на (make, model) в материализованном виде.

Всякий раз, когда делается попытка дублирования с помощью make 'Ford', MV будет обновляться при коммите. Изменение в MV будет отклонено, что, в свою очередь, приведет к COMMIT терпеть неудачу.