0

A business rule required my table to only registrater certain dob from bithDate column to be inserted or allow. I created the check constraint but i kind of having logical error i not able to spot out. pls advise

ALTER TABLE Personalinfo
ADD CONSTRAINT chk_ValidDOB   CHECK  
(
CASE
    WHEN (convert(int,DATEDIFF(M, BirthDate, getdate())/12) BETWEEN 30 AND 33) THEN 1
    ELSE BirthDate
END = 1
);
Error message:  `Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "chk_ValidDOB"`
  • Does the table already contains data ? If yes , one of the existing record might conflict with the new constraint that tries to be put in place. Another possibility is that the format of the column chk_validDOB does not accept one of the two types of values you're trying to input: either a birthdate or the number 1 ? – P. O. Jan 9 '15 at 12:57
  • 1
    @JoBedard, Yes, i just deleted the records and ran the it. work run sucessfully – lern Jan 9 '15 at 13:09
3

Most likely the issue is that the ELSE is returning BirthDate instead of 0.

If there is already data in the table that conflicts, then add the WITH NOCHECK clause if you want to keep that data, else first delete any rows that conflict with the new constraint.

Also, best to use MONTH instead of M for the date part, for easier readability.

Not the answer you're looking for? Browse other questions tagged or ask your own question.