一个保龄球不能连续两次在板球碗
我正在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)
你需要从给定InningID返回最后BowlerID功能:
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)
问题是,行是乱序添加的。正如OP所说,在他们的例子中,第1〜3行被首先添加,然后第4行应该被阻止被插入。但是,这需要涉及行1(和3,OP尚未突出显示),但您的功能将选择第2行。 –
是的,我编码之前,他编辑的问题,现在我需要参加其他业务 – PacoDePaco
@IshtiaqAhmed--这是否会让'(4,2,165,1)'被插入,这意味着2和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);
检查约束不能直接引用其它行数据。有一些技术尝试使用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)
这最后的插入产生错误:
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.
希望,你可以看到我使用,使这些视图查询您所需的约束的伎俩 - 它的成立,使行与配对(逻辑的,基于OrderNumber
)的继任者或者前任者基于用整数数学将OrderNumber
除以2。
然后,我们对这些配对应用唯一约束并包括BowlerID
。只有当相同的投球手连续两次接球时,我们才会生成多于一行的值(OddON
/EvenON
)和BowlerID
值。
你需要把它放到一个函数中 - 函数应该返回表中的最后一个bowler id,并在约束中检查它是否与你想插入的bowler id不同。 – PacoDePaco
哪个函数?你能解释一下吗?因为我是Sql中的新成员,所以我不太了解 –
请再次阅读我的表格。数据库中不需要连续添加Overs。第二行OverNumber是4 –