I welcome you back again to another video lecture on PostgreSQL.
And in this video lecture, let's go ahead and learn how to create a table with constraint.
So in the previous lecture we were able to create this table.
And that is the student table right here.
And the you can see that under nullable we have nothing specified.
What happens here is that someone can go and create a student and inserts in maybe the student ID and
the leave the first name and last name or even the GP and just go ahead and insert this data.
So we want to create a table where the columns must always be filled up.
So you must enter the first name, the last name, the GP and the enroll date with the student ID.
So let's go ahead and make the student ID to be autoincrement.
Okay.
For that to be done, I'm going to show you how to do that.
So if we're going to create a table with constraint, it means that you're going to insert every data
that is required except the data you don't want.
Or if a user has that you enter that or you go ahead and leave that.
But in this case, uh, on the student table, a student must have an ID him, must have a first name
and a last name and a GP.
And the in road dates the data strings in road.
We cannot add something like an email.
Then not every student might have an email.
So we can make that to be optional.
But every other thing must not be null.
In order to make of that, let me go ahead and show you how to create such table.
So first of all, I'll go right in here and uh, create table.
And if I were to do that, I'll have to drop my students table because this table is already existing.
And, uh, let me go ahead and drop it first.
Go ahead and drop a table.
And, uh, the table I want to drop is, uh, students and put a semicolon at the end and hit enter,
and until it drops.
Now, if I should go right in here and it's the cutest query, check it out.
It says error ratio students does not exist.
But this actually existed before.
Now, if I should go back writing here and check backward slash D and hit enter, you can see did not
find any ratios because I have dropped this table.
And this table no longer exist so that someone would drop a database.
I'll also drop a table.
All right.
And for us to create a new table or I need to do is to go ahead and say create.
And then I'm going to say table.
And then the name of the table is students.
So this is exactly the same thing we did right in there.
All right.
So just exactly the same thing.
You can go ahead and copy whatever you have here.
And let me go ahead and say the first one is a student ID.
You can also go via to student ID and copy that and go over here and test it.
Now, I don't want this to be no.
Then under here I'm going to say I want this set to auto increment.
Let me take you back to this point where we have the data types.
And under the data types we have a data type called Big Syria.
And Big Syria is auto incrementing it's byte integer.
So in this way we're going to use Big Syria to make this, uh, student ID to auto increment in PostgreSQL.
So go ahead and minimize this.
So instead of using integer overhead and use uh, let me say big Syria okay.
And uh is not not norm.
And then I'm going to make this to be the primary key array.
So this is my primary key is not null and is big.
Syria means it's going to auto increment.
Always go ahead and hit enter and move to the next.
And then ready to do is to go over to first name a warhead and copy the first name.
Right.
So copy the first name, come down here and paste that.
Now I'm going to make the first name not to be known.
Or I need to do is to say not know and put a comma.
And then if you want to copy this from the SQL shell, you just copy once.
If you copy that twice you're going to have an error.
Right.
So I just copy this once, I'll go right in here and I'm going to paste the last name.
And I'm going to say not know right.
So why I do the copy and paste is because it is easier for me to actually do that than typing that each
time.
So go ahead and change this to also not know.
All right.
Not know the comma.
And the next one is the in row date because you must have the date row Roscoe, then go over here and,
uh, press that and then enrolled.
It is also not.
No.
So let's go.
Come on.
Now, you might ask me, when are we going to have a no?
Another one.
Just like I said, my students email.
So for the students email, not every student must have an email address.
So we can make that to be optional.
And for us to make that optional we go ahead and we see email.
And then the data type for email is going to be varchar because it contains characters but numbers and
the alphabet.
Okay.
And let's make this to be like a 100 or 50.
Let's try to make that to be 100.
That is okay.
And then go ahead and close this and put a semicolon at the end.
Now if I go ahead and hit enter I'm going to create this table.
Go ahead and hit enter.
And the table is now created.
So let's go back here and we're going to refresh this.
Go ahead and refresh this and I'll go ahead and execute this query again.
And you can see that the error is gone.
And under here I have all these okay.
We have the primary key as the students ID and this big INT.
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
table now I have this table created and I have another one called two names underscore two names underscore
ID underscore six.
This is the primary key.
All right.
This is the primary key.
And this autoincrement okay.
And this does that sequentially.
Now I can go ahead and say backward slash D and the overhead.
And I give the table name to be students and hit enter.
And then you can see I have a student ID bigint not known.
Next vowel strains are.
Underscore.
Underscore ID.
Underscore sequence.
This is the primary key okay.
Then the first name is not null.
The last name is not null.
GP is not null.
Erode.
That's not null.
Then the IMO is notable okay.
Because this IMO can either be there or not there, but every other one must be there.
Okay.
So these two lines are underscore primary key.
This primary key is the stress ID that is the primary key.
So goianapolis Dan that is a way to create a table with constraint in PostgreSQL.
Aurora is cool.
So put it down.
And if you have any question you use the question and answer section.
And then I'm going to get back to you as soon as possible.
Thank you and see you in the next video lecture.