Add table level Contraint that checks for date overlap in Sql Server 2005

Question

I have a table in Sql Server where i have Columns : UserId, RoleId, FromDate and Todate. I want to write a contraint that check same RoleId and UserId are not present for the same date.

Thanks In Advance....

Answer 1

If you don't have time portion or the time portion is the same in all records you can use UNIQUE constraint:

ALTER TABLE yourSchema.yourTable 
ADD CONSTRAINT uniqueConstraint1 UNIQUE (RoleId, UserId, FromDate);

This way combination of date, RoleId and UserId can occur only once in table, other attempts to insert the same combination will fail.

Note that this will work if your date field has values for time portion that are the same in every record (for instance 0) or the data type of the field is DATE (which eliminates the time portion).

If your date field has time portion that varies among records, try one of this approaches:

A) Add computed column of definition

ALTER TABLE yourSchema.yourTable 
ADD constraintCheckDate AS CAST(FromDate AS DATE)

and add UNIQUE constraint of definition

ALTER TABLE yourSchema.yourTable 
ADD CONSTRAINT uniqueConstraint1 UNIQUE (RoleId, UserId, constraintCheckDate)

B) Use trigger to validate data before inserting it, data will be entered only if it doesn't already exist:

CREATE TRIGGER trig1 ON yourSchema.yourTable
INSTEAD OF INSERT
AS
BEGIN

    IF NOT EXISTS
    (
        SELECT  *
        FROM    yourSchema.yourTable t
        JOIN    inserted i ON 
                CAST(t.FromDate AS DATE) = CAST(i.FromDate AS DATE) 
        AND     t.RoleId =  i.RoleId 
        AND     t.UserId =  i.UserId
    )
        INSERT  yourTable(RoleId, UserId, FromDate, ToDate)
        SELECT  RoleId, UserId, FromDate, ToDate
        FROM    inserted
    ELSE
        RAISERROR('Error', 16, 0)

END
GO