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

    Choose :
  • OR
  • To comment
No comments:
Write Comments

Probabilmente come studente universitario ti troverai a scrivere una o più tesi per il tuo analogo esame di fine carriera. Ma di cosa si tratta in particolare? Scopriamo quali sono i pro dei tool per riscrivere testo che troverai online. Per strumento di parafrasi si intende un tool online che si occupa di parafrasare in maniera immediata frasi oppure interi paragrafi. Questa attività si svolgerà interamente su internet e non lascerà alcuna traccia di plagio