All right.
Next, we're going to talk about default values.
As we saw when I describe any of our tables so far.
Describe cats.
Cats, too.
We have this default column here that describes the default values present for a column.
Kind of a confusing sentence, but essentially there is no default value for name or age.
Same thing with cats too.
There's no default value, but we can change that.
When we create a table, we can specify a default value using this syntax default and then immediately
after separated by a space, whatever the default value is that we want to set now, we can alter a
table as well to do this after the fact, but we haven't covered that yet.
So once again, we're going to make a new table this time.
Katz three.
It's going to get a little confusing.
Cats, Cats, two.
Cats three.
But we've only got one or two more, so let's do cats three.
And I'm going to just keep it simple again.
Name of our car.
We'll have a default of unnamed or no name or unknown, whatever it would make sense to set up as a
default value for a cat.
Let's say we're running a shelter and we bring in cats off the street.
They don't have microchips or collars.
We don't know their name.
We probably still assign them some name.
I think the way shelters usually do it is they actually have a list of different random names they can
pick from instead of naming every cat that comes in, you know, John Doe or something.
But it doesn't matter.
That's not the point of this course.
But let's do a create table Cats three.
And we'll have name, which again will be our car and the slides that did 100, It doesn't really matter.
We're going to have short cat names, so I'll do 50 and then default.
Let's go with mystery and then age is an int.
We don't have to provide a default value if we don't want to, but in this case just to demonstrate
it, I will age int the default value will be 99, just like the slide.
All right, close my Perin semicolon and we're done.
So now what I can do is insert into cat three.
Let's say I only provide an age because this cat that we just picked up off the street, this poor wet,
sad cat, we don't know its name so we can estimate its age.
I guess we'll say it's age is two.
If we look at cats three, select star from Cats three.
We'll see.
That name has been set to mystery.
So that default value kicked in.
In fact, I could insert a cat that has nothing when we create it.
No value is present.
But because we have those default values present, we end up with mystery and 99.
Now it's kind of confusing to have all these cats called mystery.
And why on earth would the default age be 99?
It doesn't matter.
Stop asking questions.
So that's how we set up a default value, at least when we create a table.
That's how we set up a default value.
Following the name of the column.
We then specify the data type and then add ons like not null or in this case default followed by the
default value.
Now, something that you might be wondering is do we need to specify not null and default?
Because by providing a default value, doesn't that mean that there's no way for this value to be null?
Right?
If I inserted this empty cat as we saw right here.
And we select Star again from Catch three.
We don't end up with null values because we have default values.
However, that doesn't stop me from changing this to be null or changing this to be null.
Just setting a default value is not the same as saying this can never be null, so they can work in
tandem, not null.
Just says this can never be empty.
This particular column, this value can be empty.
And default is simply saying if there is no value specified, it should default to this value.
But if we had default without not null, then I could easily set a value to be null manually.
So I can do that right here.
I'll show you.
I can insert the value null directly into a table.
So insert in two cats.
Three.
Let's say name and age values will be null and no.
So it's a special my SQL value.
And then I'll select all my cats.
And what do we see?
No and no.
So that tells us that a default value is not going to protect us in a situation like that.
If I manually set something to NULL, which is not always a bad thing, sometimes we want to allow NULL.
Imagine we're setting up a a phone book or a contact app or something where users have to have a or
every row in the user's table has a name, an email, a cell phone, and maybe a home phone number and
maybe a fax number.
But not everybody has those.
So maybe we make cell phone a requirement or home phone a requirement, but we can make the others knowable.
They don't have to be present.
So we could say your fax number can be null if you don't have a fax machine.
I don't have one.
We would allow that to be null.
So null is not a bad thing, but it can be a bad thing if you aren't expecting it or you don't want
to allow it.
But all you have to do to make sure that a value is not knowable and has a default value if it's not
present, is to use both not null followed by default.
So to save time, I'll just copy and paste this so that you don't have to watch me type the whole thing.
I'll paste it in there.
And now if I were to try to insert into Cats four where I manually write null as my two values, I'll
get an error.
It says name cannot be null and it would have said age cannot be null, but it stopped at that first
error so it didn't insert anything.
But if I instead did this insert into cats for empty empty.
We will get those default values.
So I know this is kind of boring.
I hope I was at least clear here so you understand the difference.
Cats forward.
We have unnamed and 99.
Those were the default values.
And if I go to a describe cats for not only do we see that it's not notable name and age, neither of
which are knowable, we also see the default values listed.
So if you ever forget what the default value is, you can describe your table and my SQL will show you
right there.
I hope you're hanging in there.