Один котелок не может подать два мяча подряд в крикет

Question

Я работаю над проектом Cricket. У меня есть таблица OverDetails. Я хочу вставить данные в эту таблицу.

ID  OverNumber  BowlerID  InningsID
1       1          150          1
2       4          160          1
3       3          165          1
4       2          150          1

Row_1, Row_2 а также Row_3 законны Row_4 Это не законно, потому что один котелок не может через два последовательных оверна в одном тайме. Не обязательно, чтобы оверы добавлялись последовательно в базу данных.

Я добавил ограничение в SQL Server.

# Constraint_1

ALTER TABLE OverDetails ADD CONSTRAINT UniqueOverInInning 
UNIQUE(OverNumber, BowlerID, IninngsID);

Это ограничение работает отлично.

Мне нужен чек, как это:

# Constraint_2

ALTER TABLE OverDetails ADD CONSTRAINT UniqueConsecutiveBowlerInOneInning 
CHECK (OverNumber + 1 != OverNumber and BowlerID + 1 != BowlerID 
         and IninngID + 1 != IninngID)

Answer 1

Вам нужна функция, которая возвращает последний BowlerID из заданного InningID:

    CREATE FUNCTION dbo.GetBowlerID
        ( @InningId INT, @OverNumber INT, @BowlerID INT)
        RETURNS INT
        AS
        BEGIN
        RETURN (SELECT top 1 CASE WHEN
(SELECT BowlerID
    FROM OverDetails 
    WHERE InningsId = @InningId AND OverNumber = @OverNumber - 1 ) = @BowlerID
OR
(SELECT BowlerID
    FROM OverDetails 
    WHERE InningsId = @InningId AND OverNumber = @OverNumber + 1 ) = @BowlerID
THEN 1 else 0 end)
        END

Затем вы можете поместить это в проверочное ограничение:

ALTER TABLE OverDetails ADD CONSTRAINT UniqueConsecutiveBowlerInOneInning 
  CHECK (dbo.GetBowlerID(InningsId, OverNumber, BowlerID)=0)

Answer 2

Проверочные ограничения не могут напрямую ссылаться на данные других строк. Есть некоторые методы, которые пытаются использовать UDF, чтобы обойти это ограничение, но они, как правило, не работают должным образом. Особенно в этом случае, где я предполагаю, что вставка строки 4 также должна быть заблокирована, если она имеет идентификатор bowlerID 165 так как это означало бы, что оверы 2 и 3 разделили боулера.

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

create table dbo.Bowling (
    ID int not null,
    OverNumber int not null,
    BowlerID int not null,
    InningsID int not null,
    constraint PK_Bowling PRIMARY KEY (ID),
    constraint UQ_Bowling_Overs UNIQUE (OverNumber,InningsID)
)
go
create view dbo.Bowling_DRI_SuccessiveOvers_Odd
with schemabinding
as
    select
        (OverNumber/2) as OddON,
        BowlerID
    from
        dbo.Bowling
go
create unique clustered index UQ_Bowling_DRI_SuccessiveOvers_Odd on dbo.Bowling_DRI_SuccessiveOvers_Odd (OddON,BowlerID)
go
create view dbo.Bowling_DRI_SuccessiveOvers_Even
with schemabinding
as
    select
        ((OverNumber+1)/2) as EvenON,
        BowlerID
    from
        dbo.Bowling
go
create unique clustered index UQ_Bowling_DRI_SuccessiveOvers_Even on dbo.Bowling_DRI_SuccessiveOvers_Even (EvenON,BowlerID)
go
insert into dbo.Bowling(ID,OverNumber,BowlerID,InningsID) values
(1,1,150,1),
(2,4,160,1),
(3,3,165,1)
go
insert into dbo.Bowling(ID,OverNumber,BowlerID,InningsID) values
(4,2,150,1)

Эта последняя вставка генерирует ошибку:

Сообщение 2601, Уровень 14, Состояние 1, Строка 37. Невозможно вставить строку повторяющегося ключа в объект 'dbo.Bowling_DRI_SuccessiveOvers_Even' с уникальным индексом 'UQ_Bowling_DRI_SuccessiveOvers_Even'. Дубликат значения ключа (1, 150). Заявление было прекращено.

Надеюсь, вы можете увидеть трюк, который я использую, чтобы эти представления проверяли желаемое ограничение - оно настроено так, что строки соединяются с их (логическим, основанным на OrderNumber) преемник или предшественник, основанный на делении OrderNumber на два с использованием целочисленной математики.

Затем мы применяем уникальные ограничения к этим парам, включая BowlerID, Только если один и тот же котелок подает два последовательных овера, мы сгенерируем более одного ряда с одним и тем же (OddON/EvenON) а также BowlerID ценности.

Answer 3

Может этот?

create function dbo.chk_fnk (@OverNumber int,  @BowlerID int,  @InningsID int)
returns int
as
begin
return 
      case when
           exists (select * 
                   from dbo.OverDetails
                   where BowlerID = @BowlerID and abs(OverNumber - @OverNumber) = 1 and InningsID = @InningsID)
           then 1
           else 0
      end;  
end;
go

ALTER TABLE dbo.OverDetails ADD CONSTRAINT UniqueConsecutiveBowlerInOneInning 
  CHECK (dbo.chk_fnk(OverNumber,  BowlerID,  InningsID) = 0);