约束检查外键的值 [英] constraint check against values of foreign key
问题描述
我有这两个表
表： Guards
- ID int
- 名称varchar
- Rank int
表格：小队
- SquadId
- 领导人
- SquadName
The Leader 列指向 Guard 表中的 ID 列，然后我正在尝试创建一个约束，以检查链接到作为领导者提供的后卫ID的 Rank 列是否为特定值（在本例中为1）
The Leader column points to the ID column in the Guard table and I'm trying to create a constraint that checks if the Rank column linked to the guard id provided as the leader is a specific value (in this case 1)
这可能还是必须使用触发器？
Is this possible or do I have to use a trigger?
推荐答案
添加 CHECK 约束。我将约束包装到函数中，因为您需要检查另一个表的值。
You need to add a CHECK constraint. I'd wrap the constraint into a function since you need to check another table's value.
CREATE FUNCTION CheckLeaderRank
(@LeaderID INTEGER)
RETURNS INTEGER
AS
BEGIN
DECLARE @value INTEGER;
DECLARE @MinimumRank INTEGER = 3;
SET @value = CASE WHEN (SELECT RANK FROM Guards WITH(NOLOCK) WHERE Id = @LeaderID) >= @MinimumRank THEN 1 ELSE 0 END
RETURN @value
END
该功能将检查后卫的排名足够高：确保将 @MinimumRank 设置为适当的值，或者甚至最好从另一个表中获取它。
The function will check if the guard's Rank is high enough : make sure to set @MinimumRank to the proper value or, even better, to fetch it from another table.
现在将约束添加到您的队表中。
Now add the constraint to your Squads table.
ALTER TABLE Squads
ADD CONSTRAINT chk_rank CHECK (dbo.CheckLeaderRank(i) = 1)
这篇关于约束检查外键的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
