Как может произойти сбой этого ограничения проверки SQL для временных диапазонов, когда таблица пуста?

Я реализовал проверку диапазона времени в качестве контрольного ограничения, используя функцию в SQL, используя это руководство, почти дословно.

Сначала создайте функцию:

create function dbo.ValidateStatusPeriodInfoTimeRange
(
    @btf_id VARCHAR(32),
    @start_time BIGINT,
    @end_time BIGINT
)
returns bit
as
begin
declare @Valid bit = 1;

if exists( select *
           from   dbo.StatusPeriodInfoOccurrence o
           where  o.btf_id = @btf_id
           and    @start_time <= o.end_time and o.start_time <= @end_time )
   set @Valid = 0;
return @Valid;

конец

А затем ограничение, используя функцию:

alter table dbo.StatusPeriodInfoOccurrence with nocheck add constraint 

CK_StatusPeriodInfoOccurrence_ValidateTimeRange 
    check (dbo.ValidateStatusPeriodInfoTimeRange(btf_id, start_time, end_time) = 1);

Когда я пытаюсь вставить элемент в совершенно пустую таблицу, я получаю:

The INSERT statement conflicted with the CHECK constraint 
"CK_StatusPeriodInfoOccurrence_ValidateTimeRange". The conflict occurred in 
database "D600600TD01_BSM_Surveillance", table "dbo.StatusPeriodInfoOccurrence".

Я попытался выяснить, сделал ли я что-то не так в самой функции, и создал этот запрос, чтобы проверить его возвращаемое значение:

DECLARE @ReturnValue INT
EXEC @ReturnValue =  ValidateStatusPeriodInfoTimeRange
@btf_id = 'a596933eff9143bceda5fc5d269827cd',
@start_time = 2432432,
@end_time = 432432423
SELECT @ReturnValue

Но это возвращает 1, как и должно быть.

Я в недоумении, как продолжить отладку этого. Все части вроде работают, а целое нет. Любые идеи о том, как оператор вставки может конфликтовать с ограничением проверки?

Изменить: вот мой оператор вставки для завершения:

INSERT INTO StatusPeriodInfoOccurrence (btf_id, start_time, end_time) VALUES ('a596933eff9143bceda5fc5d269827cd',2432432,432432423);

Существует дополнительный столбец первичного ключа с автоматическим приращением идентификатора.


sql sql-server sql-function check-constraints
person jumps4fun    schedule 26.03.2018    source источник
comment
Ограничение CHECK не выполняется, потому что вставленная вами строка нарушает то самое условие, которое она должна проверять — проверка логически происходит после выполнения вставки (но до ее отката). Я думаю, что автор хотел написать where o.btf_id <> @btf_id, а не where o.btf_id = @btf_id, чтобы избежать именно этого случая. (Это предполагает, что btf_id сам по себе является ключом, поэтому дубликаты не могут быть вставлены.)   -  person Jeroen Mostert    schedule 26.03.2018
comment
у меня похожая проблема. Я создал новую таблицу только с этим ограничением. Теперь я не могу вставить какие-либо строки, так как проверка всегда терпит неудачу...   -  person Michał Turczyn    schedule 26.03.2018
comment
@JeroenMostert Я прочитал статью, хорошее упражнение в сочетании с вашим комментарием. Но равенство идентификаторов в этом случае имеет решающее значение. Смысл такой проверки в том, чтобы избежать перекрытия периодов времени для одного идентификатора.   -  person Michał Turczyn    schedule 26.03.2018
comment
да, btf_id — это не первичный ключ для этой таблицы, а ссылка на внешний ключ. Так что однозначно нужен.   -  person jumps4fun    schedule 26.03.2018
comment
у меня нет триггеров   -  person jumps4fun    schedule 26.03.2018
comment
В этом случае вам придется добавить суррогатный ключ (например, идентификатор), который позволяет различать строки, иначе этот подход обречен. Вне триггера невозможно отличить вновь добавленную строку от существующей.   -  person Jeroen Mostert    schedule 26.03.2018
comment
@JeroenMostert, если проверка происходит после вставки по умолчанию, знаете ли вы какой-либо способ изменить порядок выполнения? В противном случае весь этот способ сделать это кажется мне невозможным, по крайней мере. Было бы обидно, так как мне действительно очень понравилась идея таких ограничений проверки.   -  person jumps4fun    schedule 26.03.2018
comment
@JeroenMostert, я попытаюсь реализовать SCOPE_IDENTITY() внутри функции. Это должно получить последний вставленный идентификатор и, надеюсь, проигнорировать это единственное вхождение.   -  person jumps4fun    schedule 26.03.2018
comment
Не делай этого! Ограничения CHECK должны быть действительными в каждом контексте, и добавление SCOPE_IDENTITY приведет к серьезному нарушению, если строка будет обновлена. Явно передайте идентификатор строки в функцию.   -  person Jeroen Mostert    schedule 26.03.2018
comment
Как я могу это сделать, когда идентификатор является автоматически увеличивающимся целым числом?   -  person jumps4fun    schedule 26.03.2018
comment
Помните: CHECK происходит после логической вставки строки, поэтому значение уже известно. Вы можете просто определить ограничение как check (dbo.ValidateStatusPeriodInfoTimeRange(id, btf_id, start_time, end_time) = 1) и использовать @id и id в функции.   -  person Jeroen Mostert    schedule 26.03.2018
comment
@JeroenMostert, ты гений :) Теперь это работает как шарм! Если ты напишешь это как ответ, я сразу приму это. Спасибо за ваши навыки и терпение :)   -  person jumps4fun    schedule 26.03.2018

Ответы (2)


arrow_upward
3
arrow_downward

CHECK ограничения возникают после того, как строка вставлена, поэтому в своей текущей форме ограничение не работает, потому что сама вставленная строка соответствует ограничению. Чтобы это работало как ограничение (а не триггер), должен быть способ отличить проверяемую строку от всех остальных строк. Michał answer показывает, как это сделать, не полагаясь на IDENTITY, но если у вас есть явное исключение строки, это может быть проще :

create function dbo.ValidateStatusPeriodInfoTimeRange
(
    @id INT,
    @btf_id VARCHAR(32),
    @start_time BIGINT,
    @end_time BIGINT
)
returns bit
as
begin
declare @Valid bit = 1;

if exists( select *
           from   dbo.StatusPeriodInfoOccurrence o
           where  o.id <> @id AND o.btf_id = @btf_id
           and    @start_time <= o.end_time and o.start_time <= @end_time )
   set @Valid = 0;
return @Valid;
end;

с ограничением, определенным как

check (dbo.ValidateStatusPeriodInfoTimeRange(id, btf_id, start_time, end_time) = 1)

Независимо от подхода, индексы на (btf_id, start_time) и (btf_id, end_time) — хорошая идея, чтобы сохранить масштабируемость, иначе при каждой вставке необходимо полное сканирование таблицы.

person Jeroen Mostert    schedule 26.03.2018

arrow_upward
2
arrow_downward

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

select *
from   dbo.StatusPeriodInfoOccurrence o
where  o.btf_id = @btf_id
and    @start_time <= o.end_time and o.start_time <= @end_time

всегда будет возвращать хотя бы одну строку (вставляемую).

Итак, зная это, вы должны проверить, возвращает ли запрос более одной записи, поэтому условие в операторе if должно выглядеть так:

if (select count(*)
          from   dbo.StatusPeriodInfoOccurrence o
          where  o.btf_id = @btf_id
          and    @start_time <= o.end_time and o.start_time <= @end_time ) > 1

Это решение отлично работает (проверено на моей БД).

person Michał Turczyn    schedule 26.03.2018
comment
Очень умное решение. Я считаю, что это будет работать каждый раз, хотя я думаю, что способ Jeroen с явной проверкой идентификатора немного чище и легко читается :) - person jumps4fun; 26.03.2018
comment
Боюсь, чище не будет. Также требуется добавить дополнительный столбец в ваши таблицы, тем самым изменив функцию. - person Michał Turczyn; 26.03.2018
comment
Да, вы попали в точку. Хотя колонка у меня уже была. Тогда не чище, но легче читать. По крайней мере для меня. И это было первое предоставленное решение, хотя и в комментариях. - person jumps4fun; 26.03.2018
comment
Преимущество этого заключается в том, что он работает даже без столбца IDENTITY. Я все равно напишу ответ, потому что характеристики производительности подходов могут быть разными, даже если поля диапазона оба проиндексированы (хотя на практике я этого не ожидаю). - person Jeroen Mostert; 26.03.2018
comment
Я хотел бы отметить оба ответа как принятые, но я должен пойти с тем, кто первым решил проблему. Хотя оба великолепны - person jumps4fun; 26.03.2018