constraint check against values of foreign key

Question

I have these two tables

Table: Guards

• ID int
• Name varchar
• Rank int

Table: Squads

• SquadId
• Leader
• SquadName

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?

Answer 1

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

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)