Как предотвратить неверную вставку данных SQL

Question

В моем проекте мне нужно динамически проверять условия. Для этого создайте таблицу следующим образом.

CREATE TABLE myconditions 
( 
     conditionid INT IDENTITY PRIMARY KEY CLUSTERED, 
     minvalue    INT, 
     maxvalue    INT, 
     result      INT 
) 

и есть данные, содержащие следующее,

insert into MyConditions (MinValue, MaxValue, Result)
values (10, 20, 1), (20, 30, 2), (null, 10, 3), (30, null, 3)

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

declare @age int = 25 --this represents user age

select *
from MyConditions
where @age > isnull(MinValue, @age - 1)
  and @age <= isnull(MaxValue, @age)

но теперь проблема в том, предположим, если кто-то вставит недопустимый диапазон, например values (5, 25, 4) Я имею в виду, что это недействительно, потому что в базе данных уже есть (10, 20, 1) это ценности. когда @age = 15 оба условия будут выполнены. поэтому мне нужно предотвратить (5, 25, 4) это значение сложения. если кто-то должен добавить это (5, 25, 4) диапазон, этот диапазон значений (10, 20, 1) должны быть удалены.

Я вставляю эти данные в базу данных с помощью приложения ASP.NET MVC. Как я могу это сделать? В моем проекте используется Oracle. (в этом вопросе я использовал пример кода MS SQL, но мне нужен оракул)

Answer 1

Такой вид проверки целостности данных очень сложно реализовать надежным и производительным способом.

Для начала многое зависит от определения перекрывающегося диапазона. Например, можно утверждать, что все ваши диапазоны выборочных данных недействительны: maxvalue = 10 пересекается с minvalue = 10при условии, что границы тестируются с >= а также <= который по умолчанию. Точно так же, null границы создают сложность: если у вас есть существующий диапазон (30, null) является (40,50) действует?

Поэтому, как только вы разберетесь со своей бизнес-логикой, встанет вопрос об их реализации. В Oracle мы можем сделать что-то с помощью сложного триггера. Для каждой строки мы храним идентификатор вставленной / обновленной строки в массиве. Затем в конце оператора мы перебираем массив и запрашиваем таблицу в перекрестном соединении для сравнения диапазонов дат.

create or replace trigger myconditions_trg 
  for insert or update of minvalue, maxvalue 
    on myconditions 
  compound trigger 

  type condition_array is table of int 
    index by binary_integer; 
  conditions condition_array; 

  procedure validate_range (p_id in int) is 
    overlapping_range exception; 
    dummy char(1); 
  begin 
    begin 
      select null into dummy 
      from myconditions t1 
          , myconditions t2 
      where t1.conditionid = p_id 
      and t2.conditionid != p_id 
      and t1.minvalue != t2.minvalue 
      and ( 
           t1.minvalue between t2.minvalue and t2.maxvalue 
           or 
           t1.maxvalue between t2.minvalue and t2.maxvalue 
          ) 
      and rownum = 1; 
      raise overlapping_range; 
    exception 
      when no_data_found then 
        -- what we're hoping for, no overlaps found
        null; 
    end; 
  exception 
    when overlapping_range then 
      raise_application_error(-20000, 
        'overlapping range for id #' || p_id); 
  end validate_range; 

  procedure validate_ranges is 
    l_id int; 
  begin 
    l_id := conditions.first; 
    loop 
      exit when l_id is null; 
      validate_range (l_id); 
      l_id := conditions.next(l_id); 
    end loop; 
    conditions.delete; 
  exception 
    when others then 
      conditions.delete; 
      raise; 
  end validate_ranges; 

  BEFORE EACH ROW is 
  begin 
    -- store id to validate 
    conditions(:new.conditionid) := 1; 
  end before each row; 

  AFTER STATEMENT is 
  begin 
    validate_ranges; 
  end after statement; 

end myconditions_trg; 

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

Если вам интересно, я опубликовал демо-версию Oracle LiveSQL (требуется бесплатный вход, извините!). Найдите это здесь.

Answer 2

Для этого вам нужно использовать либо триггер, либо пользовательскую функцию.

Просто check ограничение может проверять значения только в одном. Честно говоря, я думаю, что триггер будет более распространенным подходом.

Точный синтаксис любого из них зависит от базы данных - и вы указали два из них - поэтому более подробный ответ не представляется возможным.