Operations and Constraint Violations Video Lecture | SSC CGL Tier 2 - Study Material, Online Tests, Previous Year

all right let's look at an example of a
question that I was asked the other day
by an African application developer and
this relates to when we get the insert
failure of a violation in the primary
key imagine that we have a badge of
10,000 records in one of those records
is a duplicate primary key what's going
to happen is the entire batch is going
to fail so let's look at this just a
simple example what we're gonna do here
is we're gonna create a table called
kill primary key we're gonna declare an
ID that's a primary key we're gonna
insert into this table 1 then we're
gonna print a bad batch and you'll
notice that in this bad batch there's
already a 1 so we know we're going to be
inserting a duplicate primary key and
then we're gonna print a good valid
batch I should say and we're not going
to be entering any duplicate primary key
so 789 are not a duplicate and then
we're gonna be selecting from that and
we'll see what what happens here is that
of course the entire batch when we
select from this fails of this piece
right here now we know been looking at
it that both 2 & 3 & 4 are valid right
but the problem is is we observe that in
our table we have an ID of 1 7 8 & 9 so
I'm speaking with the application
developer one of the things that I
pointed is that if our application does
not handle the logic of checking if the
primary he exists on some level then
what that means is that if I insert
10,000 records at a time in one of those
records is a duplicate primary key
because T sequel is a set based language
that batch is going to fail so one of
the ways to handle this of course it
depends on how you write it you can do a
one by one insert and that's that's
valid but what you can also do is you
can check if the primary key exists
right and so in your application logic
you can handle it and basically what
you're doing there is you're pre
validating the records in the batch to
ensure that valid records are inserted
and that invalid records are logged you
can also change the type of primary key
to where there's no possible way that
you could ever have a duplicate value
that one is
a lot more obviously a lot more
difficult but it's probably gonna
require some overhead to do but this is
something to note that if I insert let's
say a hundred records and one of those
records is invalid or is I should say
the same there's a violation of a
primary key constraint and what's going
to happen is those 100 records aren't
going to end up there so I have to think
on my application level as to how I'm
going to approach building something
to where if the insert fails on one
particular record I still get the other
ones that are unique so in that case I
would still get the two the three and
the four