I will call you back again to another video lecture in MySQL.
And in this video lecture we go ahead and look at the check constraint.
So the check constraint is like a condition.
So it's a condition that you give in your database.
So let me say here I've dropped the table known as the employee table because I want to do something
important I want us to recreate this table.
But this time around we're going to give a condition that only the employees who have the GPA of 3.5
from their schools will be employed.
So only students who have up to 3.5 and above cGPA are going to be employed in that company.
And that is what I'm going to consider.
Now let's go ahead and put this table back.
So this is the table we have previously.
And now if I go ahead and create this table we are going to have a table known as employees.
Now let me go ahead and say I want to add something called CG.
Okay.
I'm inside CG and this GPA is a decimal.
So I'll go ahead and consider decimal.
And let this decimal.
We're going to say it's going to be five for the mark.
And this amount is just going to be one.
So that is what I need I want this map list.
Now if I want to go ahead and insert a check constraint, I can insert a check constraint for every
first name, the GPA or the salary or the employee ID.
Now I want to insert that, and I want to check the GPA of any student before I employ them.
I'll go ahead and put what is called check.
Now if I give a check right in there, I'll go ahead and say that a c GPA must be greater than or equal
to 3.5.
So you must meet a 3.5 and above.
Before I can be able to employ in that company.
So before I can become an employee of that company, you must have a GPA of 3.5 and above.
And I can also give you a GPA, uh, in name.
It can also name that maybe instead of GPA, maybe in case you want to drop that can go ahead and give
it a name and let me say it's constraint.
And then we go ahead and say the name is going to be employee underscore GPA.
And then you go ahead and insert check.
All right.
So how would I school.
So the name is employee underscore GPA.
So anytime I go ahead and call employee underscore GPA it's we always go ahead and drop or execute the
check constraint.
All right.
So that is the name for the GPA constraint which we just added.
Now I'll go ahead and create this table.
Go ahead and create this table.
And then here you can see I have created a table.
And let's go ahead and I refresh this and you can see we have this table right in the air.
Now if you have a tool already created and you want to add a CGP, A, or you want to add a check to
that, you can actually go ahead and add a CGP here by using the updates, which we actually have thought
about.
You can actually update your table by adding a column to a rule to that.
And uh, you can actually also add a column to that as well.
So you can go ahead and update.
Then if you want to add the check constraint you can alter that by adding this.
Go ahead and show you how to do that.
So if I want to add that to an existing table I'll go ahead and say alter table.
In case you have a table you want to add this to.
And I'll go ahead and say the table to add, this is uh, the employees table.
And remember I have already done this, but it's going to tell us if we have this already added.
So go ahead and uh, say add and then go ahead and add constraint.
And then we go ahead and name the constraint to be employee underscore GP.
And I saw how underscore GP and go ahead and name check.
I want that to be opaque is going to say check.
And then we go ahead and um press semicolon at the end of this.
Then what I need to do now guidance GP.
A is going to be greater than or equal to 3.5.
Now if I do this and I run this let's go ahead and check it out.
It says duplicated constraints named employee GP.
So the check constraint has been there already.
So that is a way you can actually add that to an existing table.
So if you have an existing table just go ahead and add to the table and then add the constraint, then
the name of your constraint.
Then check and then put in the column you want to actually set this condition for.
And maybe base salary can say it must be above $2,000 or 5000 and above.
Alright, now let's go ahead.
And if I go ahead and select my table, let's say I want to select this table and show this table.
I can go ahead and say Oklahoma employee.
Then press semicolon.
And then let's go ahead and select table.
So nothing is in there.
What I need to do now is I'll go ahead and get the data we have right in there before.
So it won't waste much of our time typing this and starting afresh all over again.
Now I'll go ahead and drag this down a little bit.
Alright, so this is the data.
We have the Michael, Casey, Thomas, Messi and James and this is what we have.
And now I only I just added this GPA of 4.7, 3.8, 4.1 and 3.6.
So all these are both a 3.5.
Then our head and try another thing altogether by trying to In such the inside the table a an employee
with a GPA below 3.5.
This guy had an update this post and see it now I have just added this.
Let me go ahead and drag this so can be able to see it.
So I have added this and I can say that right in here.
Now the text is I want to go ahead.
We have this table already created now.
And you can see down here our table and there you can see the rows have inserted.
What if I want to insert into that table.
Then I say insert and I'm going to insert into employees.
And then the guy and then give the values.
The values I want to give is a I put a semicolon at the end of this.
I want to first of all add the ID because you have four right in here.
So I want to add ID five.
And then the name is going to be George.
And then let's go ahead and say that George has the Z.
CGP of 3.4.
So this is a CPU of 3.4.
And then I'm going to add a summary.
Maybe let's say the summary is 5000.
All right.
Now let's go ahead and attempt to add these.
And then let's go ahead and check this out.
See the turbo.
Nothing was done.
Let's go ahead and drag this up.
I want to see the error message that was given.
So down here you can see insights employees value five George 3.5 and 5000.
Check this error code says check constraint.
Employee GP is violated.
So you can see that that is actually working fine.
I hope that is interesting.
Now if I go back right here and I change this to 3.5 adults because it's going to be greater than or
equal to 3.5.
Now let's go ahead and execute this query again.
And uh, you can see that our query was executed successfully.
Now I want to drag this up and we can still have George 3.5.
So this is a way you can actually check.
Or condition that if this is not met then this cannot be added to your table.
You can see that we can actually add only students who have a GPA of 3.5 and above.
So what.
Only people have on us to work in that company.
So only people who have honors work in this company now.
So now, if you were to go ahead and, uh, let me say drop the check constraint, you don't want to
check it anymore.
We will just want to add anybody to that.
Let's go ahead and remove this.
And what I need to do is I'll go ahead and say I want to alter the table.
So alter the table named employees.
And what I'm going to do is I'll go ahead and drop.
Check.
So don't check.
And then I'll just go ahead and give the name of the employee, sorry, the name of a check and not
the name of the employee.
So employee On that score GP.
So at the end of this and uh, we can go ahead and execute this code.
And down here you can see draw.
And that is actually marked green tick.
Because here green tick right in here.
Now let's go ahead and check if this actually works if that is actually dropped.
So we can go ahead and uh remove this.
And let's go ahead and insert into employees.
Then go ahead and insert new values.
So insert new values.
And the value I want to insert now is going to be six.
So that is our employee ID six.
And uh let the name be Steve.
And then we go ahead and say has a GPA of 2.4.
And this is way below 3.5.
So we want to ensure if we actually drop the check constraint and let the salary be equal to $3,000.
So we see that this is really too big.
The salary is really too big for 2.4 GB.
Now let's go ahead and check this out.
And you can see what such an error we have.
Steve added with cGPA of 2.4 way below 3.5, showing that you have dropped the check constraint and
that is so cool.
So another way anyone with any GPA can work in your company and that is no problem at all.
So I'll put this down.
That is a way to use check constraint in MySQL and I hope that is very cool.
Put it down.
If you have any question under this, please go ahead and use the question and answer section.
And I'm going to get back to you as soon as possible.
Thank you so much.
And I'm going to see you in the next video lecture.