How to add a check constraint on a table

Syntax:

ALTER TABLE

   ADD CONSTRAINT CHECK ()

Example:

It is always better to check for existence before adding any object to avoid any runtime errors:

IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name = 'CK_ProductReview_Rating' AND type = 'C')

ALTER TABLE Production.ProductReview

ADD CONSTRAINT CK_ProductReview_Rating CHECK (([Rating]>=(1) AND [Rating]<=(5)))

If you are adding a CHECK constraint on an existing table with data, and the conditions of the constraint are invalid, it would not be able to create the check constraint. However, the error it throws does not give a clear indication of the reason. It seems to indicate that a constraint already exists, so you need to check for the conditions:

Msg 547, Level 16, State 0, Line 2

The ALTER TABLE statement conflicted with the CHECK constraint "CK_ProductReview_Rating". The conflict occurred in database "AdventureWorks", table "Production.ProductReview", column 'Rating'.