WEBVTT 00:07.610 --> 00:08.090 All right. 00:08.090 --> 00:12.410 So let's go ahead and look at the check constraint in PostgreSQL. 00:12.560 --> 00:20.540 So the check constraint helps us to be able to determine the values we add into a column. 00:20.900 --> 00:21.440 All right. 00:21.440 --> 00:23.150 We have this gender. 00:23.150 --> 00:25.490 And we remember we generated this. 00:25.520 --> 00:29.150 It was auto generated for us not just by us. 00:29.570 --> 00:36.500 And here we can see we have things like a female we have big or by gender. 00:36.500 --> 00:39.080 And we have a full agenda. 00:39.080 --> 00:44.810 We have non-binary and we have gender cue and so many more. 00:45.650 --> 00:53.510 Now what I need to do is let's go ahead and check how many of these genders we have. 00:53.540 --> 01:02.660 What I need to do is to go in and say, select a distinct and the distinct keyword we have asked to 01:02.690 --> 01:08.460 actually differentiate the genders we have so select distinct gender from. 01:10.830 --> 01:13.980 Students because we didn't generate this by ourself. 01:13.980 --> 01:16.350 And then go ahead and execute this. 01:16.380 --> 01:20.550 And you can see we have about eight different genders. 01:21.540 --> 01:25.470 So I'm going to delete whatever it is. 01:25.470 --> 01:27.570 Not either male or female. 01:27.630 --> 01:31.350 We are going to be left with either male or female. 01:31.380 --> 01:37.590 You can have bigender genderfluid or non-binary only gender and so on. 01:37.590 --> 01:42.780 I don't know if this is actually the same with you, but this is what I have when I uploaded this and 01:42.780 --> 01:44.490 it doesn't suppose to be so. 01:44.520 --> 01:50.850 And you have to restrain people from entry, just only either male or female. 01:50.850 --> 01:54.180 So I'm going to use this check constraint to actually do this. 01:54.180 --> 01:59.940 So we go ahead and uh delete the following. 01:59.940 --> 02:03.270 That is our gender Q and so on. 02:03.270 --> 02:11.190 So I'll go ahead and uh, I'm going to skip this b roll so that I can always use this to see what genders 02:11.190 --> 02:12.060 that are left. 02:12.060 --> 02:19.590 So while I say delete key word and uh from students. 02:22.890 --> 02:26.310 Where gender. 02:28.650 --> 02:29.970 Is equal to. 02:29.970 --> 02:35.070 So I'm going to copy the very first one here that is uh, go ahead and copy this. 02:35.640 --> 02:40.500 And I'm going to press that here and put a semicolon at the end and execute this query. 02:40.530 --> 02:42.390 Now I have removed the very first one. 02:42.390 --> 02:44.220 So I'm going to copy the second one. 02:44.460 --> 02:46.200 And I'm going to replace that. 02:46.200 --> 02:47.670 And that is that. 02:47.670 --> 02:49.920 And I'm going to copy non-binary. 02:51.180 --> 02:54.960 And then I'm going to put that and delete it. 02:54.960 --> 02:59.190 So the reason why I left it select distinct is to always see what them is. 02:59.220 --> 02:59.970 All right. 02:59.970 --> 03:07.860 So go ahead and copy this and go ahead and paste this here and execute the query. 03:07.910 --> 03:12.980 and I'm going to copy this agenda and I'm going to press that. 03:13.010 --> 03:14.330 Execute the query. 03:14.330 --> 03:16.370 And I remain gender fluid. 03:16.400 --> 03:19.520 I'm going to also delete that. 03:21.170 --> 03:21.440 All right. 03:21.470 --> 03:24.710 So I'm now left with a female and male. 03:24.710 --> 03:29.900 So for us to be sure let's go ahead and uh also select everything. 03:29.930 --> 03:38.540 Go ahead and select uh all from students. 03:38.570 --> 03:41.900 I put a semicolon at the end and go ahead and check it out. 03:41.900 --> 03:47.420 So we're now left with 898 out of 898. 03:47.450 --> 03:52.190 Therefore, we have eliminated every order once and under the gender. 03:52.190 --> 03:56.240 We are now left with, uh, male and female. 03:56.660 --> 04:01.970 But what happens is that if we go over to Visual Studio. 04:01.970 --> 04:04.400 So one can actually go ahead and copy this. 04:04.430 --> 04:09.230 Let's go ahead and copy this and then go back in here. 04:09.230 --> 04:15.110 And I'm going to put this down so that it's going to always show. 04:15.140 --> 04:19.580 And, uh, the value here can be anything. 04:19.610 --> 04:23.660 Instead of no a computer only. 04:23.840 --> 04:24.260 Okay. 04:24.260 --> 04:24.800 Let me see. 04:25.010 --> 04:26.240 Fully male. 04:26.270 --> 04:34.280 And if I go ahead and, uh, run this okay, I'm going to change the email because I've already made 04:34.280 --> 04:36.050 this email to be unique constraint. 04:36.050 --> 04:38.000 So I had to change this email. 04:38.000 --> 04:44.870 And I'm going to say that this email is a digestive at businesswire.com. 04:44.900 --> 04:46.460 I'll go ahead and execute this query. 04:46.490 --> 04:48.590 Now get down here. 04:51.050 --> 04:55.460 And down here you can see that, uh, the gender still email. 04:55.460 --> 05:02.540 And if we go over and check this out go ahead and check this out. 05:02.570 --> 05:04.190 Go ahead and delete everything here. 05:04.190 --> 05:05.540 And it's the cutest query. 05:05.570 --> 05:13.050 You can see that, uh, Palermo is still there because we just added that now, and I don't want anybody 05:13.050 --> 05:16.470 to enter anything that is not male and female. 05:16.500 --> 05:18.060 Go ahead and put this. 05:18.090 --> 05:20.130 I'll go ahead and delete this. 05:21.960 --> 05:24.510 So go ahead and execute this query. 05:24.510 --> 05:28.650 And now we're now left with our male and female only. 05:28.650 --> 05:33.840 So if we go back to select uh let's go ahead and uh. 05:36.930 --> 05:40.890 Select all from. 05:43.710 --> 05:44.640 Students. 05:44.910 --> 05:47.040 And there is a query. 05:47.040 --> 05:53.250 If you drag this down we have deleted that which is sorted and is no longer there. 05:53.400 --> 05:58.080 Now let's go ahead and use a check constraint to prevent people from entering any number. 05:58.080 --> 06:01.230 That's any gender that is not male or female. 06:01.230 --> 06:08.100 And if I want to do that I'll go ahead and see other table. 06:08.340 --> 06:11.760 And the table name is uh, students. 06:11.760 --> 06:14.340 And then I'll go ahead and add a. 06:16.020 --> 06:17.490 constraint. 06:17.820 --> 06:24.690 And now I can give my constraint any name, or at least leave it so that PostgreSQL will give it a name. 06:24.720 --> 06:25.200 All right. 06:25.230 --> 06:28.050 So I'll go ahead and say add a. 06:30.150 --> 06:30.960 Constraint. 06:30.960 --> 06:33.660 And I can say that is a. 06:36.240 --> 06:39.510 Students underscore gender. 06:39.510 --> 06:41.250 So that is the name I give it. 06:41.250 --> 06:43.440 And then I say check this. 06:43.440 --> 06:46.650 And there we see gender. 06:49.050 --> 06:50.910 Is equal to. 06:54.240 --> 06:58.860 Mu or gender. 07:02.610 --> 07:04.080 Is equal to. 07:06.150 --> 07:06.810 Female. 07:07.410 --> 07:10.080 So you are in a male or female. 07:10.110 --> 07:11.610 So go ahead and drag this down. 07:11.610 --> 07:19.540 So other students have constraints and are required and I see strength on the agenda, that is the name 07:19.540 --> 07:20.560 of the constraint. 07:20.560 --> 07:27.130 And that can also bring down these say check gender equal to male or gender equal to female, or put 07:27.160 --> 07:28.930 a semicolon at the end of days. 07:28.930 --> 07:34.450 Now we can go ahead and execute this query and our table or task successfully. 07:34.480 --> 07:39.610 Now I'll go ahead and now select all. 07:42.820 --> 07:46.690 From students up to semicolon. 07:46.810 --> 07:48.520 And now this is our table. 07:48.550 --> 07:51.610 Now let me go ahead and move over to Visual Studio. 07:51.610 --> 07:53.680 And I'm going to copy this again. 07:54.040 --> 07:55.750 Going to copy this one more time. 07:55.750 --> 07:57.460 Let's try to add that. 07:57.460 --> 07:59.950 And down here I'm going to press that. 07:59.950 --> 08:04.990 And now remember we just copied what we just inserted previously. 08:04.990 --> 08:06.250 And we have deleted it. 08:06.250 --> 08:06.520 Now. 08:06.520 --> 08:12.880 Want to retry it again and execute this query and says email. 08:14.950 --> 08:16.420 Already exist. 08:16.420 --> 08:19.150 And uh okay. 08:19.160 --> 08:21.920 So we have ordered this email previously. 08:21.920 --> 08:25.490 So the email constraint is what prevents that. 08:25.490 --> 08:30.140 And now I'll go ahead and uh change the email just like we did. 08:30.170 --> 08:38.210 And that is a gist of now I'll go ahead and add it to be fully email. 08:38.240 --> 08:38.780 All right. 08:38.810 --> 08:39.260 So good. 08:39.260 --> 08:42.470 We have done a we have done the email constraint. 08:42.470 --> 08:46.040 And uh, we added a unique constraint to our email. 08:46.040 --> 08:47.810 So we're not able to add it. 08:47.810 --> 08:49.370 But now we have changed the email. 08:49.370 --> 08:53.720 And now we change the gender to pull email. 08:53.720 --> 08:56.150 And now execute this query and check it out. 08:56.150 --> 09:00.890 So it says error offering contains these check constraints. 09:01.370 --> 09:02.720 Uh students gender. 09:02.750 --> 09:05.900 So you can see that the students gender. 09:06.050 --> 09:11.060 It says new row for racial students violates check constraints students gender. 09:11.060 --> 09:20.270 And if I go ahead and, uh, open up our SQL show course head and uh, of course d students and hit 09:20.300 --> 09:22.820 enter, we have a check constraint. 09:22.880 --> 09:23.360 Status. 09:23.360 --> 09:23.960 Gender. 09:23.960 --> 09:26.990 And the check is equal to gender. 09:27.020 --> 09:27.800 Equal to male. 09:27.830 --> 09:29.360 Or gender equal to female. 09:29.360 --> 09:33.080 So you can no longer add anything that is not either male or female. 09:33.110 --> 09:34.880 Now go ahead and remove the pulley. 09:34.880 --> 09:40.820 And I'm going to be left with male and female is to Steve at Businesswire.com. 09:40.820 --> 09:43.100 And go ahead and try to add it. 09:43.130 --> 09:46.820 Now that we have male as the gender and let's check it out. 09:47.570 --> 09:50.960 So we have been able to add it and let's drag this down. 09:50.960 --> 09:57.230 And down here you can see George, Steve G, Steve Dahl Businesswire.com and male. 09:57.230 --> 10:00.950 So you can no longer add anything that is not male or female. 10:00.950 --> 10:03.620 And that is very very beautiful. 10:03.650 --> 10:05.360 Well go ahead and check it out. 10:05.390 --> 10:12.110 Try it out and uh, try to implement that and have a force out table to accept only either male or female 10:12.110 --> 10:12.830 gender. 10:12.830 --> 10:14.120 That is so beautiful. 10:14.120 --> 10:16.220 So I'm going to see you in the next video. 10:16.220 --> 10:17.930 Thank you and see you then.