WEBVTT 00:07.610 --> 00:08.000 All right. 00:08.030 --> 00:13.100 So let's go ahead and look at unique constraints in PostgreSQL. 00:13.520 --> 00:17.900 So in PostgreSQL we have a dealt with the primary key. 00:17.900 --> 00:21.620 And the primary key can only be added to one of your columns. 00:21.620 --> 00:26.390 If I should go ahead and see add on I want to add these maybe to email. 00:26.390 --> 00:29.570 And if I go ahead and run this code I'm going to have an error. 00:29.600 --> 00:33.590 It serves multiple primary key for table students and not allowed. 00:33.620 --> 00:34.520 Wow. 00:34.820 --> 00:42.950 And now if you leave your email without any unique constraint, it means that two persons can have the 00:42.950 --> 00:44.360 same email address. 00:44.360 --> 00:46.640 That is not supposed to be. 00:47.030 --> 00:47.840 All right. 00:48.170 --> 00:54.830 So how can you be able to checkmate this so that two persons will not have the same email address? 00:54.860 --> 00:56.060 Let's go ahead and see. 00:56.090 --> 01:02.960 We're going to use a count to check email addresses that are of the same discourse head under select. 01:04.380 --> 01:06.030 Oh, the scorpion on the. 01:06.060 --> 01:08.580 Okay, now go ahead and select just Email and not all. 01:08.580 --> 01:13.200 So select email and let's go ahead and count this email. 01:15.690 --> 01:20.670 And I count this email from students. 01:20.670 --> 01:22.350 And then we're going to group. 01:22.350 --> 01:28.980 That email is indeed group by class group that email by email and a score head. 01:28.980 --> 01:30.270 And check this out. 01:30.360 --> 01:32.280 Now we have all this email. 01:32.280 --> 01:35.190 And if you can check that out we have one and one. 01:35.190 --> 01:43.470 And one of this email is to accept this email that is that is 300 and that is the no. 01:43.500 --> 01:44.820 The ones without email. 01:44.820 --> 01:52.680 So if you are going to use the having clause to check this out, you can go ahead and say group by email 01:52.680 --> 01:56.790 and uh using the having clause. 01:56.790 --> 01:58.950 So having count. 01:58.950 --> 02:05.310 And then you go ahead and put text that is all and check emails that are greater than one. 02:05.310 --> 02:09.790 Let's go ahead had a check and will have just 309. 02:10.090 --> 02:16.540 But there's one thing that is still risky, and it means that our table is not protected, and that 02:16.540 --> 02:23.680 is the unique constraint because someone can get to the email just like we have in this, uh, place 02:23.680 --> 02:25.570 the square head on the brink of this. 02:25.570 --> 02:26.710 So we have this guy. 02:26.740 --> 02:30.280 We can also go ahead and remove the stress ID. 02:30.790 --> 02:31.150 It's quite. 02:31.180 --> 02:32.440 And remove the stress ID. 02:32.620 --> 02:36.520 And I'm going to also remove this stress ID right in here. 02:36.640 --> 02:46.090 And I can change the name of this guy to maybe change the name to George and the last name to be Steve. 02:46.090 --> 02:48.940 But they have the same email address, is it not? 02:48.940 --> 02:50.200 So I'm going to copy this. 02:50.230 --> 02:51.850 Then go ahead and copy this. 02:51.850 --> 02:54.970 And now go back right in here and I'm going to press that. 02:54.970 --> 03:01.900 So now you can see insight into the first name, last name and so on. 03:01.900 --> 03:03.610 But we have the same email. 03:03.640 --> 03:08.600 So if I go ahead and execute this query you can see inside the zero one it means that I have having 03:08.600 --> 03:15.680 started this and right now, if I should go ahead and, uh, go back right in here and check email addresses 03:15.680 --> 03:18.200 that are greater than one, let's go ahead and check it out. 03:18.200 --> 03:19.220 We have two. 03:19.790 --> 03:28.400 So you can see that these actually exist two two times because we have that for uh, two presses. 03:28.400 --> 03:31.340 So let's go ahead and uh go ahead and select this. 03:31.340 --> 03:37.520 So I actually copy that and I'm going to say select. 03:39.800 --> 03:43.820 Oh from students. 03:44.780 --> 03:53.630 So select all from students where the email is equal to this. 03:53.630 --> 03:55.250 And with a semicolon at the end. 03:55.250 --> 03:56.960 And let's go ahead and check it out. 03:57.470 --> 03:58.670 So. 04:02.210 --> 04:06.020 So make sure that this is a single quotation mark or had. 04:06.020 --> 04:10.340 And uh let's go ahead and remove this and put back to that single quotation mark. 04:10.460 --> 04:15.480 So when M0 is equal to this and go ahead and execute this query and check it out. 04:15.480 --> 04:18.570 So we have Mark on and we have George Steve. 04:18.570 --> 04:21.900 And you can see that both of them have the same email address. 04:21.900 --> 04:25.950 And let me say you are done with the school system and you want to send email. 04:25.950 --> 04:28.080 So who are you going to send this email to. 04:28.110 --> 04:30.030 Are you going to send that to Id1. 04:30.060 --> 04:34.140 Are you going to send that to Id2 but have the same email addresses? 04:34.140 --> 04:37.170 And uh, that is a problem right in here. 04:37.860 --> 04:41.970 And you know that your email, no two persons can have the same email address. 04:41.970 --> 04:43.350 That is not possible. 04:43.350 --> 04:44.520 It can never happen. 04:44.520 --> 04:50.640 So in order to actually deal with this, you introduce a unique constraint to your email. 04:50.640 --> 04:56.340 And in doing that, you have protected your table and meaning that your table meets the standard. 04:56.790 --> 04:59.010 Now how do we add a unique constraint? 04:59.040 --> 05:00.390 Let's go ahead and check it out. 05:00.510 --> 05:03.600 So first of all, let everything I have right in here. 05:03.600 --> 05:07.680 And then I'll go ahead and uh, alter table. 05:07.680 --> 05:11.430 And the table name is going to be students. 05:12.660 --> 05:14.140 That is the name of a turtle. 05:14.140 --> 05:19.450 And then we're going to add a constraint. 05:19.900 --> 05:21.370 So add a constraint. 05:21.370 --> 05:28.030 And I'm going to give that constraint a name and say that the name is email underscore unique. 05:28.030 --> 05:31.090 So you can give this a name that is a unique. 05:32.560 --> 05:37.960 Or you go ahead and let the PostgreSQL give it a unique name by itself. 05:37.960 --> 05:43.510 So I'll go ahead and show you how to allow PostgreSQL to give that name by itself. 05:43.510 --> 05:48.850 So but this time around we give it an email underscore unique. 05:48.880 --> 05:49.480 All right. 05:49.480 --> 05:57.370 And then we'll go ahead and call unique and then pass in the column name which is our email. 05:57.400 --> 05:59.260 And put a semicolon at the end. 05:59.650 --> 06:00.310 All right. 06:00.460 --> 06:05.260 If we go ahead and execute this we're going to run into a problem because we have these two guys still 06:05.260 --> 06:05.740 here. 06:05.740 --> 06:08.590 So if these two people are still here we can be able to create this. 06:08.620 --> 06:09.760 Let's go ahead and check it out. 06:09.760 --> 06:15.700 So it says that this email address is duplicate could not create unique index. 06:15.800 --> 06:17.300 Email unique. 06:17.630 --> 06:22.700 So you have to first of all delete these two people, discord ahead and delete that. 06:23.510 --> 06:25.130 First of all, remove everything I have here. 06:25.520 --> 06:36.260 Then what I need to do is I'll go ahead and delete from our student. 06:37.310 --> 06:40.610 So delete from students where. 06:44.150 --> 06:45.800 We have these students are. 06:48.950 --> 06:54.590 Underscore ID is equal to 1001. 06:54.590 --> 06:57.830 So that is the last one we created 1001. 06:57.830 --> 07:00.530 Then we'll go ahead and execute this query. 07:00.530 --> 07:03.080 And it says delta one. 07:03.080 --> 07:08.690 And if we check that out so let me say uh go ahead and select. 07:11.690 --> 07:13.400 Select all from. 07:17.120 --> 07:18.050 Students. 07:19.670 --> 07:26.480 Where the email is equal to this. 07:27.200 --> 07:29.570 So go ahead press semicolon at the end. 07:29.660 --> 07:31.280 Let's go ahead on this query. 07:31.280 --> 07:37.040 And you can see we have just one because I have deleted the uh the last one we created. 07:37.040 --> 07:40.220 And now we can go back and create our table. 07:40.250 --> 07:44.840 If I go ahead and move back over here and create unique constraint again. 07:46.220 --> 07:47.330 So this is what we have. 07:47.360 --> 07:54.920 Now if I go ahead and execute this query you can see our table and our query returns successfully. 07:55.070 --> 08:00.050 And now we can go ahead and uh select all. 08:02.930 --> 08:09.440 Select all from uh students and put a semicolon at the end. 08:09.470 --> 08:11.240 And let's go ahead and execute this query. 08:11.240 --> 08:18.710 And uh under this email you can no longer have two persons that have the same email address. 08:18.740 --> 08:27.390 Now I'll go ahead and open up, our sketch show and let's go ahead and say backward slash D and then 08:27.390 --> 08:30.750 square head and check out the unique constraint students. 08:30.750 --> 08:31.950 And hit enter. 08:31.950 --> 08:37.200 And over here you can see we have our students underscore p key for primary key. 08:37.230 --> 08:43.200 And we have uh email underscore unique for unique constraints for the email. 08:43.740 --> 08:44.040 All right. 08:44.070 --> 08:46.890 In this way we are able to create this by ourself. 08:46.890 --> 08:55.920 We can also allow the uh we can also allow uh particular skill to assign a unique name to our unique 08:55.920 --> 08:56.670 constraint. 08:56.700 --> 08:57.420 Yes. 08:57.450 --> 09:01.620 We'll give this name email on a unique for by ourselves. 09:01.620 --> 09:03.810 What PostgreSQL can also do that. 09:03.840 --> 09:05.280 Let me go ahead and show you how to do that. 09:05.310 --> 09:09.660 But before we do that we need to drop that unique constraint. 09:10.800 --> 09:13.020 So let me go ahead and delete the written I have here. 09:13.020 --> 09:16.260 And then I'll go ahead and uh, alter the table. 09:16.260 --> 09:21.420 So go ahead under alter table under table name is students. 09:23.590 --> 09:26.890 And then we'll go ahead and drop constraint. 09:28.480 --> 09:31.420 So we'll go ahead and give the name of the table. 09:31.780 --> 09:34.600 The name which we added. 09:34.600 --> 09:39.040 And that name is in our email underscore unique. 09:39.040 --> 09:45.610 So go ahead and say it is an email underscore unique. 09:45.610 --> 09:47.230 So that is what we want to drop. 09:47.230 --> 09:50.290 And put a semicolon at the end and underscore head. 09:50.320 --> 09:53.350 And A is a q dot query. 09:53.380 --> 09:55.210 See your table. 09:55.210 --> 10:04.750 And if we go ahead and move back right here let's go ahead and put backslash D and uh say students and 10:04.750 --> 10:05.500 hit enter. 10:05.500 --> 10:09.010 You can see that we don't have any unique constraint. 10:09.010 --> 10:10.750 Again we only have the primary key. 10:10.780 --> 10:15.040 Now let's go ahead and allow PostgreSQL to give this a unique name. 10:15.040 --> 10:21.010 So in order to do that what I need to do is I'll go ahead and uh, alter this. 10:21.250 --> 10:23.950 So let me go ahead and alter this this way again. 10:24.790 --> 10:28.130 So the request I added drop or just. 10:28.160 --> 10:29.510 Simply add. 10:29.540 --> 10:30.080 Add. 10:30.080 --> 10:31.070 So what's that? 10:31.070 --> 10:32.690 Simple strings add constraint. 10:32.690 --> 10:33.620 And now I'm. 10:33.650 --> 10:35.240 Not going to give it a name. 10:35.270 --> 10:36.770 I'm just going to remove this. 10:36.770 --> 10:41.180 And uh I will now see add unique. 10:42.230 --> 10:46.160 So drop constraint and add unique. 10:46.190 --> 10:47.330 Add two different things. 10:47.330 --> 10:49.070 Then we're going to give the column. 10:49.070 --> 10:53.450 We want to add that unique constraint and put a semicolon at the end. 10:53.480 --> 10:55.310 Go ahead and execute this query. 10:55.310 --> 10:57.650 And you can see our table. 10:57.650 --> 10:59.030 And that is successful. 10:59.060 --> 11:03.170 Let's go ahead and open up execution and check out this again. 11:03.170 --> 11:10.160 So backward slash D then students I hit enter. 11:10.190 --> 11:13.640 You can see we have students underscore email underscore key. 11:13.670 --> 11:17.300 That is the unique constraint name given by PostgreSQL. 11:17.300 --> 11:20.570 And the very first one is stress underscore primary key. 11:20.600 --> 11:22.010 That is for the primary key. 11:22.010 --> 11:29.310 And the stress underscore email underscore key for the unique constraint for the email address. 11:29.310 --> 11:33.960 So in that way, two persons cannot have the same email address. 11:33.960 --> 11:40.380 Now let's go over to our Visual Studio and copy this again. 11:40.380 --> 11:42.330 Now we copy this again. 11:42.330 --> 11:44.820 And over here I'll go ahead and press this. 11:44.820 --> 11:49.080 And I want to add George back again just the way it is. 11:49.110 --> 11:54.030 And if I go ahead and that is the query, it says key email. 11:54.060 --> 11:59.670 This email address already exist duplicates key value pairs unique constraint. 11:59.760 --> 12:05.610 And it gives the name of the constraint that is strings underscore underscore key. 12:05.640 --> 12:08.730 Therefore, no two persons can have the same email address. 12:08.730 --> 12:13.140 In that way you have secured your table and that makes it professional. 12:13.140 --> 12:15.390 So why did I check it out and practice with it? 12:15.390 --> 12:18.690 And if you have any question, go ahead and use the question and answer section. 12:18.690 --> 12:21.240 And I'm going to get back to you as soon as possible. 12:21.270 --> 12:22.080 Thank you so much. 12:22.080 --> 12:25.290 And I'm going to see you in the next video lecture.