All right.
So let's go ahead and look at unique constraints in PostgreSQL.
So in PostgreSQL we have a dealt with the primary key.
And the primary key can only be added to one of your columns.
If I should go ahead and see add on I want to add these maybe to email.
And if I go ahead and run this code I'm going to have an error.
It serves multiple primary key for table students and not allowed.
Wow.
And now if you leave your email without any unique constraint, it means that two persons can have the
same email address.
That is not supposed to be.
All right.
So how can you be able to checkmate this so that two persons will not have the same email address?
Let's go ahead and see.
We're going to use a count to check email addresses that are of the same discourse head under select.
Oh, the scorpion on the.
Okay, now go ahead and select just Email and not all.
So select email and let's go ahead and count this email.
And I count this email from students.
And then we're going to group.
That email is indeed group by class group that email by email and a score head.
And check this out.
Now we have all this email.
And if you can check that out we have one and one.
And one of this email is to accept this email that is that is 300 and that is the no.
The ones without email.
So if you are going to use the having clause to check this out, you can go ahead and say group by email
and uh using the having clause.
So having count.
And then you go ahead and put text that is all and check emails that are greater than one.
Let's go ahead had a check and will have just 309.
But there's one thing that is still risky, and it means that our table is not protected, and that
is the unique constraint because someone can get to the email just like we have in this, uh, place
the square head on the brink of this.
So we have this guy.
We can also go ahead and remove the stress ID.
It's quite.
And remove the stress ID.
And I'm going to also remove this stress ID right in here.
And I can change the name of this guy to maybe change the name to George and the last name to be Steve.
But they have the same email address, is it not?
So I'm going to copy this.
Then go ahead and copy this.
And now go back right in here and I'm going to press that.
So now you can see insight into the first name, last name and so on.
But we have the same email.
So if I go ahead and execute this query you can see inside the zero one it means that I have having
started this and right now, if I should go ahead and, uh, go back right in here and check email addresses
that are greater than one, let's go ahead and check it out.
We have two.
So you can see that these actually exist two two times because we have that for uh, two presses.
So let's go ahead and uh go ahead and select this.
So I actually copy that and I'm going to say select.
Oh from students.
So select all from students where the email is equal to this.
And with a semicolon at the end.
And let's go ahead and check it out.
So.
So make sure that this is a single quotation mark or had.
And uh let's go ahead and remove this and put back to that single quotation mark.
So when M0 is equal to this and go ahead and execute this query and check it out.
So we have Mark on and we have George Steve.
And you can see that both of them have the same email address.
And let me say you are done with the school system and you want to send email.
So who are you going to send this email to.
Are you going to send that to Id1.
Are you going to send that to Id2 but have the same email addresses?
And uh, that is a problem right in here.
And you know that your email, no two persons can have the same email address.
That is not possible.
It can never happen.
So in order to actually deal with this, you introduce a unique constraint to your email.
And in doing that, you have protected your table and meaning that your table meets the standard.
Now how do we add a unique constraint?
Let's go ahead and check it out.
So first of all, let everything I have right in here.
And then I'll go ahead and uh, alter table.
And the table name is going to be students.
That is the name of a turtle.
And then we're going to add a constraint.
So add a constraint.
And I'm going to give that constraint a name and say that the name is email underscore unique.
So you can give this a name that is a unique.
Or you go ahead and let the PostgreSQL give it a unique name by itself.
So I'll go ahead and show you how to allow PostgreSQL to give that name by itself.
So but this time around we give it an email underscore unique.
All right.
And then we'll go ahead and call unique and then pass in the column name which is our email.
And put a semicolon at the end.
All right.
If we go ahead and execute this we're going to run into a problem because we have these two guys still
here.
So if these two people are still here we can be able to create this.
Let's go ahead and check it out.
So it says that this email address is duplicate could not create unique index.
Email unique.
So you have to first of all delete these two people, discord ahead and delete that.
First of all, remove everything I have here.
Then what I need to do is I'll go ahead and delete from our student.
So delete from students where.
We have these students are.
Underscore ID is equal to 1001.
So that is the last one we created 1001.
Then we'll go ahead and execute this query.
And it says delta one.
And if we check that out so let me say uh go ahead and select.
Select all from.
Students.
Where the email is equal to this.
So go ahead press semicolon at the end.
Let's go ahead on this query.
And you can see we have just one because I have deleted the uh the last one we created.
And now we can go back and create our table.
If I go ahead and move back over here and create unique constraint again.
So this is what we have.
Now if I go ahead and execute this query you can see our table and our query returns successfully.
And now we can go ahead and uh select all.
Select all from uh students and put a semicolon at the end.
And let's go ahead and execute this query.
And uh under this email you can no longer have two persons that have the same email address.
Now I'll go ahead and open up, our sketch show and let's go ahead and say backward slash D and then
square head and check out the unique constraint students.
And hit enter.
And over here you can see we have our students underscore p key for primary key.
And we have uh email underscore unique for unique constraints for the email.
All right.
In this way we are able to create this by ourself.
We can also allow the uh we can also allow uh particular skill to assign a unique name to our unique
constraint.
Yes.
We'll give this name email on a unique for by ourselves.
What PostgreSQL can also do that.
Let me go ahead and show you how to do that.
But before we do that we need to drop that unique constraint.
So let me go ahead and delete the written I have here.
And then I'll go ahead and uh, alter the table.
So go ahead under alter table under table name is students.
And then we'll go ahead and drop constraint.
So we'll go ahead and give the name of the table.
The name which we added.
And that name is in our email underscore unique.
So go ahead and say it is an email underscore unique.
So that is what we want to drop.
And put a semicolon at the end and underscore head.
And A is a q dot query.
See your table.
And if we go ahead and move back right here let's go ahead and put backslash D and uh say students and
hit enter.
You can see that we don't have any unique constraint.
Again we only have the primary key.
Now let's go ahead and allow PostgreSQL to give this a unique name.
So in order to do that what I need to do is I'll go ahead and uh, alter this.
So let me go ahead and alter this this way again.
So the request I added drop or just.
Simply add.
Add.
So what's that?
Simple strings add constraint.
And now I'm.
Not going to give it a name.
I'm just going to remove this.
And uh I will now see add unique.
So drop constraint and add unique.
Add two different things.
Then we're going to give the column.
We want to add that unique constraint and put a semicolon at the end.
Go ahead and execute this query.
And you can see our table.
And that is successful.
Let's go ahead and open up execution and check out this again.
So backward slash D then students I hit enter.
You can see we have students underscore email underscore key.
That is the unique constraint name given by PostgreSQL.
And the very first one is stress underscore primary key.
That is for the primary key.
And the stress underscore email underscore key for the unique constraint for the email address.
So in that way, two persons cannot have the same email address.
Now let's go over to our Visual Studio and copy this again.
Now we copy this again.
And over here I'll go ahead and press this.
And I want to add George back again just the way it is.
And if I go ahead and that is the query, it says key email.
This email address already exist duplicates key value pairs unique constraint.
And it gives the name of the constraint that is strings underscore underscore key.
Therefore, no two persons can have the same email address.
In that way you have secured your table and that makes it professional.
So why did I check it out and practice with it?
And if you have any question, go ahead and use the question and answer section.
And I'm going to get back to you as soon as possible.
Thank you so much.
And I'm going to see you in the next video lecture.