CODEWITHSUNDEEP
sqlsql-serverconstraints
Anders M.
Anders M.

Reputation: 199

constraint check against values of foreign key

I have these two tables

Table: Guards

Table: Squads

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?

Upvotes: 3

Views: 2217

Answers (1)

SolarBear
SolarBear

Reputation: 4619

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)

Upvotes: 4

Related Questions