CHECK constraint is used to limit the range of the values, that can be entered for a column.
Let's say, we have an integer AGE column, in a table. The AGE in general cannot be less than ZERO and at the same time cannot be greater than 150. But, since AGE is an integer column it can accept negative values and values much greater than 150.
So, to limit the values, that can be added, we can use CHECK constraint. In SQL Server, CHECK constraint can be created graphically, or using a query.
[フレーム]
The following check constraint, limits the age between ZERO and 150.
ALTER TABLE tblPerson
ADD CONSTRAINT CK_tblPerson_Age CHECK (Age > 0 AND Age < 150)
The general formula for adding check constraint in SQL Server:
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.
To drop the CHECK constraint:
ALTER TABLE tblPerson
DROP CONSTRAINT CK_tblPerson_Age
12 comments:
MR. Venkat Thanks for greatest Tutorials form Kingdom of Bahrain =)
Reply DeleteI agree
Reply Delete-jay
California
Thanks, Venkat! Very clear explanation! Love your video!
Reply DeleteU R The Best !
Reply Deletegood videos very helpful for me. I searched a lot but i could not find out the better videos as this
Reply DeleteThis the best For Beginners
Reply Deletecan we check the check constraint Boolean expression for existing column data
Reply Deleteyes, use this syntax sp_help for check constraint
Deletecan we check check constraint for existing column data
Reply DeleteYes. It will checks for existing column data
DeleteHi there, Your videos are really helpful, can you kindly send me a complete file of the slides for the Sql server tutorial. please. i could not download it through the link you provided
Reply DeleteYES YOU CAN. YOU CAN JUST GO TO THE TABLE AND EXPAND THE COLUMN YOU WANT TO CHECK AND SEE IF THE COLUMN HAS KEYS OR CONSTRAINTS.
Reply DeleteIt would be great if you can help share these free resources
[フレーム]