Monday, June 21, 2010

How to ADD CHECK CONSTRAINTS on existing table?

Adding CHECK constraint to an existing column
The following T-SQL code adds a constraint to an existing column in the table. Option WITH CHECK is used to to apply a check against existing rows. WITH CHECK option is optional in the code because it is defalut check option.

ALTER TABLE TestPK
WITH CHECK --Optional
ADD CONSTRAINT TestPK_CHECK_DoB
CHECK (DoB < DateAdd(yy,-20,GetDate()))
 
If any existing value violates the constraint, we get following error:
Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the CHECK constraint "ConstraintName". The conflict occurred in database "DBName", table "dbo.TableName", column 'ColumnName'.
 
Adding CHECK constraint WITH NOCHECK
The following T-SQL code adds a constraint to an existing column in the table with nocheck option. I assume that the column has a value that violates the constraint. Therefore, WITH NOCHECK is used to prevent the constraint from being validated against existing rows, and to allow for the constraint to be added

ALTER TABLE TestPK WITH NOCHECK
ADD CONSTRAINT TestPK_CHECK_DoB
CHECK (DoB < DateAdd(yy,-20,GetDate()))

2 comments:

  1. This is my first time i visit here and I found so many interesting stuff in your blog especially it's discussion, thank you.
    home

    ReplyDelete