WEBVTT 00:07.670 --> 00:10.700 I welcome you back again to another video lecture on PostgreSQL. 00:10.700 --> 00:15.620 And in this video lecture, let's go ahead and learn how to create a table with constraint. 00:15.650 --> 00:18.470 So in the previous lecture we were able to create this table. 00:18.470 --> 00:20.510 And that is the student table right here. 00:20.600 --> 00:25.820 And the you can see that under nullable we have nothing specified. 00:25.820 --> 00:34.220 What happens here is that someone can go and create a student and inserts in maybe the student ID and 00:34.250 --> 00:42.710 the leave the first name and last name or even the GP and just go ahead and insert this data. 00:42.710 --> 00:48.860 So we want to create a table where the columns must always be filled up. 00:48.860 --> 00:54.260 So you must enter the first name, the last name, the GP and the enroll date with the student ID. 00:54.260 --> 00:58.310 So let's go ahead and make the student ID to be autoincrement. 00:58.340 --> 00:58.970 Okay. 00:59.000 --> 01:02.090 For that to be done, I'm going to show you how to do that. 01:02.100 --> 01:07.860 So if we're going to create a table with constraint, it means that you're going to insert every data 01:07.860 --> 01:11.010 that is required except the data you don't want. 01:11.160 --> 01:16.980 Or if a user has that you enter that or you go ahead and leave that. 01:17.010 --> 01:23.730 But in this case, uh, on the student table, a student must have an ID him, must have a first name 01:23.730 --> 01:25.860 and a last name and a GP. 01:26.010 --> 01:29.130 And the in road dates the data strings in road. 01:29.130 --> 01:31.380 We cannot add something like an email. 01:31.410 --> 01:33.750 Then not every student might have an email. 01:33.750 --> 01:36.330 So we can make that to be optional. 01:36.360 --> 01:38.820 But every other thing must not be null. 01:38.850 --> 01:43.230 In order to make of that, let me go ahead and show you how to create such table. 01:43.230 --> 01:48.450 So first of all, I'll go right in here and uh, create table. 01:48.450 --> 01:53.250 And if I were to do that, I'll have to drop my students table because this table is already existing. 01:53.250 --> 01:55.140 And, uh, let me go ahead and drop it first. 01:55.140 --> 01:57.120 Go ahead and drop a table. 01:57.120 --> 02:02.850 And, uh, the table I want to drop is, uh, students and put a semicolon at the end and hit enter, 02:02.880 --> 02:04.250 and until it drops. 02:04.280 --> 02:09.020 Now, if I should go right in here and it's the cutest query, check it out. 02:09.050 --> 02:12.950 It says error ratio students does not exist. 02:12.980 --> 02:15.320 But this actually existed before. 02:15.350 --> 02:24.230 Now, if I should go back writing here and check backward slash D and hit enter, you can see did not 02:24.230 --> 02:27.500 find any ratios because I have dropped this table. 02:27.500 --> 02:31.400 And this table no longer exist so that someone would drop a database. 02:31.850 --> 02:33.440 I'll also drop a table. 02:33.470 --> 02:33.950 All right. 02:33.950 --> 02:38.750 And for us to create a new table or I need to do is to go ahead and say create. 02:38.750 --> 02:41.300 And then I'm going to say table. 02:41.300 --> 02:44.480 And then the name of the table is students. 02:44.480 --> 02:47.750 So this is exactly the same thing we did right in there. 02:47.780 --> 02:48.530 All right. 02:48.830 --> 02:50.780 So just exactly the same thing. 02:50.780 --> 02:53.750 You can go ahead and copy whatever you have here. 02:53.750 --> 02:58.910 And let me go ahead and say the first one is a student ID. 02:59.180 --> 03:06.210 You can also go via to student ID and copy that and go over here and test it. 03:06.240 --> 03:10.620 Now, I don't want this to be no. 03:10.650 --> 03:16.140 Then under here I'm going to say I want this set to auto increment. 03:16.170 --> 03:20.430 Let me take you back to this point where we have the data types. 03:20.430 --> 03:24.510 And under the data types we have a data type called Big Syria. 03:24.510 --> 03:28.980 And Big Syria is auto incrementing it's byte integer. 03:28.980 --> 03:37.050 So in this way we're going to use Big Syria to make this, uh, student ID to auto increment in PostgreSQL. 03:37.050 --> 03:38.580 So go ahead and minimize this. 03:38.580 --> 03:47.730 So instead of using integer overhead and use uh, let me say big Syria okay. 03:47.760 --> 03:50.370 And uh is not not norm. 03:51.240 --> 03:56.550 And then I'm going to make this to be the primary key array. 03:56.550 --> 03:59.430 So this is my primary key is not null and is big. 03:59.430 --> 04:01.560 Syria means it's going to auto increment. 04:01.560 --> 04:04.470 Always go ahead and hit enter and move to the next. 04:04.470 --> 04:09.980 And then ready to do is to go over to first name a warhead and copy the first name. 04:10.010 --> 04:10.850 Right. 04:10.880 --> 04:15.200 So copy the first name, come down here and paste that. 04:15.230 --> 04:18.920 Now I'm going to make the first name not to be known. 04:18.950 --> 04:24.110 Or I need to do is to say not know and put a comma. 04:24.110 --> 04:29.360 And then if you want to copy this from the SQL shell, you just copy once. 04:29.360 --> 04:32.570 If you copy that twice you're going to have an error. 04:32.600 --> 04:32.990 Right. 04:32.990 --> 04:37.100 So I just copy this once, I'll go right in here and I'm going to paste the last name. 04:37.100 --> 04:41.360 And I'm going to say not know right. 04:41.360 --> 04:49.160 So why I do the copy and paste is because it is easier for me to actually do that than typing that each 04:49.160 --> 04:49.490 time. 04:49.490 --> 04:55.070 So go ahead and change this to also not know. 04:57.890 --> 04:58.550 All right. 04:58.580 --> 05:00.260 Not know the comma. 05:00.260 --> 05:09.060 And the next one is the in row date because you must have the date row Roscoe, then go over here and, 05:09.060 --> 05:12.630 uh, press that and then enrolled. 05:12.630 --> 05:16.410 It is also not. 05:16.620 --> 05:17.520 No. 05:17.520 --> 05:18.270 So let's go. 05:18.720 --> 05:19.230 Come on. 05:19.260 --> 05:23.250 Now, you might ask me, when are we going to have a no? 05:23.280 --> 05:24.000 Another one. 05:24.000 --> 05:26.250 Just like I said, my students email. 05:26.250 --> 05:29.400 So for the students email, not every student must have an email address. 05:29.400 --> 05:31.140 So we can make that to be optional. 05:31.140 --> 05:35.910 And for us to make that optional we go ahead and we see email. 05:35.910 --> 05:42.870 And then the data type for email is going to be varchar because it contains characters but numbers and 05:42.900 --> 05:43.920 the alphabet. 05:43.950 --> 05:44.520 Okay. 05:44.550 --> 05:49.650 And let's make this to be like a 100 or 50. 05:49.800 --> 05:51.300 Let's try to make that to be 100. 05:51.300 --> 05:52.140 That is okay. 05:52.140 --> 05:55.590 And then go ahead and close this and put a semicolon at the end. 05:55.620 --> 05:58.830 Now if I go ahead and hit enter I'm going to create this table. 05:58.860 --> 05:59.970 Go ahead and hit enter. 05:59.970 --> 06:02.100 And the table is now created. 06:02.100 --> 06:06.870 So let's go back here and we're going to refresh this. 06:07.290 --> 06:12.400 Go ahead and refresh this and I'll go ahead and execute this query again. 06:12.400 --> 06:15.280 And you can see that the error is gone. 06:15.280 --> 06:20.020 And under here I have all these okay. 06:20.050 --> 06:24.070 We have the primary key as the students ID and this big INT. 06:24.100 --> 06:34.690 Now if I go back to school I'll go right in here and just do backward slash D and hit enter I have my 06:34.690 --> 06:42.070 table now I have this table created and I have another one called two names underscore two names underscore 06:42.100 --> 06:44.200 ID underscore six. 06:44.230 --> 06:46.300 This is the primary key. 06:46.330 --> 06:46.930 All right. 06:46.930 --> 06:47.980 This is the primary key. 06:47.980 --> 06:50.350 And this autoincrement okay. 06:50.380 --> 06:52.060 And this does that sequentially. 06:52.090 --> 06:57.520 Now I can go ahead and say backward slash D and the overhead. 06:57.640 --> 07:03.910 And I give the table name to be students and hit enter. 07:03.910 --> 07:09.570 And then you can see I have a student ID bigint not known. 07:09.600 --> 07:11.760 Next vowel strains are. 07:11.790 --> 07:12.450 Underscore. 07:12.810 --> 07:14.100 Underscore ID. 07:14.130 --> 07:15.390 Underscore sequence. 07:15.420 --> 07:18.030 This is the primary key okay. 07:18.060 --> 07:20.730 Then the first name is not null. 07:20.760 --> 07:22.170 The last name is not null. 07:22.200 --> 07:23.640 GP is not null. 07:23.670 --> 07:24.090 Erode. 07:24.090 --> 07:25.140 That's not null. 07:25.170 --> 07:28.770 Then the IMO is notable okay. 07:28.800 --> 07:36.690 Because this IMO can either be there or not there, but every other one must be there. 07:36.720 --> 07:37.260 Okay. 07:37.290 --> 07:41.670 So these two lines are underscore primary key. 07:41.700 --> 07:45.420 This primary key is the stress ID that is the primary key. 07:45.420 --> 07:52.080 So goianapolis Dan that is a way to create a table with constraint in PostgreSQL. 07:52.110 --> 07:52.950 Aurora is cool. 07:52.950 --> 07:54.090 So put it down. 07:54.090 --> 07:57.900 And if you have any question you use the question and answer section. 07:57.900 --> 08:00.870 And then I'm going to get back to you as soon as possible. 08:00.900 --> 08:04.380 Thank you and see you in the next video lecture.