SQL Server 2012: Check Constraint with Default Values

Question

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 ?

Why would you want to put Dec 31 9999 as termination date? If you don't know the termination date yet, it should arguably be NULL instead of some token value... — Aaron Bertrand, Feb 01 '14 at 20:52

score 7 · Accepted Answer · edited Jul 29 '20 at 18:30

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.

after using the date conversion it is working as expected, thanks for pointing out(2 of them) EffectiveDate <= TerminationDate comparison, i actually meant it to be like this EffectiveDate >= CONVERT(DATE, GETDATE()) AND EffectiveDate < TerminationDate. — NSS, Feb 01 '14 at 21:14
Is it really necessary to repeat the same test twice (`EffectiveDate <= TerminationDate` <=> `TerminationDate >= EffectiveDate`)? I thought a check constraint was working against a row, not against a column, and so it should be enough to implement the test I'm talking about in just one of these two constraints, because it would be performed regardless of which date column you updated. — Andriy M, Feb 02 '14 at 12:52
@AndriyM yes, I think you're right, you would only need one constraint. I was merely correcting the logic that prevented either constraint from evaluating as intended. — Aaron Bertrand, Feb 02 '14 at 13:28
@AndriyM Also updated to a single constraint per your suggestion, thanks. — Aaron Bertrand, Feb 02 '14 at 21:28

SQL Server 2012: Check Constraint with Default Values

1 Answers1