Unique key constraint - Part 9 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to premium technologies I
am venket this is part 9 of signal
server in this session we'll understand
what a unique key constraint is when do
we use unique key constraint or primary
key constraint and the differences
between unique and primary key
constraints now we use unique key
constraint to enforce uniqueness of a
column that is the column shouldn't
allow any duplicate values if you
remember in this video series we have
already discussed a primary key is also
used to enforce uniqueness of a column
so when do we choose one over the other
now we know that a table cannot have
more than one primary key now let's say
in my table I have person ID which is
already a primary key and I also have
email ID Social Security number passport
number columns and I want to maintain
uniqueness of each of these columns okay
so is is it possible to do that using
primary keys no because why a table can
only have one primary key so that's when
you make use of this unique key
constraint okay a table can have more
than one unique key constraint so this
is one of the primary differences
between a primary key constraint and a
unique key constraint and this is one of
the very common interview question
that's asked these days so if you look
at this a table can have only one
primary key but more than one unique key
so when do we choose unique key
constraint or primary key constraint if
there are multiple columns in a table
where you want to maintain uniqueness
then we go for unique key constraint
okay another difference between primary
and unique key constraints is that
primary key doesn't allow even one now
where as unique key allows one and only
one now so these are the two differences
between primary key and unique key
constraints now to create a unique key
constraint again there are two ways one
using the designer of sequel server
management studio and obviously the
other one is to use the query now first
let's see how to create a unique key
constraint using the designer so we
already have this TBL person table and
this table has got four columns ID name
email gender ID and
age ID column is already the primary key
column so if I have one column as
primary key it's not practically
possible to mark another column as
primary key okay so that's when I use a
unique key constraint now I want to
insure the email column is always unique
so how do I do that by creating a unique
key constraint so how do we create a
unique key constraint right click on the
table for which you want to create a
unique key constraint select design and
in the table designer window right-click
any on any of the columns and select
indexes slash keys which should bring up
select primary or unique key index
window so look at this here you can
create primary keys as well as unique
keys or indexes when spoken about
indexes yet we'll be talking about them
in detail in a later session okay so we
already have a primary key on this ID
column so that's that key now let's add
another key for a unique key constraint
okay since this is a unique key usually
the naming convention is u-q and the
name of the person on which you are
creating that unique key constraint and
the column now in our case the column is
email okay so you can select your
columns here click on the ellipsis
button and select your column email
column so I want to maintain the
uniqueness of email column and you can
specify the sort order we'll talk about
this sort order when we talk about
indexes and it's unique yes I want this
to be unique and this is not an index I
want to create a unique key close-set
save this table okay now if we save this
table obviously we end up creating
unique key index let's not do that let's
go and try to create that using a query
so how do we create a unique key
constraint using a query now just like
adding any other constraint you know you
add the unique key constraint in the
same way obviously when you're adding
unique key constraint you are altering
the definition of the table so you have
to use alter table statement to come so
alter table TBL person and how do we
want to alter the table by adding a
unique key constraint so ad what do we
want to add we want to add a constraint
and give the constraint a meaningful
name so you queue for unique key
constraint underscore on which table are
you creating this constraint on TBL
person table and on which column email
column okay so we are adding a
constraint with this name and obviously
what type of constraint are you adding a
unique key constraint so unique and for
which column are you creating this email
so that's it so execute that now if you
refresh the keys folder expand this you
should see UQ TBL person email now let's
try to insert a row into this table and
see what's going to happen so since this
table has got four columns we have to
supply the values for these four columns
so insert into TBL person values value
for ID let's say one name let's say ABC
email this what is a dot a.com a at
a.com and gender ID let's say one and
age let's 20 now let's insert this row
okay so if you look at this table we've
got 8 a comm now let's try to insert
maybe another person whose name is XYZ
and by mistake or he or he is
intentionally entering this a at a comm
look at this and remember we already
have a unique key constraint on this
column so if I try to enter this query I
mean execute this query we should get a
unique key constraint violation error
so let's see that when I press f5 look
at this violation of the unique key
constraint and look at the name of the
constraint that we have created UQ TBL
person underscore email and it clearly
says cannot insert duplicate key in
object TBL person all right so how do we
drop this constraint now like just like
how we drop other constraints we drop
unique constraint in the same way
obviously there are two ways to do that
one is you can go to the constraint
right click on that and you have an
option to delete that or you can do that
using a query so how do we do that using
a query drop constraint so we want to
drop a constraint and the name of the
constraint what's the name of the
constraint UQ TBL person underscore
email so when I press f5 Oh obviously we
have to use the alter table command so
alter table which table TBL person and
how do we want to alter that by dropping
the constraint so press f5 so the
command the constraint is drop refresh
the folder and you should see that's
gone and if I try to execute this now I
will be able to insert a duplicate value
for email address look at this I have
this duplicate 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