Check constraint in sql server ~ MiTechDev.com

Mitechdev.com is a programming blog which focused on Web Programming and development

Wednesday, 8 March 2017

Check constraint in sql server

21:38:00 Posted by raviteja swayampu , No comments
CHECK constraint is used to limit the range of values that can be entered in a column.If the value is not in the range sql server do not allow to insert the values into table by throwing an error.

Lets say we have an integer AGE column in TUser table.An user age can not be less than zero and can not more than 150.So,the column do not allow negative values and number more than 150.To limit the values we use CHECK constraint on the column.
We can create CHECK constraint graphically or using query.
Syntax for CHECK constraint :
ALTER TABLE { TABLE_NAME }
ADD CONSTRAINT { CONSTRAINT_NAME } CHECK ( BOOLEAN_EXPRESSION )
If the BOOLEAN_EXPRESSION returns true, then the CHECK constraint allows the value, otherwise it doesn't. Since, AGE is a nullable column, it's possible to pass null for this column, when inserting a row. When you pass NULL for the AGE column, the boolean expression evaluates to
UNKNOWN, and allows the value.


The following CHECK constraint limits the age between 0 to 100.
ALTER TABLE TUser
ADD CONSTRAINT Check_TUser_1 CHECK(Age>0 AND Age<100)
Note: Here Check_TUser_1 is name of check constraint.
Example:When i try insert a Age value 105 into TUser table sql server displays a message like below.
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__TUser__Age__239E4DCF". The conflict occurred in database "TestDB", table "dbo.TUser", column 'Age'.
The statement has been terminated.

To drop CHECK constraint:
Use below query to drop CHECK constraint from column..
ALTER TABLE TUser
DROP CONSTRAINT CK_TUser_Age

0 comments:

Post a Comment