Wednesday, July 1, 2009

Add a check constraint for your date in Sql Server

 

Generally most of the people will have the requirement to add the dates to the database.  Most of the cases they will have two related dates, as FromDate and ToDate or Begin and End.  However most of the people wont add any constraints to their database even they knows that the First Date is always less than the Second Date. 

CREATE TABLE CheckDates
(
    StartDate DATETIME,
    EndDate DATETIME,

    CONSTRAINT
        cmp_Dates CHECK (StartDate<EndDate)
)

Here I am adding a table level check constraint.  Now the database will verify when ever the user tries to add the Start Date > End Date with out writing any triggers or additionally writing logic in stored procedures or front end application every time we insert.

However, you will face problem if you try to insert the column level constraint by referring to another column in the same table.

CREATE TABLE CheckDates
(
    StartDate DATETIME,
    EndDate DATETIME CONSTRAINT cmp_Dates CHECK (StartDate<EndDate)
)

Writing the script as above will give you the error message “Column CHECK constraint for column 'EndDate' references another column, table 'CheckDates'.”.  Notice the comma in the first script which separates the column after EndDate DATETIME.


kick it on DotNetKicks.com

3 comments: