Adding a check constraint - Part 6 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

Oh welcome to premium technologies I am
venket this is part 6 of sequel server
in this session we'll understand what
check constraint is and when do we use
it now
check constraint is used to limit the
range of the values that can be entered
for a specific column let's understand
this with an example I have this table
called TBL person which has got this age
column in reality age of a person cannot
be negative and along the same lines
it's impractical for a human being to be
alive after 150 years so the age column
you know should be taking values between
0 and 115 okay but if you look at the
data type of this age column it's an
integer by the way if you want to find
out the you know did the data type of
any column in sequence over there are
several ways okay one of the easiest
ways is to expand the tables folder
within the database and you go to that
specific table and expand the columns
folder and you can see that the age
column is of type integer and it allows
nulls this is one way another quick way
to find out the information about the
columns that are present inside the
table is highlight the table and press
alt f1 so that should bring up the
columns and their respective data types
as well so if you look at the age data
type it is of type integer
so since age is integer datatype
it can allow any allow you know valid
integer for example let's try to insert
a rain row into the stable so insert
into TBL person let's say
I want to insert ID of four and the name
of the person maybe Sarah and email a
set s comm and maybe gender IDs and she
is a female let's say two and age now
look at this since age is of type
integer I can enter something like minus
nine hundred and seventy okay and if I
try to execute this query the rule will
be happily accepted into the table so
for the age column I'm able to enter an
invalid age okay so is it possible to
prevent numbers like this being entered
see all I want to allow with this column
is a number between zero and fifty if
anybody tries to enter a number outside
of that range I want to throw an error
is that possible absolutely using the
check constraint of sequel so it checks
the values while you enter them and then
if it falls outside that range it throws
in error so how do we add a check
constraint like any other constraint
check constraint you know can be added
to sequel server in two ways one is
graphically using the designer the other
one is obviously using a query now first
let's see how to do that graphically
using the designer so since it's a
constraint you can go on to the
constraints folder in sequence of our
management studio by the way you have to
be in the table under the table folder
so I want to do it for TBL person table
so expand the TBL person table there you
should see a constraints folder right
click on that and select new constraint
so this should bring up a check
constraints window where we can
basically add the constraint information
now remember a constraint is just a
boolean expression which returns a true
or a false okay now what's our aim our
aim is that the age column shouldn't be
allowing a number less than 0 or greater
than 150 okay so how do we do that you
add that expression here and whatever
expression that you enter here should be
a boolean expression it should written a
true or a false if it returns true then
the value will be allowed to be entered
if it returns false
then it doesn't allow the value okay so
what we want to do is the age should be
less than I mean greater than zero and
age should be less than 150 so anytime
anybody is supplying a value for the age
column it has to be within that range it
has to be greater than zero less than
150 click OK and another thing that we
have to do is give it a meaningful name
okay just by looking at the name you
should be able to tell ok this is a
check constraint on this table for this
column okay and the common convention is
that for a check constraint we use ck
prefix
ck underscore the name of the table on
which we want to create that constraint
and for the column to which we want to
create that constraint in this case it's
the age column and then you have another
option here check existing data on
creation okay
so what does this mean if there is
already invalid data which doesn't fall
into this criteria
you know this constraint will fail you
know it doesn't you know it basically
will will not succeed in getting created
okay so it checks the existing data so
you can turn this off or alternatively
you can delete that bad data okay let's
close that but then when I try to save
it that's when we get an error
so unable to add this constraint okay so
what we will do first is we will go
ahead and delete that invalid row so
which is that invalid row they record
with ID 4 so let's delete that from that
table so they lead from TBL person where
ID is equal to 4 so we deleted that
invalid row now all of the rows are
valid for us so now let's try to save
this and it gets saved ok now let's try
to execute this query and see what's
going to happen when I try to execute
this query look at that you know the
check constraint is kicking in it's
telling ok the insert statement
conflicted with the check constraint see
Candice code tibial personal score 8 the
constraint that we have just created
on the other hand if I supply a valid
age maybe ten years and if I try to
execute this query it happily gets
inserted so if we select all the rows
from the table you should see that sarah
has got the age of ten right now okay
now let's do one more test let's say we
are inserting another set of at ID five
and let's say cure instead of passing a
value for age now remember each column
is a nullable column which means you
don't have to supply a value for that
you can just pass now okay so if I pass
now what is going to happen to that
boolean expression that we have written
okay now if you remember the boolean
expression is age should be greater than
zero and at the same time it should be
less than 150 now is null greater than 0
or less than 150 you know this is an
unknown value you know the boolean
expression returns an unknown result in
which case the constraint passes and
allows null to be inserted so if we run
this this query will be executed why
because the boolean expression doesn't
return false it just returns unknown
because you cannot compare null with
anything so it doesn't return false
that's why it allows nulls to be
interest inserted so you can add nulls
and you can add any valid values that
fall within 0 and 150 okay so how do we
drop a constraint now you know that we
have added this constraint using the
designer so obviously if we expand this
folder you should see that constraint
there okay so we have this constraint
now how do we delete this constraint or
drop this constraint again you can do
that graphically or you can use a
command to do it graphically we just
right click on that and delete and if
you want to use a command a sequel
server query it is just like dropping
any other constraint so obviously if you
want to drop a constraint it's like you
are altering the table because the
constraint is for the table so we have
to tell we are going to alter the table
so all the table which table is that TBL
person and how are you going to alter
this table I'm going to drop one of the
existing constraints in this case the
check constraint that we have created
refresh this and we should see that
check constrain there okay so I want to
drop this constraint so let's copy that
constraint name so all the table TBL
person drop what do I want to drop I
want to drop a constraint and then
specify the name of the constraint so
you press f5 command completed
successfully let's refresh the folder
and the constraint shouldn't be there
anymore okay now let's go ahead and see
how to add a constraint using sequel
query now if you look at the
presentation it's very simple the
general formula for adding a check
constraint not only check constraint any
constraint for that matter okay so all
the table table name add the constraint
the name of the constraint so here we
are adding a check constraint so we use
this check keyword and then a boolean
expression if you remember while we are
doing it graphically we did in in that
boolean expression window but here we
have to do that within this parentheses
okay so let's see how to do that so all
the table table name and what we want to
do we want to add a constraint so add
constraint and then you have to give the
constraint a meaningful name
so check constraints by default will
have ACK prefix underscore the table for
which we are adding this constraint in
this case it's going to be TBL person
and for which column it's going to be
for each column and then what type of
constraint are we going to add check
constraint and within the parentheses we
have to specify the boolean expression
what should be our boolean expression we
don't want to allow values for age you
know less than 0 or greater than 150
okay so age should be greater than 0 and
age should be less than 150 that's it so
I press f5 command completed
successfully refresh the constraints
folder and you should see the constraint
once again and if we try to add any
value something like maybe 900
50 years plus f5 it should give an error
alright
so that's how we do that and remember if
this boolean expression returns true the
check constraint allows the value
otherwise it doesn't since ages
inaudible column it's possible to pass a
null value for this column when we are
inserting a row so when you pass a null
for this age column the boolean
expression evaluates to unknown and
allows that null value so it's possible
for you to insert Mouse because why the
boolean expression that's involved there
returns an unknown result okay so when
will a check constraint prevent a value
from being entered whenever the boolean
expression returns false okay and to
drop a constraint we have seen we use
this alter table and drop constraint the
name of the constraint on this slide you
can find resources for asp.net and C
sharp interview questions that's it for
today thank you for listening have a
great day