WEBVTT 00:07.970 --> 00:11.180 I will call you back again to another video lecture in MySQL. 00:11.180 --> 00:15.920 And in this video lecture we go ahead and look at the check constraint. 00:15.920 --> 00:19.430 So the check constraint is like a condition. 00:19.430 --> 00:22.580 So it's a condition that you give in your database. 00:22.580 --> 00:29.480 So let me say here I've dropped the table known as the employee table because I want to do something 00:29.510 --> 00:31.970 important I want us to recreate this table. 00:32.000 --> 00:39.470 But this time around we're going to give a condition that only the employees who have the GPA of 3.5 00:39.470 --> 00:41.150 from their schools will be employed. 00:41.150 --> 00:47.930 So only students who have up to 3.5 and above cGPA are going to be employed in that company. 00:47.930 --> 00:50.690 And that is what I'm going to consider. 00:50.750 --> 00:52.700 Now let's go ahead and put this table back. 00:52.700 --> 00:54.680 So this is the table we have previously. 00:54.680 --> 01:00.800 And now if I go ahead and create this table we are going to have a table known as employees. 01:00.830 --> 01:05.250 Now let me go ahead and say I want to add something called CG. 01:05.280 --> 01:05.730 Okay. 01:05.760 --> 01:10.530 I'm inside CG and this GPA is a decimal. 01:10.530 --> 01:13.380 So I'll go ahead and consider decimal. 01:13.380 --> 01:15.840 And let this decimal. 01:15.870 --> 01:20.490 We're going to say it's going to be five for the mark. 01:20.490 --> 01:23.100 And this amount is just going to be one. 01:23.100 --> 01:26.310 So that is what I need I want this map list. 01:26.340 --> 01:32.100 Now if I want to go ahead and insert a check constraint, I can insert a check constraint for every 01:32.130 --> 01:35.820 first name, the GPA or the salary or the employee ID. 01:36.030 --> 01:43.500 Now I want to insert that, and I want to check the GPA of any student before I employ them. 01:43.530 --> 01:46.230 I'll go ahead and put what is called check. 01:46.440 --> 01:57.600 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 01:57.600 --> 01:59.520 to 3.5. 01:59.520 --> 02:04.540 So you must meet a 3.5 and above. 02:04.570 --> 02:06.970 Before I can be able to employ in that company. 02:06.970 --> 02:13.000 So before I can become an employee of that company, you must have a GPA of 3.5 and above. 02:13.000 --> 02:16.930 And I can also give you a GPA, uh, in name. 02:16.930 --> 02:22.300 It can also name that maybe instead of GPA, maybe in case you want to drop that can go ahead and give 02:22.300 --> 02:25.030 it a name and let me say it's constraint. 02:25.030 --> 02:33.250 And then we go ahead and say the name is going to be employee underscore GPA. 02:33.310 --> 02:36.400 And then you go ahead and insert check. 02:36.910 --> 02:37.480 All right. 02:37.480 --> 02:38.530 So how would I school. 02:38.530 --> 02:40.780 So the name is employee underscore GPA. 02:40.810 --> 02:49.270 So anytime I go ahead and call employee underscore GPA it's we always go ahead and drop or execute the 02:49.270 --> 02:50.620 check constraint. 02:50.650 --> 02:51.040 All right. 02:51.070 --> 02:55.750 So that is the name for the GPA constraint which we just added. 02:55.780 --> 02:57.880 Now I'll go ahead and create this table. 02:57.910 --> 02:59.050 Go ahead and create this table. 02:59.050 --> 03:02.950 And then here you can see I have created a table. 03:02.950 --> 03:07.400 And let's go ahead and I refresh this and you can see we have this table right in the air. 03:07.700 --> 03:14.090 Now if you have a tool already created and you want to add a CGP, A, or you want to add a check to 03:14.120 --> 03:20.540 that, you can actually go ahead and add a CGP here by using the updates, which we actually have thought 03:20.540 --> 03:20.990 about. 03:20.990 --> 03:26.480 You can actually update your table by adding a column to a rule to that. 03:26.480 --> 03:30.530 And uh, you can actually also add a column to that as well. 03:30.530 --> 03:32.090 So you can go ahead and update. 03:32.120 --> 03:37.100 Then if you want to add the check constraint you can alter that by adding this. 03:37.100 --> 03:38.540 Go ahead and show you how to do that. 03:38.540 --> 03:43.610 So if I want to add that to an existing table I'll go ahead and say alter table. 03:43.640 --> 03:45.770 In case you have a table you want to add this to. 03:45.800 --> 03:50.480 And I'll go ahead and say the table to add, this is uh, the employees table. 03:50.480 --> 03:57.080 And remember I have already done this, but it's going to tell us if we have this already added. 03:57.080 --> 04:03.770 So go ahead and uh, say add and then go ahead and add constraint. 04:03.770 --> 04:12.630 And then we go ahead and name the constraint to be employee underscore GP. 04:13.650 --> 04:18.270 And I saw how underscore GP and go ahead and name check. 04:19.500 --> 04:22.290 I want that to be opaque is going to say check. 04:22.290 --> 04:28.080 And then we go ahead and um press semicolon at the end of this. 04:28.320 --> 04:31.260 Then what I need to do now guidance GP. 04:34.320 --> 04:38.250 A is going to be greater than or equal to 3.5. 04:38.280 --> 04:42.210 Now if I do this and I run this let's go ahead and check it out. 04:43.050 --> 04:47.520 It says duplicated constraints named employee GP. 04:47.520 --> 04:50.190 So the check constraint has been there already. 04:50.190 --> 04:53.490 So that is a way you can actually add that to an existing table. 04:53.490 --> 04:58.140 So if you have an existing table just go ahead and add to the table and then add the constraint, then 04:58.140 --> 04:59.160 the name of your constraint. 04:59.190 --> 05:04.890 Then check and then put in the column you want to actually set this condition for. 05:04.890 --> 05:12.610 And maybe base salary can say it must be above $2,000 or 5000 and above. 05:12.880 --> 05:14.050 Alright, now let's go ahead. 05:14.050 --> 05:20.290 And if I go ahead and select my table, let's say I want to select this table and show this table. 05:20.320 --> 05:24.280 I can go ahead and say Oklahoma employee. 05:25.270 --> 05:26.200 Then press semicolon. 05:26.230 --> 05:27.820 And then let's go ahead and select table. 05:27.820 --> 05:28.960 So nothing is in there. 05:28.990 --> 05:33.700 What I need to do now is I'll go ahead and get the data we have right in there before. 05:33.700 --> 05:38.380 So it won't waste much of our time typing this and starting afresh all over again. 05:38.410 --> 05:41.590 Now I'll go ahead and drag this down a little bit. 05:42.250 --> 05:43.660 Alright, so this is the data. 05:43.660 --> 05:48.430 We have the Michael, Casey, Thomas, Messi and James and this is what we have. 05:48.430 --> 05:58.450 And now I only I just added this GPA of 4.7, 3.8, 4.1 and 3.6. 05:58.450 --> 06:02.140 So all these are both a 3.5. 06:02.170 --> 06:10.510 Then our head and try another thing altogether by trying to In such the inside the table a an employee 06:10.540 --> 06:14.110 with a GPA below 3.5. 06:14.140 --> 06:17.950 This guy had an update this post and see it now I have just added this. 06:17.980 --> 06:20.110 Let me go ahead and drag this so can be able to see it. 06:20.110 --> 06:22.780 So I have added this and I can say that right in here. 06:22.960 --> 06:26.080 Now the text is I want to go ahead. 06:26.080 --> 06:27.970 We have this table already created now. 06:28.000 --> 06:32.560 And you can see down here our table and there you can see the rows have inserted. 06:32.740 --> 06:36.430 What if I want to insert into that table. 06:36.430 --> 06:44.170 Then I say insert and I'm going to insert into employees. 06:44.260 --> 06:47.020 And then the guy and then give the values. 06:49.390 --> 06:54.640 The values I want to give is a I put a semicolon at the end of this. 06:54.700 --> 06:58.390 I want to first of all add the ID because you have four right in here. 06:58.390 --> 07:00.220 So I want to add ID five. 07:00.220 --> 07:03.430 And then the name is going to be George. 07:03.460 --> 07:07.100 And then let's go ahead and say that George has the Z. 07:07.130 --> 07:11.180 CGP of 3.4. 07:11.750 --> 07:13.670 So this is a CPU of 3.4. 07:13.670 --> 07:15.560 And then I'm going to add a summary. 07:15.590 --> 07:19.010 Maybe let's say the summary is 5000. 07:19.370 --> 07:19.670 All right. 07:19.700 --> 07:23.030 Now let's go ahead and attempt to add these. 07:23.240 --> 07:25.940 And then let's go ahead and check this out. 07:26.360 --> 07:28.160 See the turbo. 07:28.160 --> 07:29.210 Nothing was done. 07:29.240 --> 07:31.400 Let's go ahead and drag this up. 07:31.430 --> 07:33.950 I want to see the error message that was given. 07:33.980 --> 07:41.720 So down here you can see insights employees value five George 3.5 and 5000. 07:41.930 --> 07:44.750 Check this error code says check constraint. 07:44.750 --> 07:46.790 Employee GP is violated. 07:46.850 --> 07:49.730 So you can see that that is actually working fine. 07:49.850 --> 07:51.170 I hope that is interesting. 07:51.200 --> 07:58.460 Now if I go back right here and I change this to 3.5 adults because it's going to be greater than or 07:58.460 --> 07:59.600 equal to 3.5. 07:59.630 --> 08:02.060 Now let's go ahead and execute this query again. 08:02.060 --> 08:06.350 And uh, you can see that our query was executed successfully. 08:06.380 --> 08:10.950 Now I want to drag this up and we can still have George 3.5. 08:11.250 --> 08:13.500 So this is a way you can actually check. 08:13.530 --> 08:20.490 Or condition that if this is not met then this cannot be added to your table. 08:20.730 --> 08:26.190 You can see that we can actually add only students who have a GPA of 3.5 and above. 08:26.190 --> 08:26.730 So what. 08:26.760 --> 08:29.280 Only people have on us to work in that company. 08:29.280 --> 08:32.580 So only people who have honors work in this company now. 08:32.610 --> 08:39.240 So now, if you were to go ahead and, uh, let me say drop the check constraint, you don't want to 08:39.270 --> 08:40.020 check it anymore. 08:40.050 --> 08:41.970 We will just want to add anybody to that. 08:42.000 --> 08:43.410 Let's go ahead and remove this. 08:43.440 --> 08:47.400 And what I need to do is I'll go ahead and say I want to alter the table. 08:47.400 --> 08:51.750 So alter the table named employees. 08:51.780 --> 08:56.070 And what I'm going to do is I'll go ahead and drop. 08:58.440 --> 08:59.250 Check. 08:59.580 --> 09:01.380 So don't check. 09:01.380 --> 09:06.600 And then I'll just go ahead and give the name of the employee, sorry, the name of a check and not 09:06.600 --> 09:07.590 the name of the employee. 09:07.590 --> 09:10.390 So employee On that score GP. 09:10.660 --> 09:16.570 So at the end of this and uh, we can go ahead and execute this code. 09:16.570 --> 09:19.000 And down here you can see draw. 09:19.300 --> 09:23.650 And that is actually marked green tick. 09:23.680 --> 09:25.450 Because here green tick right in here. 09:25.480 --> 09:30.250 Now let's go ahead and check if this actually works if that is actually dropped. 09:30.250 --> 09:33.220 So we can go ahead and uh remove this. 09:33.220 --> 09:39.220 And let's go ahead and insert into employees. 09:39.370 --> 09:42.250 Then go ahead and insert new values. 09:44.350 --> 09:48.640 So insert new values. 09:48.640 --> 09:55.090 And the value I want to insert now is going to be six. 09:55.210 --> 09:59.320 So that is our employee ID six. 09:59.320 --> 10:03.220 And uh let the name be Steve. 10:03.280 --> 10:07.570 And then we go ahead and say has a GPA of 2.4. 10:07.570 --> 10:09.790 And this is way below 3.5. 10:09.790 --> 10:16.610 So we want to ensure if we actually drop the check constraint and let the salary be equal to $3,000. 10:16.610 --> 10:18.590 So we see that this is really too big. 10:18.620 --> 10:22.400 The salary is really too big for 2.4 GB. 10:22.670 --> 10:24.650 Now let's go ahead and check this out. 10:24.650 --> 10:27.740 And you can see what such an error we have. 10:27.740 --> 10:35.660 Steve added with cGPA of 2.4 way below 3.5, showing that you have dropped the check constraint and 10:35.660 --> 10:36.770 that is so cool. 10:36.770 --> 10:44.210 So another way anyone with any GPA can work in your company and that is no problem at all. 10:44.210 --> 10:45.980 So I'll put this down. 10:45.980 --> 10:50.660 That is a way to use check constraint in MySQL and I hope that is very cool. 10:50.690 --> 10:51.440 Put it down. 10:51.470 --> 10:55.520 If you have any question under this, please go ahead and use the question and answer section. 10:55.520 --> 10:57.800 And I'm going to get back to you as soon as possible. 10:57.800 --> 10:58.760 Thank you so much. 10:58.760 --> 11:01.820 And I'm going to see you in the next video lecture.