One Bowler can not bowl two consecutive over in cricket

Question

I am working on Cricket Project. I have a table OverDetails. I want to insert data in this table.

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

Row_1, Row_2 and Row_3 are legal. Row_4 is not legal, because one bowler can not through two consecutive overs in one innings. It is not necessary that overs are added consecutively in database.

I have added a constraint in SQL Server.

#Constraint_1

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

This constraint works perfectly.

I need a check like this:

#Constraint_2

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

You need to put it into a function - the function should return the last bowler id from your table and in the constraint you just check if it is different than the bowler id you want to insert — PacoDePaco, Sep 20 '17 at 06:19
which function? Can you please explain a little bit. Because I am new in Sql and I don't know much — Ishtiaq Ahmed, Sep 20 '17 at 06:21
Please read my table again. It is not necessary that Overs are added consecutively in database. In second row OverNumber is 4 — Ishtiaq Ahmed, Sep 20 '17 at 06:28
Shouldn't constraint 1 just be on `OverNumber` and `IninngsID`? Why is `BowlerID` in there? Are you saying it's okay for *two different bowlers to both bowl the same over in the same innings?* — Damien_The_Unbeliever, Sep 20 '17 at 06:39
No. Constraint one is Ok. One over can be through-en only once. In case a bowler is injured, then another bowler will complete his over. That is why I have added BowlerID in first constraint. — Ishtiaq Ahmed, Sep 20 '17 at 06:52

PacoDePaco · Accepted Answer · 2017-09-20T07:56:29.377

1

You need a function which returns a last BowlerID from a given 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

Then you can put it into a check constraint:

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

edited Sep 20 '17 at 07:56

answered Sep 20 '17 at 06:28

PacoDePaco

689
5
16

The problem is, rows are added out of order. As the OP says, in their example rows 1-3 are added first and then row 4 should be prevented from being inserted. But that needs to relate to rows 1 (and 3, which the OP hasn't highlighted yet) but your function would be selecting row 2. – Damien_The_Unbeliever Sep 20 '17 at 06:48
Yep, I coded it before he edited the question and now I need to attend other business – PacoDePaco Sep 20 '17 at 06:49
@IshtiaqAhmed - is it a concern that this would allow `(4,2,165,1)` to be inserted, meaning that overs 2 & 3 were bowled by the same bowler? – Damien_The_Unbeliever Sep 20 '17 at 07:09
yes. this is the problem. that no bowler can through two consective overs in cricket. but one over can be throughn by two bowlers, if first got injured after throughing one ball of over. than another bowler can through rest 5 balls. I have another table which forbides bowlers to ball same over (already completed) twice. – Ishtiaq Ahmed Sep 20 '17 at 07:16
@IshtiaqAhmed - I don't think you get what I'm saying here - I'm saying that this check/function only checks the *predecessor* row. But since rows can be inserted out of order, surely you *also* need to check that the *successor* row isn't now in violation. This check/function doesn't prevent that situation. – Damien_The_Unbeliever Sep 20 '17 at 07:24
Damien is right, I will modify my answer to reflect the valid logic – PacoDePaco Sep 20 '17 at 07:46

score 1 · Answer 2 · answered Sep 20 '17 at 07:01

Check constraints cannot directly reference other rows data. There are some techniques that try to use UDFs to get around this limitation but they tend to not work well. Especially in this case where I presume the insert of row 4 should also be blocked if it had a bowlerID of 165 since that would mean overs 2&3 shared a bowler.

Instead, we can implement this with a pair of views. I usually put DRI somewhere in the name of views like this to indicate that they're there for Declarative Referential Integrity reasons, not because I intend people to query them.

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)

This final insert generates the error:

Msg 2601, Level 14, State 1, Line 37 Cannot insert duplicate key row in object 'dbo.Bowling_DRI_SuccessiveOvers_Even' with unique index 'UQ_Bowling_DRI_SuccessiveOvers_Even'. The duplicate key value is (1, 150). The statement has been terminated.

Hopefully, you can see the trick I'm employing to make these views check your desired constraint - it's set up so that rows are paired with either their (logical, based on OrderNumber) successor or predecessor based on dividing the OrderNumber by two using integer maths.

We then apply unique constraints on these pairs and including the BowlerID. Only if the same bowler bowls two successive overs will we generate more than one row with the same (OddON/EvenON) and BowlerID values.

score 0 · Answer 3 · answered Sep 20 '17 at 06:52

Maybe this one?

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);

One Bowler can not bowl two consecutive over in cricket

3 Answers3