How to add check constraint in SQL Server?

Question

I have a table with column BirthDate(date, null).

I tried to add check constraint in designer BirthDate >= 'January 1, 1980' but got error:

Unable to add constraint 'CK_MyTable'.
The ALTER TABLE statement conflicted with the CHECK constraint "CK_MyTable". The conflict occurred in database "MyDb", table "dbo.MyTable", column 'BirthDate'.

When I tried to add the constraint with this T-SQL script:

ALTER TABLE dbo.MyTable 
ADD CONSTRAINT BirthDate CHECK (BirthDate >= 'January 1, 1900');

I got the same error.

What's wrong?

Do you have any existing rows in the table that break that constraint? If so; you have to Update or Delete them and try again. — tobypls
– tobypls, Commented Aug 26, 2015 at 8:04
@tobypls yeah i have a rows in table. How can i modifity script to add constratint in this case? — Kliver Max
– Kliver Max, Commented Aug 26, 2015 at 8:07

tobypls · Accepted Answer · 2015-08-26 08:13:55Z

2

You can't have existing rows in the table that violate the constraint. You can solve this by either

Delete them

DELETE FROM dbo.MyTable WHERE BirthDate < '1980-01-01'

OR

Update the existing rows

UPDATE dbo.MyTable SET BirthDate = '1980-01-01'/*Your new date*/ 
WHERE BirthDate < '1980-01-01'

answered Aug 26, 2015 at 8:13

tobypls

8591 gold badge9 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:43:55Z

0

Try like this

ALTER TABLE dbo.MyTable 
ADD CONSTRAINT BirthDate WITH NOCHECK CHECK (BirthDate >= 'January 1, 1900');

We do not recommend doing this, except in rare cases.

Unsure of WITH CHECK clause when adding a constraint

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Aug 26, 2015 at 8:15

StackUser

5,3983 gold badges29 silver badges48 bronze badges

Collectives™ on Stack Overflow

How to add check constraint in SQL Server?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related