WEBVTT 00:07.700 --> 00:11.030 I welcome you back again to another video lecture in MySQL. 00:11.030 --> 00:16.670 And in this video lecture, let's go ahead and look at the default constraint. 00:17.120 --> 00:17.660 All right. 00:17.660 --> 00:19.490 So we have this table called employees. 00:19.490 --> 00:23.600 And we're going to use that to explain what the default constraint is all about. 00:23.630 --> 00:32.870 Now the default constraint is a constraint that helps us to insert a particular condition that helps 00:32.870 --> 00:37.550 to maintain a status quo in your database. 00:37.550 --> 00:45.050 So let me say that you have this, uh, employee database, and we don't want to consider your cGPA 00:45.080 --> 00:45.710 anymore. 00:45.710 --> 00:49.580 So anybody can enroll and we don't want to take record of GPA. 00:49.580 --> 00:55.190 And we don't want this to also be null, but we want that to just like 0.0. 00:55.190 --> 00:58.820 And we don't want to be entering that 0.0 by ourself. 00:58.910 --> 01:04.490 Let me go ahead and say I have this table or this that I want to include, and look at this data right 01:04.490 --> 01:04.940 in here. 01:04.940 --> 01:09.050 So I have insight into an employee's values. 01:09.170 --> 01:17.720 Uh, employee ID for George, 0.0, employee five, id id five, and first name is Steve 0.0. 01:17.720 --> 01:22.130 And uh, the next one is six -0.0. 01:22.130 --> 01:26.480 So if I should go ahead and run this then you can see that over here. 01:26.480 --> 01:28.880 I have, uh, this right. 01:28.910 --> 01:31.130 And get now this is what we want to get. 01:31.160 --> 01:33.860 We want to have 0.0, 0.00.0. 01:33.890 --> 01:39.800 We don't consider the cGPA anymore, but we don't want to go ahead and start typing this by ourselves. 01:39.830 --> 01:45.050 We don't want to start typing, uh, premises employee ID for George. 01:45.050 --> 01:46.670 Then we go ahead and add 0.0. 01:46.700 --> 01:48.770 We don't want we don't just want it anymore. 01:48.770 --> 01:55.850 We just want to enter only your employee ID there, your first name, and then it'll go ahead and automatically 01:55.850 --> 01:57.350 include 0.0. 01:57.350 --> 01:58.940 So how can we be able to do that? 01:58.940 --> 02:01.070 First of all let me go ahead and delete this. 02:01.070 --> 02:05.060 Then I'll go ahead and remove that from the table. 02:05.090 --> 02:07.490 To delete that I'll go ahead and say this. 02:07.490 --> 02:12.890 So delete uh, from employees. 02:12.890 --> 02:14.870 So that is from employees. 02:14.900 --> 02:25.520 Then where we have the employee ID what the employee ID is greater than or equal to four and put a semicolon 02:25.520 --> 02:26.000 at the end. 02:26.030 --> 02:27.800 Now let's go ahead and run this and check it out. 02:27.830 --> 02:33.650 Now we have remove that and want to repeat what we just did right now because I used that to explain 02:33.650 --> 02:35.030 how this works. 02:35.030 --> 02:42.050 So if I go ahead and uh say I want to maybe create a new table. 02:42.050 --> 02:49.880 So if I want to create a new table on this, all I need to do is, uh, I'll go over here and then go 02:49.880 --> 02:50.960 ahead and delete this. 02:50.990 --> 02:52.460 I'll go ahead and put this. 02:52.490 --> 02:58.820 Now I'll go away and I'll create a new employee table called employee ID and their first name equal 02:58.820 --> 03:01.730 to Charles, then CGP equal to decimal. 03:01.730 --> 03:04.190 And that five precision one. 03:04.190 --> 03:06.500 Then I will set the default to be zero. 03:06.500 --> 03:11.360 Or I'll go ahead and say 0.01 because I have this table right here. 03:11.390 --> 03:13.280 I don't need to create that again. 03:13.280 --> 03:19.370 So if I should go ahead to create this table, you can see that table employee already exists. 03:19.400 --> 03:21.200 You can see that that is the error message. 03:21.230 --> 03:22.490 Go ahead and drag this up. 03:22.610 --> 03:24.620 So you can see we have this error message. 03:24.650 --> 03:26.690 Employee table already exists. 03:26.720 --> 03:31.640 Now the reason why I didn't want to create this table from the beginning is because throughout this 03:31.640 --> 03:35.810 course I've been teaching you how to actually do that on an existing table. 03:35.810 --> 03:41.510 So now I want to actually implement it by yourself by setting this up on existing table. 03:41.510 --> 03:45.080 So how can we do that on existing table by setting this. 03:45.080 --> 03:47.180 So let's go ahead and try it over again. 03:47.210 --> 03:54.650 Now I will also has already existed what we want to actually set the CGP 8 to 0.0 for any name that 03:54.650 --> 04:02.030 has been entered, or any employee ID with the name, what I need to do is I'll go ahead and see Alter 04:02.480 --> 04:09.350 Table, and the name of the table you want to choose is a employees. 04:09.350 --> 04:12.050 And then I need to. 04:12.980 --> 04:21.770 alter the CDP and then set the defaults to be equal to 0.0. 04:21.860 --> 04:23.810 The semicolon at the end. 04:23.840 --> 04:25.040 Now, instead of. 04:25.070 --> 04:32.870 There is no need for this 0.0, because you have already set a CGP to be decimal and five precision 04:32.900 --> 04:33.140 one. 04:33.140 --> 04:35.570 What we've created is that we initially can go ahead and set. 04:35.570 --> 04:37.790 The default is equal to 0.0. 04:37.790 --> 04:39.020 And that is all. 04:39.050 --> 04:42.260 Now let's go ahead and execute this. 04:42.260 --> 04:48.110 And down here you can see that we have this returned. 04:48.140 --> 04:48.740 Okay. 04:48.770 --> 04:49.910 So we have this returned. 04:49.910 --> 04:52.040 And tick green means we have set it. 04:52.070 --> 04:59.990 Now if we want to go ahead and, uh, check this out, what I need to do is let's go ahead and insert 04:59.990 --> 05:07.970 this paragraph and say insert into insert into employees. 05:08.000 --> 05:14.330 Then I will go ahead and, uh, see values. 05:14.570 --> 05:18.950 I want this to be in uppercase so valleys. 05:18.950 --> 05:21.860 And then I'll go ahead and give the values. 05:22.040 --> 05:26.630 So I have a comma and the next one. 05:28.820 --> 05:30.890 Put a camera then the next one. 05:30.890 --> 05:34.190 Just watch only three of these. 05:37.520 --> 05:40.640 Oh I saw the last one constant semicolon at the end. 05:40.640 --> 05:44.870 So the first id I need is four. 05:44.900 --> 05:47.420 Then the first one is George. 05:47.420 --> 05:51.890 And uh let's go ahead and put the fifth. 05:51.920 --> 05:56.330 The next one and uh is Steve. 05:56.330 --> 06:01.280 So remember to put a comma right in here and that this is our Steve. 06:01.310 --> 06:07.490 Then the next one is y say this is uh. 06:10.250 --> 06:10.970 And Manuel. 06:11.150 --> 06:13.700 Now we have these IDs. 06:13.820 --> 06:15.410 Um, I will have this employees. 06:15.440 --> 06:20.600 Now, if we go ahead and, uh, set these, we're are going to get an error because we didn't actually 06:20.600 --> 06:24.410 specify any of the kernel for CGP. 06:24.440 --> 06:27.320 Now let's try to run this code and see if that works out fine. 06:27.320 --> 06:27.740 Now. 06:27.770 --> 06:29.420 Now here you can see we have an error. 06:29.420 --> 06:32.870 Say column card doesn't match value card at row one. 06:32.900 --> 06:36.440 Now what happens is because we didn't consider the CGP. 06:36.470 --> 06:42.770 So what you need to do is go back right inside, insert into employees and give the condition that this 06:42.770 --> 06:45.560 is going to be for employee. 06:47.720 --> 06:52.640 Employee underscore ID and just the first name. 06:53.210 --> 06:55.040 So first underscore name. 06:55.310 --> 06:58.820 So that is where we just want to insert right. 06:58.820 --> 07:03.290 And let's go ahead and uh enter this. 07:04.280 --> 07:08.780 And uh it says this is employee ID and not the employee ID. 07:09.020 --> 07:09.470 All right. 07:09.470 --> 07:10.910 So that has been corrected. 07:10.940 --> 07:12.530 And go ahead and insert this. 07:12.830 --> 07:15.560 Oh and now so you can see we have this. 07:16.250 --> 07:18.710 Then I want to drive this a little bit down here. 07:18.710 --> 07:22.580 So you can see that once we have Michael is still 4.7. 07:22.610 --> 07:24.380 Crystal is still at 4.8. 07:24.410 --> 07:26.150 Mike is 4.1. 07:26.150 --> 07:35.480 Then George, Steve and the memo which we just entered are 4.0, 0.0, 0.0, 0.0 respectively. 07:35.750 --> 07:38.540 And you see, we didn't enter that by ourselves. 07:38.900 --> 07:43.040 This is because we have already set the default to be zero. 07:43.070 --> 07:43.640 Okay. 07:43.670 --> 07:45.380 So we have set a default to zero. 07:45.380 --> 07:51.290 So anytime you include any of these things let's say enter any data into this. 07:51.320 --> 07:54.920 It will head and set HPA to 0.0. 07:54.920 --> 07:56.810 And that is really very interesting. 07:56.810 --> 07:58.280 So I hope that is cool. 07:58.310 --> 08:01.490 Now I'll go ahead and take this a little bit. 08:01.490 --> 08:05.780 And let's go ahead and add another column right here. 08:05.780 --> 08:08.390 And let that column be the enrollment date. 08:08.390 --> 08:16.220 So that any time we employ a new person into the company without we going to insert the date, which 08:16.250 --> 08:20.270 automatically gives us the current date and time, we depress it in rules. 08:20.300 --> 08:22.490 Now let's go ahead and delete this. 08:22.490 --> 08:24.350 And for us to add a colon. 08:24.350 --> 08:26.180 So go ahead and say alter. 08:26.210 --> 08:26.720 All right. 08:26.750 --> 08:28.130 Let's go ahead and make this uppercase. 08:28.130 --> 08:31.070 So this is alter table. 08:31.070 --> 08:35.720 And the table went over to the employees table. 08:35.720 --> 08:48.320 And then we go ahead and uh add the let's say this name is going to be employment underscore date. 08:48.320 --> 08:53.930 So employment date and the the data type is going to be date time. 08:53.960 --> 08:57.650 I don't want that to be date or time alone I want that to be date time. 08:57.770 --> 09:00.620 So it's going to give us a date and a time as well. 09:00.650 --> 09:03.230 Now I want to set this. 09:03.320 --> 09:03.860 All right. 09:03.860 --> 09:11.660 So let's go ahead and say set this defaults and uh let it default be null. 09:11.660 --> 09:19.460 So in this case we have now set this to have given it a default null means that it's going to give all 09:19.460 --> 09:24.080 the time or the date which the person is being enrolled. 09:24.110 --> 09:27.950 Now let's go ahead and uh, Enter these and let's check it out. 09:27.950 --> 09:30.590 So you can see that we didn't type anything. 09:30.590 --> 09:35.900 It gave us the employment date for everybody for everyone that's been enrolled. 09:35.900 --> 09:38.780 So in the next one I want to check this out. 09:38.810 --> 09:42.110 It gives you the current time you understand. 09:42.110 --> 09:45.440 So in next minutes many more years to come. 09:45.470 --> 09:48.500 Next year it's always update itself. 09:48.500 --> 09:54.440 So anytime you enroll someone into the company, automatically the date time will be entered in the 09:54.440 --> 09:55.130 database. 09:55.130 --> 09:57.860 And you don't need to go ahead and start repeating this. 09:57.860 --> 10:03.470 So by default, I've already set this, and I've already set the GPS to record 0.0 because we're not 10:03.470 --> 10:05.960 considering cGPA on your employment. 10:06.140 --> 10:09.080 You know, uh, again, so what's your input? 10:09.110 --> 10:10.670 You go ahead and get started. 10:10.700 --> 10:12.380 Nobody consider your cGPA. 10:12.380 --> 10:18.410 And we've also taken the time that you enrolled or into the company, or the time you're employed would 10:18.410 --> 10:21.800 get your employment date and your GPA is negligible. 10:21.800 --> 10:22.820 So that is cool. 10:22.820 --> 10:23.900 I hope that is cool. 10:24.830 --> 10:29.180 Oh I so you can go ahead and drop any of these by writing drop. 10:29.190 --> 10:30.030 I will drop that. 10:30.060 --> 10:30.870 Go ahead and put it down. 10:30.870 --> 10:35.190 If you want to drop these things, all I need to do is I'll go ahead and drop. 10:35.280 --> 10:38.250 Let me go ahead and say, uh, drop. 10:39.570 --> 10:41.760 When I say drop. 10:41.760 --> 10:49.380 Uh, employee employment underscore date. 10:49.380 --> 10:55.500 So if I drop colon so going to say drop colon and permit underscore date. 10:55.500 --> 10:57.630 And I will go ahead and drop this. 10:57.630 --> 10:59.250 So that is it for now. 11:00.030 --> 11:00.900 We're done with this. 11:00.900 --> 11:02.790 And I hope you are familiar. 11:02.820 --> 11:07.020 And you understood what employment the default actually does. 11:07.020 --> 11:11.910 So the default constraint helps us to be able to set our defaults. 11:11.910 --> 11:16.560 And we actually work whatever we set without repeating that any time. 11:16.560 --> 11:17.190 Again. 11:17.190 --> 11:18.810 So that is it for now. 11:18.810 --> 11:19.770 So thank you so much. 11:19.770 --> 11:22.770 And I'm going to see you in the next video lecture.