The last thing I'll show you around altar table is how we can add constraints and drop constraints after
a table is created.
So like I said, there's a lot more to alter table.
I'm giving you the most important pieces renaming columns, dropping columns, adding columns, modifying
columns, adding and dropping constraints.
But there's other things that you could do.
But if we find alter or rather add constraint right here, we have different options.
If we're adding a check constraint versus adding something like primary key constraint, unique foreign
key.
Once we talk about that, but let's just do a simple add a check constraint.
So remember, this here means it's optional whenever we have those brackets.
What we can do is add a constraint, give it a name check expression.
It looks like a lot.
But let's let's do a simple example.
Let's say that we have our house's table.
Let's make sure that purchase price has to be positive.
And I've already made that house's table select star from houses.
Right.
I have described houses and at the moment I could insert a negative purchase price, but if I add a
constraint so I would do alter table.
And then add constraint.
Again, reference the syntax if we ever have a question constraint is actually optional.
I could just say add check, but I'm going to give it a name, so I'm going to say add constraint and
then positive.
Price.
Technically purchase price.
So p price.
Sure.
Check.
And then what is the actual check we're doing?
This would look the same as if we were just doing this when we created the table.
But I'm going to say purchase price.
Oops.
Didn't mean to delete that.
Purchase price has to be greater than or equal to zero.
I guess you could buy it for zero, but you can't buy it for negative dollars.
Okay, so let's try running that.
And what is?
Do I have a syntax error somewhere?
Let's verify.
Alter table.
Oh, I didn't say what table.
Duh.
I hope you saw that before me.
If not, it's okay.
I didn't see it.
Oh, and it doesn't like my quotes either.
There.
Anyway, let's create it.
Now.
Let's describe houses again.
We're not going to see the constraint here.
But if I try and insert into House's purchase price, sale, price, we already know that purchase price
has to be greater than the sale price.
But let's say or other way around, sale price has to be greater than purchase.
But let's say I purchased it for negative one dollars and then I sold it for $4.
I get an error check constraint.
Positive P price is violated.
That is the constraint I just added.
Now I can also drop a constraint using drop.
Where are you?
Drop and then check some constraint name.
I could also drop constraint in the name of that constraint.
In this case, both of them will work.
So as long as I have the name positive P price, I can now do alter table houses.
And then drop constraint.
Positive P price.
And if I now try and insert that what used to be invalid and negative purchase price, now it's fine.
Now I still have the other constraint in there that says that sale price has to be greater than purchase
price.
So I could drop that one too if I wanted to.
All I need to do is alter table house's drop constraint and then SE price GTP price.
And that's the name I came up with when I added that constraint.
So Ultra table is powerful.
It can be overwhelming to remember how it works, but just go to the docs if you ever have questions.
Remember if you see those square brackets there or whatever those stripe brackets, square brackets,
that means something is optional.