CODEWITHSUNDEEP
sql-server
Kliver Max
Kliver Max

Reputation: 5299

How to add check constraint in SQL Server?

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?

Upvotes: 0

Views: 1123

Answers (2)

StackUser
StackUser

Reputation: 5398

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

Upvotes: 0

tobypls
tobypls

Reputation: 849

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

  1. Delete them

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

    OR

  2. Update the existing rows

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

Upvotes: 2

Related Questions