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

welcome to presume technologies I am
venket this is part 4 of signal server
in this session will understand how to
add a default constraint in part 3 of
this video series we have seen how to
create tables specifically we have
created TBL person and TBL gender tables
and we have also seen how to add a
primary key and a foreign key constraint
if you remember in TBL person in TBL
person table we mark this ID column as a
primary key and gender IDs a foreign key
and along the same lines in TBL gender
table we mark the ID column as its
primary key now if you look at the
sample data that we have caught in these
two tables okay this is how we have the
sample data and if you look at the
columns in TBL person table you know we
have got four columns here
the first three columns ID name and
email they have a not null constraint
meaning whenever you insert a row into
this TBL person table you have to
mandate release apply the values for ID
name and email columns but whereas if
you look gender ID it's it's not
mandatory I mean you don't have a not
null constraint you're saying that this
column does allow us it means when you
insert some of the records for some of
the records you can have gender IDs na
but whereas ID name and email cannot be
now okay so let's insert a record into
this TBL person table and obviously to
insert a row into this table you can
either do that graphically or using an
insert query let's do it using an insert
query so insert into the table name so I
want to insert the data into this TBL
person table now you can also specify
the column list let's say I want to
insert the values for ID name email and
gender ID now gender ID since gender ID
allows now you may or may not supply a
value for that let's say I don't want to
supply a value for that column and then
I will say values so into this table for
these columns we have to supply the
values so let's say for example the ID
of the person is going to be
seven and the name of the person may be
rich and maybe their email are at our
comm okay if you look at this row that
we are going to insert we haven't
supplied gender ID and since it's
nullable column it means this column
allows now so when I execute this query
and when we select the rows from this
table you should see that rich record
has got null there okay now let us say
when somebody doesn't supply a value and
if you look at the TBL gender table for
example here okay if you don't know the
you know gender of a person you know I
would recommend you know I want the data
to be inserted in such a way that they
will get you know by default three okay
so it's you know instead of storing null
I want to store a default value okay if
we don't know the gender of a person
rather than allowing nulls there I want
to insert number three so that anybody
when they look at this data okay rich
gender ID is three so his gender is
unknown okay
so I want to have defaults so how do we
do that
using a default constraint but if you
don't have a default constraint and if
you don't supply a value for a nullable
column then by default it will get
another value there but on the other
hand you can control that using a
default constraint so in this case let's
say anytime we insert a record into TBL
person table and I don't supply a value
for gender ID column I want the default
number three to be inserted okay let's
see how to do that so to add a default
constraint okay here is the syntax
altering an existing column to add a
default constraint so obviously we are
changing the definition of the table so
we have to say all the table in this
case TBL person and what do we want to
do we want to add a constraint add
constraint and give a meaningful name to
that constraint and default and then
whatever is the value in our case it's
going to be three for gender ID column
within this TBL person table so let's do
that
so to add the default constraint alter
table and which is the table that we
want to alter TBL person table so alter
table TBL person now how do we want to
add alter this table we want to add a
constraint so add constraint and give
the constraint a meaningful name since
this is going to be a default constraint
I would give an abbreviated form DF and
then we are adding this constraint to
this table so include the table name as
well so TBL person and for which column
we are adding that we are adding it for
gender ID column so specify the gender
ID column as well so just by looking at
the name the constraint name you can say
okay this constraint is for TBL person
table and it's a default constraint for
which column gender ID so give a
meaningful name to your constraint and
then what type of constraint is this
this is a default constraint and you can
specify the value in our case the value
is going to be 3 for gender ID column so
3 for gender ID okay so when we execute
this query look at that if you look at
the TBL person table now if you expand
constraints you don't have anything
there but once we can create this
constraint we should get that there so
let's execute this query so command
completed successfully now if I go ahead
and refresh the constraints folder of
TBL person we should see the default
constraint created there now let's
insert if you look at the records in TBL
person table now we've got 7 records and
rich has got now now let us say I want
to insert somebody here like Mike and
his ID is 8 and let us say my cat or com
and if you look at this query we are
still not supplying the gender of this
person Mike's gender now but we have
added a constraint so what should happen
now if you do not supply a value for
gender ID column then this constraint
you know will insert default value which
is three for the gender ID for Mike so
let's see how you know let's execute and
see what happens so when I execute this
one row has been affected and if you
select all rows look at this for Mike
didn't supply a value so it took the
default but on the other hand if I
supply a value let us say I'm going to
enter somebody you know I'm going to
pass the gender ID as well because I
know the gender ID of the person and
let's say I'm going to say this time
Sarah let's say her email ids s at our
comm and I want to supply gender ID for
her I know she is a female so I'm going
to pass the value for that so when you
pass the value are specifically saying
Sarah's gender ID is 1 so so now the
default will not be kicking in so this
value will be stored for Sarah so when I
execute that and when you check TBL
person you should see her gender ID
which we have supplied and finally let's
do this let's say I'm going to insert
maybe John's record whose ID is going to
be 10 we already have John so let's say
Johnny for example let's say his email
ID is J at our comm now I am passing in
the gender ID but I am specifically
saying I don't know his gender so I'm
just going to say now so you are
specifically passing null value which
means we'll the default be assigned or
will it not be a sign now let's execute
and see what happens
so I executed that query Johnny is the
latest record that we have inserted and
if you look at the result for Johnny
it didn't take the default why because
if you supply a value even if it is null
you know the default will not be
executed
instead the value that you have supplied
will be you know insert it into the
table but in the other case if you miss
the value or if you don't supply a value
that's when default will be considered
okay so we have seen how to alter an
existing column to add a default
constraints it's also possible that when
you are creating a table or if you want
to add a new column to a table for an
existing table and you want to have a
default for that column it is still
possible adding a new column with the
default value to an existing table and
this is the syntax of the table and the
table for which you want to add this new
column you add that columns basically
the data type whether it allows nulls or
not and then constraint give it a
meaningful name and the default value
for that
okay and how to drop a constraint you
know you can use alter table command
drop constraint and the constraint name
okay so if we want to drop this
constraint we can use drop constraint
statement but before that we have to use
alter table so alter table TBL person
drop constraint and the name of the
constraint so in our case the name of
the constraint is this one so when I
plus f5 the constraint will be dropped
so if we refresh this constraints folder
on the left hand side that should be
gone so it's possible to assign a
default to a column using the default
constraint on the 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