Welcome back, everyone.
In this section, we're going to learn about more constraints that we can use when setting up a table.
So when I say constraints, I'm referring to things like not null, right?
We saw what that meant.
If we use not null on a column, it can't be null.
That is a constraint.
We also saw that we could use primary key.
There's another example.
And the next one of those constraints we're going to learn is called unique.
And it's exactly what it sounds like.
It's a way of setting up and enforcing that uniqueness of a particular column.
So in this table companies, there's a lot of stuff going on, but all that really matters is right
here.
Phone is a bar chart, 15 characters at most.
It's not null, it cannot be null and it has to be unique.
So let me show you what happens if we set up a table with this unique constraint.
So let's do a simpler one.
Just create table contacts.
So we'll have a contact table and it's going to be super simple.
We'll just have a name which is var char, not null, and let's have that be, I don't know, 100 characters.
And then we'll also have a phone, which is for Char.
Sure, we'll say 15 and we'll say it also can't be null and it has to be unique.
Let's try running this OC and I'm going to do an insert into contacts.
Well do name and phone values.
And let's start with a simple one.
Like, I don't know, Billy Bob is our contact name.
Phone number is 87812134, five, five.
So that's our first person that we will insert or our first contact.
Let's just make sure that works at my semicolon.
Seems to have worked.
Of course we'll do select star from contacts and nothing is at all changed or unexpected.
However, if I now go and try and insert a couple more, let's insert into contacts.
We can have the same name.
Billy Bob.
It doesn't matter, but let's do another name.
How about like Timmy, Tim, Tim.
And then we keep the same phone number and I insert this.
Oh, no.
We get an error says duplicate entry.
Eight, seven, eight.
One, two, one, three, four, five, five for this key phone.
So it's maybe not the most helpful message.
I mean, it's clear enough what's going on.
It says there's a duplicate value and that's fine.
It's not really this it's not like an end user facing error that says that username or that phone number
is already taken or something like that.
Instead, it's just nitty gritty duplicate entry for this key.
But it works.
If I select Star from Contacts, we'll see that we only have one person in there, one contact with
that phone number.
So Billy Bob, like I said, that can be duplicated.
Billy Bob But if I change this just by one digit, let's say that's the phone number and I enter that.
No problem.
Select star from contact's name.
It's Billy Bob twice.
But phone has to be unique.
So that's the unique constraint.
We can add it on to any of the fields.
We want to define.
Something like Primary key, as we already know, has to be unique.
So you don't want to say primary key and unique for the same column in the same way.
That not null is redundant for a primary key because primary keys are required, they can't be null
and they must be unique.
Okay.