In this video, we're going to answer one question What is going on with this column right here?
When we showed a table's description.
So if we take a look at our cat's table, describe cats.
This column right here, it says null.
Yes and yes.
So Field, that's the name of the column.
Name is a var 50 characters.
Age is an integer.
No.
Yes.
No.
Yes.
What does that mean?
Well, what this means is that the null value is permitted in this particular column in name and in
age.
NULL is a value in my SQL that means no value.
It represents the lack of a value for a given row.
It basically means if we see null set to yes, that name can be empty for a row or age can be empty,
or in fact both could be missing entirely.
In other words, it means the value is unknown.
But it's really important to know this is different than something like zero.
Zero is a number.
It is a value.
NULL means that there is no information, there is no value at all.
It's a MySQL concept here.
So we could do something like this right now with our cat's table.
I could say I'm inserting a new cat that has a name and no age.
I'll show you what happens.
Insert into cat's name and then values.
And let's insert a cat called Todd.
Okay, I hit enter.
No problem whatsoever.
Let's do a select start from cats.
And what do we see?
Todd is the name.
That's what we expect.
H is null.
So remember, it doesn't mean zero.
It means no value at all.
It means unknown.
There is no information here for age for this row.
And maybe sometimes I want that.
It depends on the situation because we might have different columns that aren't always required that
could be empty.
But a lot of the time we want to require values to be present in a column, meaning you cannot have
null.
Just as a side note right now, we could do something like this.
I can insert a completely empty cat insert into cats no values.
It's just like this.
I select star from cats and now we've got this null null cat, empty name, empty age.
It's still a valid row, but it doesn't.
Shouldn't be valid.
I don't think we would want our cats table to permit this.
So this is where we can add a constraint in called not null.
Now we could alter the table.
We'll learn how to do that.
But for now what we're going to do is make a new table.
I'll just call it Cats two Very simple naming pattern here.
It's simple enough, but in the real world, you probably wouldn't want to have cats.
One cats, two cats, three cats for you probably just want a cats table that you are fixing and updating,
but we don't know how to do that yet.
So all we do is add on not null.
When we are creating a table for each column, we specify the name of the column like age, that type
int and then not null, just like we could do Primary key.
That's another sort of add on for a column, not null as a constraint we can add.
So let's try it.
Let's make a new table create table cats to.
And I'll open it up here.
Oh, no, I misspelled create.
Let's just close that.
We'll get an error.
That's OC.
Create table catch two and then we're going to have instead of here.
Let's go with name is a var car.
Let's limit it to 50 again.
Not know age is an int also not null.
Close the n semicolon and now if we try to insert into cat two, let's just do name only and then values.
Let's insert Bilbo into cats two we get an error field age doesn't have a default value, so that error
might seem like a weird error you might not expect.
Maybe you were expecting something like age must be present or an age cannot be null, but instead it
says age doesn't have a default value.
We'll learn how to set default values in a moment.
But for now, if we take a look at Cats two, it's totally empty.
So it did not work.
Now, if I recall this and I did name and age and I say that Bilbo is, I don't know, 19 and then I
select everything from cats to it worked so we can insert as normal unless we leave off one of those
required fields or columns.
So if I tried it the other way, right, if I didn't provide a name and I only provided an age, same
thing.
We get this error saying field name doesn't have a default value.
Okay, so that's how we can add that constraint that says this column cannot be null, it must be present.
And that's something we often want to enforce on important pieces of data.
But not every single column needs to have not null, but a lot of them do.
Oh, and the last thing I'll show you is that if we try and describe one of these tables again that
has not null set up, we'll see NULL set to know for these different columns.
So let's do describe cats.
We see null as yes, cats to null is no.
And in this case it's just both columns have no or not null.
Both cases allow null, but obviously we don't have to always have the same value for every single column.
Okay.
Next we're going to tackle this default over here.