SQL Server 2012: Check Constraint with Default Values

check-constraints, sql-server-2012

Solution

The real problem here is that you are inserting a datetime into a date column, then checking that a date column >= datetime. So if you insert a row right now, you are checking:

2014-02-01 >= 2014-02-01 18:57

This will never be true except in the rare case where you might insert a row at exactly midnight. What you want I think is:

EffectiveDate DATE NOT NULL DEFAULT GETDATE(),    
  CONSTRAINT chk_EffectiveDate CHECK 
    (EffectiveDate >= CONVERT(DATE, GETDATE()) 
     AND EffectiveDate <= TerminationDate),
TerminationDate DATE NOT NULL DEFAULT '99991231',
  CONSTRAINT chk_TerminationDate CHECK
    (TerminationDate > CONVERT(DATE, GETDATE()) 
     AND TerminationDate >= EffectiveDate),

There is still a bit of a conflict here though, as part of the constraint implies effective date and termination date could both be the same day, but the other part of the constraint prevents it. And as Andriy stated in his comment, this could be simplified to a single constraint. Assuming you didn't mean to allow the effective date and the termination date to actually fall on the same day, this could be achieved with a single, two-column constraint:

EffectiveDate DATE NOT NULL DEFAULT GETDATE(),    
TerminationDate DATE NOT NULL DEFAULT '99991231',
  CONSTRAINT chk_Dates CHECK 
    (EffectiveDate >= CONVERT(DATE, GETDATE()) 
     AND TerminationDate > EffectiveDate)

Though as I mention in my comment above I think token values like 12/31/9999 are silly and are not a proper alternative to NULL. You should also be very careful about using regional, ambiguous formats like `mm/dd/yyyy`.

Problem

I have the following Column ``` EffectiveDate Date NOT NULL default getDate(), CONSTRAINT chk_EffectiveDate CHECK (EffectiveDate >= getDate() AND EffectiveDate <= TerminationDate ) , TerminationDate Date NOT NULL default '12/31/9999', CONSTRAINT chk_TerminationDate CHECK (TerminationDate > getDate() AND TerminationDate >= EffectiveDate), ``` The problem i am running into is that if i don't enter values in those column it is throwing checkconstraint error, it is not accepting the default values Is what i am trying to achieve possible ?

Original source