WEBVTT 00:07.460 --> 00:10.100 I will call you back again to another video lecture in MySQL. 00:10.100 --> 00:14.960 And in this video lecture we go ahead and look at the unique constraint in MySQL. 00:14.960 --> 00:16.850 So we are creating a database. 00:16.880 --> 00:19.850 You want to make one of your columns to be unique. 00:19.880 --> 00:24.560 It means that, uh, two of each data are not going to be the same. 00:25.490 --> 00:27.560 Each of the datas are going to be different. 00:27.560 --> 00:30.200 So let us go ahead and create a new table. 00:30.200 --> 00:32.510 I'm going to leave the student table we have right in here. 00:32.510 --> 00:35.060 So that's one I'm creating this table. 00:35.090 --> 00:39.770 I'm going to add the unique constraint as well as this uh student. 00:40.100 --> 00:45.110 You can actually go ahead and specify one of these columns to be unique. 00:45.140 --> 00:47.990 Well let me go ahead and start afresh so we can be able to see it. 00:47.990 --> 00:56.450 So let's go ahead and uh, create a new table and let the name of the table be employees. 00:57.020 --> 00:57.440 Right. 00:57.920 --> 00:59.030 Semicolon end. 00:59.030 --> 01:03.420 And uh, we go here and add in some data. 01:03.450 --> 01:03.810 Okay. 01:03.840 --> 01:08.340 So let me go ahead and say I need the employee ID. 01:08.370 --> 01:11.160 So and the ID is going to be an integer. 01:11.160 --> 01:16.260 And then the first name I just want to make a simple table. 01:16.290 --> 01:17.610 Go ahead and say the first name. 01:17.610 --> 01:22.470 And that is varchar because it's going to be character. 01:22.470 --> 01:25.260 And I'll let the character be 30. 01:25.290 --> 01:25.830 Okay. 01:25.860 --> 01:27.210 I just need 30 of them. 01:27.210 --> 01:30.150 And then the last thing I need is your salary. 01:30.480 --> 01:30.810 All right. 01:30.810 --> 01:31.980 So I just made a simple table. 01:31.980 --> 01:34.050 We can see that this is really very simple right. 01:34.050 --> 01:39.030 And we're going to give the data type for the salary as int. 01:39.030 --> 01:40.170 And that is O. 01:40.200 --> 01:44.400 Now I'm going to choose one of these one of these columns which I'm just creating here. 01:44.400 --> 01:49.200 So I'm going to choose one of them to be to have a unique constraint. 01:49.200 --> 01:52.950 Or I'll go ahead and sign assign a unique constraint to one of them. 01:52.950 --> 01:56.400 And if I want to do that, I'm going to use a simple keyword called unique. 01:56.400 --> 02:01.080 So I can now choose the employee ID or the employee first name or the salary. 02:01.110 --> 02:04.300 Let's go ahead and say I'm assuming I'm going to use the first name. 02:04.300 --> 02:07.360 What I'm going to do is go ahead and say unique. 02:07.390 --> 02:15.190 So this keyword unique, we actually make the first name to be unique, meaning that no employee or 02:15.190 --> 02:17.890 two employees will never get the same first name. 02:17.890 --> 02:19.600 And you know it's not possible, right? 02:19.630 --> 02:25.720 But I'm doing this for the purpose of this course, so you can assign this to the employee ID, meaning 02:25.720 --> 02:30.220 that no employee will have the same ID, but for the purpose of clarity. 02:30.220 --> 02:37.210 And as a beginner, I'm assigning this the first name so you can see how the unique keyword actually 02:37.210 --> 02:37.810 works. 02:37.840 --> 02:41.260 Now let's go ahead and create this table. 02:41.950 --> 02:42.460 All right. 02:42.460 --> 02:44.020 You can see that we have created this table. 02:44.020 --> 02:47.620 And if I refresh this I have employees right in here. 02:47.620 --> 02:52.120 And the columns employee ID the first name and the salaries are right in here. 02:52.120 --> 02:57.250 So you can see that it works fine right now assuming that you have this students table. 02:57.250 --> 03:00.370 And let's go ahead and open this table right in here again. 03:00.370 --> 03:02.590 So I have this students table right in here. 03:02.590 --> 03:05.630 And I did assign Anyone. 03:05.630 --> 03:10.460 So what I need to do is just like here, I don't need to create the table. 03:10.460 --> 03:13.310 I'm going to use this employee table to play around. 03:13.310 --> 03:16.310 So you can actually assign that on your own student table. 03:16.310 --> 03:19.670 What I need to do is that I will ahead and delete this. 03:19.670 --> 03:23.540 So if I delete that I'm going to alter the table I just created. 03:23.540 --> 03:25.640 So let's go ahead and say alter. 03:25.730 --> 03:30.470 And go ahead and say table and choose the table you want to alter. 03:30.470 --> 03:32.480 And that is the employees. 03:32.570 --> 03:33.080 All right. 03:33.110 --> 03:37.190 So I'm going to say employees. 03:37.670 --> 03:41.810 And then I'll go ahead and add constraints. 03:43.640 --> 03:43.970 All right. 03:44.000 --> 03:46.010 Because you have a keyword called constraint. 03:46.010 --> 03:48.980 And go ahead and add a unique. 03:48.980 --> 03:51.860 And then where do you want to assign this unique. 03:51.860 --> 03:54.890 Because that is the next thing that you need to specify. 03:54.920 --> 03:57.080 Remember that everything can be in one straight line. 03:57.080 --> 03:59.990 But I just want us to see this here. 04:00.020 --> 04:03.830 Now let's go ahead and see the first underscore name. 04:03.860 --> 04:04.490 All right. 04:04.490 --> 04:09.450 So that is if you go right in here we have a column called First Name. 04:09.450 --> 04:12.570 So that is we want to assign this now overhead. 04:12.630 --> 04:18.240 Now run this ahead and run this and check it out. 04:18.690 --> 04:19.410 Okay. 04:19.440 --> 04:23.580 Down here you can see that we didn't have a red tick. 04:23.610 --> 04:25.470 We had a warning. 04:25.500 --> 04:26.130 Yes. 04:26.160 --> 04:27.960 It's a running another red tick. 04:27.960 --> 04:28.980 What does it mean. 04:28.980 --> 04:38.190 Go over here and you can see duplicate index first name to define on the table MySQL employees, meaning 04:38.190 --> 04:46.080 that I am specifying a unique constraint to a column that has already been specified. 04:47.160 --> 04:47.850 You get that. 04:47.850 --> 04:51.240 It means that I don't need to specify it again, because I've already done that. 04:51.240 --> 04:57.270 So this can only be done if you have created your table and you didn't assign a unique constraint at 04:57.270 --> 04:58.320 the very first time. 04:58.320 --> 05:01.350 So you actually did this when you have created your table. 05:01.350 --> 05:08.890 So you can go to your students table and use this alter table and then assign any of your columns a 05:08.890 --> 05:13.150 unique constraint, and it means that two of them can never be the same. 05:13.180 --> 05:15.220 Remember that everything can be in a straight line. 05:15.250 --> 05:20.920 Okay, so you can see that what it can just go ahead and put them in a separate line and it's still 05:20.920 --> 05:21.220 walk. 05:21.220 --> 05:23.320 So I'm making everything to be clean. 05:23.350 --> 05:24.820 That is why I move it to the next line. 05:24.820 --> 05:28.450 So now you have seen that we have already assigned that and that is all. 05:28.450 --> 05:29.530 Can't assign that again. 05:29.560 --> 05:35.290 This guy had to select this guy and to select all from our table. 05:36.220 --> 05:43.960 So select from employees and put a semicolon at the end. 05:43.960 --> 05:46.540 And let's go ahead and run this. 05:46.570 --> 05:50.350 And this is employee ID first name and salary. 05:50.380 --> 05:53.590 Now let's go ahead and add some things inside here. 05:53.710 --> 05:55.960 Let's go ahead and want to add some values. 05:55.990 --> 05:58.060 All you need to do is to go ahead and insert. 05:58.090 --> 06:01.960 So this is a static keyword into employees. 06:03.250 --> 06:07.180 So and go ahead and add the values. 06:08.170 --> 06:14.130 So the values want to add a skarhead and, uh, with these, with the comma. 06:15.330 --> 06:20.280 And let's go ahead and add more glaucoma with a comma. 06:22.530 --> 06:23.940 A comma okay. 06:24.570 --> 06:25.680 So. 06:27.780 --> 06:31.440 I mean, let me go ahead and make this to be the last because we just want to keep everything to be 06:31.440 --> 06:31.890 simple. 06:31.890 --> 06:39.570 So the first one the employee ID number one and uh, let employee name be let me say Michael. 06:40.170 --> 06:41.340 Oh okay. 06:41.370 --> 06:42.510 So go ahead and change this. 06:42.510 --> 06:45.210 So this is, uh, Michael. 06:45.210 --> 06:49.620 And then we kind of set a salary is a 2400. 06:49.620 --> 06:56.490 And, uh, this guy said a second one and then the name is going to be equal to Kizito. 06:58.890 --> 07:04.050 And they let Kizito salary be equal to 3200. 07:04.050 --> 07:10.320 And, uh, the next one we have is, uh, let me say. 07:12.850 --> 07:17.020 The name is going to be James. 07:18.160 --> 07:20.650 So James and the late James. 07:20.680 --> 07:23.110 Sorry we got to the $7,000. 07:23.140 --> 07:28.060 Now, let's assume I didn't know that I have to already there. 07:28.090 --> 07:36.520 I'll go ahead and make the photo ID, and I'll go ahead and put the same first name. 07:36.550 --> 07:37.690 Let's say this is a mistake. 07:37.720 --> 07:39.370 I didn't know it actually exists. 07:39.370 --> 07:42.850 And let me say, the salary is a 1100. 07:42.880 --> 07:49.930 And now, if I should try to insert this data into my table, I'm going to get an error. 07:49.960 --> 07:52.900 The error is going to tell me that Cachito has been, uh. 07:52.900 --> 07:56.590 I'm trying to assign it twice. 07:56.620 --> 07:57.370 Okay. 07:57.400 --> 08:03.250 And then I'm trying to enter Casita twice, which is not going to be possible because everything under 08:03.250 --> 08:05.140 the first name has to be unique. 08:05.170 --> 08:07.540 Now let's go ahead and run this query and check it out. 08:07.660 --> 08:10.450 So down here you can see I have this. 08:10.480 --> 08:12.980 This is not only a warning but an error. 08:13.010 --> 08:18.410 It shows duplicate entry for key employee first name. 08:18.410 --> 08:23.570 It means that we have already made this and that is the first name. 08:23.570 --> 08:24.830 The common first name. 08:24.890 --> 08:27.380 We have already given it a unique constraint. 08:27.410 --> 08:28.100 All right. 08:28.100 --> 08:33.590 That is why we cannot be able to assign the same first name under here. 08:33.620 --> 08:38.540 Now let's go ahead and remove zero and say this is a guest and not Kizito. 08:39.890 --> 08:42.380 So there's a difference between Cassette and Kizito. 08:42.410 --> 08:43.430 Let's go ahead and check it out. 08:43.460 --> 08:45.650 Go ahead and execute this query. 08:45.650 --> 08:48.110 And I can see my query has been executed. 08:48.110 --> 08:54.050 And if I should go ahead and select these let's go ahead I select this. 08:55.370 --> 08:59.570 So select all from. 09:01.940 --> 09:03.140 Employees. 09:03.140 --> 09:07.760 So select all from employees I put a semicolon and go ahead and run this. 09:07.790 --> 09:08.600 Let's check it out. 09:08.600 --> 09:15.570 So you can see I have a first employee ID Michael Kizito James and Kizzy. 09:15.600 --> 09:20.160 But I was able to enter Kizito two times. 09:20.460 --> 09:26.850 So that is a way to assign a unique constraint to any of your columns, and no two data will ever be 09:26.850 --> 09:27.570 the same. 09:27.900 --> 09:28.890 Jonathan. 09:28.920 --> 09:31.020 But this is for qualitative purpose. 09:31.050 --> 09:32.640 You've known about unique constraint. 09:32.670 --> 09:40.650 Now it is your job and assignment to actually add that to your employee ID and not your first name and 09:40.650 --> 09:44.610 not your salary, because you can have employees that get the same name. 09:44.910 --> 09:51.540 You can have employees that have the same salary, but two employees should not have the same ID so 09:51.540 --> 09:54.270 that you can actually differentiate them. 09:54.690 --> 09:54.990 All right. 09:55.200 --> 09:55.830 That is cool. 09:55.860 --> 09:56.820 Go ahead and put it down. 09:56.820 --> 10:00.180 And if you have any questions on that please use the question and answer section. 10:00.180 --> 10:02.610 And I'm going to get back to you as soon as possible. 10:02.640 --> 10:03.540 Thank you so much. 10:03.540 --> 10:06.270 And I'm going to see you in the next video lecture.