Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Check constraint in SQL Server - Part 6



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:

  1. MR. Venkat Thanks for greatest Tutorials form Kingdom of Bahrain =)

    ReplyDelete
  2. Thanks, Venkat! Very clear explanation! Love your video!

    ReplyDelete
  3. good videos very helpful for me. I searched a lot but i could not find out the better videos as this

    ReplyDelete
  4. can we check the check constraint Boolean expression for existing column data

    ReplyDelete
    Replies
    1. yes, use this syntax sp_help for check constraint

      Delete
  5. can we check check constraint for existing column data

    ReplyDelete
    Replies
    1. Yes. It will checks for existing column data

      Delete
  6. Hi 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

    ReplyDelete
  7. YES 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.

    ReplyDelete

It would be great if you can help share these free resources