Get Tesla Stock worth ₹1000. Enter code UMU2BOEITSL while signing up 🥳
Click to claim today 👉🏼
On Mobile https://indmoney.onelink.me/rmhc/pszcd5o0
On Web https://www.indmoney.com

Oct 15, 2011

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