Conditional Unique Constraint in SQL Server

This post will explain you the way, how conditional unique constraint can be applied in SQL Server.

Example
Suppose you have a table that holds Student Id, Test Id, Pass Status (yes/no) and date.
Now, you want to make sure no student can pass a test twice, means you can have multiple entries for a student and a test with 'no' value in passed column, but only 1 value with  'yes' in passed.

Step 1: Let's first create a Test Table

CREATE TABLE tblTest (
    studentid INT, testid INT, passed VARCHAR(3), date DATE
)

Step 2: Now lets create a function that will return the count of pass records against a Test for a Student

CREATE FUNCTION CheckCount(
    @studentid INT, @testid INT)
RETURNS INT
AS
BEGIN
    DECLARE @ret INT;
    SELECT @ret = COUNT (1) FROM tblTest WHERE studentid = @studentid AND testid = @testid AND passed = 'yes';
    RETURN @ret;
END;

Step 3: Now lets add a constraint to the table Test using the CheckCount function created above to ensure that if count of pass records against a Test for a students exceeds 1, the constraint should not allow to insert  the

ALTER TABLE tblTest
ADD CONSTRAINT ChkConstratint
CHECK (NOT (dbo.CheckCount(studentid,testid) > 1));

Step 4: Now lets populate the data in the table to test the constraint

INSERT INTO tblTest VALUES (1, 1, 'no', getdate()); --success
INSERT INTO tblTest VALUES (1, 1, 'no', getdate()); --success
INSERT INTO tblTest VALUES (1, 1, 'yes', getdate()); --success
INSERT INTO tblTest VALUES (1, 1, 'yes', getdate()); --fail (duplicate studentid and testid with passed = 'yes' 

OUTPUT