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"`

closed as off-topic by Paul White, RolandoMySQLDBA, RLF, Max Vernon, Philᵀᴹ Jan 15 '15 at 17:42

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, RolandoMySQLDBA, RLF, Max Vernon, Philᵀᴹ
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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.