WEBVTT 00:07.550 --> 00:08.120 All right. 00:08.120 --> 00:12.350 So in the previous lecture we are able to look at days. 00:12.350 --> 00:15.110 Now I want to introduce you to something very important. 00:15.110 --> 00:19.880 And do not just actually very important but very necessary. 00:20.270 --> 00:22.670 See I have this right in here. 00:22.670 --> 00:29.660 And if I should go ahead and insert this again, I'm going to get an error over here and check it out. 00:29.660 --> 00:31.010 So there is an error. 00:31.010 --> 00:34.910 Right now the error says already exists. 00:34.910 --> 00:36.860 That is the email address. 00:36.860 --> 00:39.830 And what if I go ahead and add in. 00:39.860 --> 00:52.880 Let me say students underscore ID and then I go right over here and uh add the maybe that is 1006. 00:52.910 --> 00:55.070 I think that is the what we have there. 00:55.070 --> 00:57.290 And if we go ahead and run this check it out. 00:57.290 --> 01:01.700 So it says select ID 1006 already exists. 01:01.730 --> 01:10.420 Duplicate a key and it violates the unique constraints student's key and the previous one is the key, 01:10.450 --> 01:12.310 that is for the email. 01:12.310 --> 01:19.390 If we remove this and check the email is correct and check it out and this student's email key. 01:20.140 --> 01:20.680 All right. 01:20.710 --> 01:22.630 Now there is no problem. 01:22.630 --> 01:27.820 I want us to actually not display this error anymore. 01:27.820 --> 01:30.580 So we're going to use what is called on conflict. 01:30.610 --> 01:32.080 Do nothing. 01:32.470 --> 01:33.130 Yes. 01:33.160 --> 01:35.290 On conflict do nothing. 01:35.290 --> 01:37.480 If I want to do that like this. 01:37.480 --> 01:43.660 First name them say students underscore ID. 01:43.960 --> 01:51.430 And if I go ahead and put in the ID 1006 which has already existed, all I need to do is go back over 01:51.430 --> 01:54.940 here, remove this, and I can continue on that same line. 01:54.940 --> 01:59.770 That is why I remove that quotation mark, the semicolon at the end. 02:00.010 --> 02:04.420 So the reason why I remove the semicolon at the end is to continue from the same line. 02:04.450 --> 02:11.440 All I need to do is to go ahead and say on conflict, okay, notice on conflicts are okay. 02:11.470 --> 02:14.770 So on Conflict. 02:14.800 --> 02:19.000 Then I'll go ahead and pass in. 02:19.030 --> 02:26.650 If that is for the stress ID, I'm going to say students underscore ID all right. 02:26.650 --> 02:31.120 And then I'll go ahead and say do nothing. 02:31.120 --> 02:33.580 So whenever there is a conflict just do nothing. 02:33.610 --> 02:34.120 All right. 02:34.120 --> 02:38.800 And it's quite an easy query now nothing actually happened. 02:39.100 --> 02:43.120 And that nothing was actually added the Squire and check it out. 02:43.240 --> 02:48.520 Done here would not have any similar, uh, ID right in here. 02:48.520 --> 02:49.780 We just have only 100. 02:50.080 --> 02:53.170 That means nothing actually happened and no error was shown. 02:53.200 --> 02:53.860 All right. 02:53.860 --> 03:02.200 And, uh, if we go over here and also check it out, if we go ahead and say we want to select everything 03:02.200 --> 03:06.250 here and do the same, that means it will still work out fine. 03:06.250 --> 03:10.030 So copy this and then let's go over to SQL. 03:10.030 --> 03:13.330 And you can actually go ahead and paste this. 03:13.330 --> 03:16.900 And then go ahead and hit enter. 03:16.900 --> 03:22.200 And you can see inside there is that inside there there is no error. 03:23.040 --> 03:25.740 But if there is no no conflict, do not. 03:25.740 --> 03:29.400 Then you're going to see an error down here okay. 03:29.430 --> 03:33.150 Now you can also do that for email. 03:33.180 --> 03:35.520 Just go ahead and remove this for students. 03:35.550 --> 03:40.740 I'll go ahead and remove the stress ID and the let's go ahead and run this again. 03:40.740 --> 03:46.620 We have that's why less distress email because we have a unique constraint for the email. 03:46.620 --> 03:48.300 So I'll just go right inside here. 03:48.300 --> 03:54.180 And I'm going to pass in the word I say email and run this and check it out. 03:54.210 --> 03:57.030 Nothing actually happened okay. 03:57.060 --> 03:59.190 So you can see what happens on the email. 03:59.220 --> 04:06.120 But if you go ahead and use something like first name or last name or the gender, because the gender 04:06.120 --> 04:10.320 is just a check constraint is not a unique constraint or a primary key. 04:10.320 --> 04:17.490 So the enroll date and country, if you try to put a maybe first name, let's go ahead and say we have 04:17.520 --> 04:20.880 a first underscore name. 04:20.880 --> 04:27.540 And if you run this you're going to get an error because it says there is no unique or exclusion constraint 04:27.570 --> 04:29.910 matching on conflict specification. 04:29.910 --> 04:32.130 So the on conflict specification. 04:32.430 --> 04:34.440 First name is not going to work. 04:34.470 --> 04:36.090 And let's go ahead and check with gender. 04:36.090 --> 04:38.460 And let's go ahead and run this and check it out. 04:38.550 --> 04:43.770 It says there is no unique or exclusion constraint matching on conflict specification. 04:43.770 --> 04:49.680 So if you run this many times you can see that gender is not part of the constraint are talking about. 04:49.680 --> 04:55.890 So the check constraint is not included but the unique constraint and the primary key is included. 04:55.890 --> 05:06.000 So if you go ahead and uh add the unique constraint uh email and uh students ID is going to work out 05:06.000 --> 05:06.390 fine. 05:06.390 --> 05:13.260 So the no conflicts do nothing actually helps us to keep moving without throwing an exception or an 05:13.260 --> 05:14.700 error right in here. 05:14.700 --> 05:15.690 I hope that is cool. 05:15.690 --> 05:17.130 So go ahead and practice with it. 05:17.160 --> 05:20.550 And if you have any questions, go ahead and use the question and answer section. 05:20.550 --> 05:23.190 And I'm going to get back to you as soon as possible. 05:23.190 --> 05:24.180 Thank you so much. 05:24.180 --> 05:27.570 And I'm going to see you in the next video lecture.