Adding a check constraint

“Check constraint is used to limit the range of the values that can be entered in a column”

Lets see an example where age cannot be negative , If you want to see the data type of column (in our example its integer), you can find the datatype of a table by selecting the table name and then press alt + F1, so if the user my mistake might enter a negative value in this column with the database will happily accept,

To avoid such cases is important to provide a range for example 0 to maybe 50 the age diff between 0 to 50 would only be allowed to be enter in this column  in case if a user enters value anything between 0 to  50  the record will get inserted , and anything other than this, I want to throw an error so how do we add a check constraint,

so like any other constraint it can be added using the

  1. graphical window
  2. query

So since its a constraint so within the table folder you will find the constraints so right click and select new constraint, here you will get a graphical window where we can basically add a constraint information so remember the constraint is just a Boolean expression so it will return either true or false for,so we need to add the expression within the window ,

Please have a look at the screenshot so if the expression returns true the value would be allowed to insert in the column and if it returns false it won’t be allowed and you will get an error message so have a look at the screenshot.

“Always remember to give constraint a meaningful name because it’s so important for you to understand that with the name it should give you an idea that it is a check constraint preferably CK preface is used along with the name of the table and then the name of the column”

Always remember to give constraint a meaningful name because it’s so important for you to understand that with the name it should give you an idea that it is a check constraint preferably CK preface is used along with the name of the table and then the name of the column

Now suppose we are creating a constraint on a table in which the coloum already has a negative value then you want be able to create its constraint meaning by as there is already negative value,constraint will failed and it will not get created so you need to turn it off with the option in the window (table designer, the first option,check existing data on creation) or you can delete the bad data  so once it’s your constraint is created try to insert a record and check with yourself if the record has a negative age you will get an error message and support your record has a positive age between 0 to 50 the record will happily get inserted

 

Suppose we Pass null value to the column so how will the Boolean expression behaves,As null Is an unknown value to the Boolean expression ,it will pass the Boolean expression and will return true allowing null to be inserted in the age column

check

Let see a complete example. First, we will create a table that has primary key. Next, we will drop the primary key successfully using the correct syntax of SQL Server.

 

 

Hope this post will be helpful.

   

Leave a Reply

Your email address will not be published.