MySQL Database Tutorial - 30 - NOT NULL & AUTO INCREMENT Video Lecture | MySQL (Sequential Query Language) Database Tutorial - Software Development

what is going on guys welcome to your
xxx MySQL tutorial and in this lesson I
want to talk to you guys about some
additional pieces of information that
you can use whenever you're creating
your tables they're going to come in
handy the first piece is null or not
null now whenever you create a table and
you want to allow the people to insert
rows there are certain columns where you
can leave empty and it doesn't really
matter for example whenever you're
making a website in you want a user to
sign up and type in their username and
password you don't want them to leave
this empty so in other words you do not
want this no you do not want this empty
there are other times when you may have
forums on your website that it's okay to
leave empty for example you know those
address forums that say address 1 and
there's an optional address 2 if you
live in an apartment or maybe you have
some foreign address well not everyone
needs to fill out the address 2 or the
second address so it's ok if they leave
that empty but other times such as name
zip code you need to fill that in so let
me go ahead and give you guys a quick
example use your name bar chart if you
were to insert a row like this where you
definitely needed the information to be
required they couldn't leave empty such
as a username you definitely want to use
your name go ahead and after you're done
writing your data type type the word not
what I say nuts not null and what this
means is not empty they can't enter a
blank for a username so if you were to
have something like address two you
would go ahead and not even include that
because address two is optional but
again remember whenever you want the
information to be required and you can't
have empty go ahead and type not null
and this means that if they try to
insert a row of data without a username
or with a blank username it won't insert
it into your table and it'll give them
an error so that's what not in all means
it can't be empty another thing is
something called auto-increment now
whenever you're making a primary ID say
you want to make a primary D it's of
course an int and of course it's not no
because that's the unique identifier you
can't have it empty now we don't want
the user to choose their own primary key
we want to do that automatically now in
order to do this every time a row gets
inserted into your database we want it
to increase by one for example we want
the first easier that signs up to have
the primary key of one we want the
second one to have the ID number of two
three four five and so forth so how can
we do this well at first you may be
thinking okay so I'm gonna have to make
a query I'm gonna have to select the
most recent user I'm gonna have to
select their ID number maybe make a
formula add one to it and then throw it
in for this one pretty confusing huh or
you can just use this skirt keyword
right here Auto underscore increment now
whenever you use the keyword Auto
increment that means that it's
automatically gonna add one to the last
ID you don't have to do anything
manually it automatically adds one
whenever you create a new row so there
you go instead of having to grab the
last piece of data and making a formula
to add one it automatically either adds
one to each entry sounds confusing it's
once you get the hang of it it's really
not so now let me show you guys what a
final full table creation will look like
so let's say we want to create a table
we already have users and customers so
let's just name this I don't know people
you can even name it bacon or something
stupid doesn't matter
so of course let's just go ahead and
enter the same information as before ID
username and password now ID is gonna be
the primary key so int now of course not
know an aside from that Auto increment
so this is going to start by default at
one and it's going to keep going and
going add one to it each time the user
doesn't have to worry about the ID
number you don't have to worry about the
ID number my SQ o is going to
care of everything for you so again
their primary key is not null
it can't be empty and it's going to
increase by one each time
now the next one the next column is
username which is just text letters and
numbers and we'll give this a max of 30
and this can't be empty either and the
third one is password varchar' I think
this was 20 and of course they need a
password they can't have this empty
either now last but not least we already
know that we want this to be the primary
key primary key there you go
I was about to say primary IKEA's
totally different but MySQL doesn't know
that yet so we need to tell it so
primary key we want one of these to be
the primary key which one you say ID so
now we go ahead and create this table
and it says okay
table created BAM there you go so now if
we look at bacon it has ID is the
primary key use your name and password
before but now none of these are allowed
to be null and also ID gets Auto
incremented so if we were to add some
information in the bacon table say we
wanted to I don't know
insert two users or something check it
out for ID I'm not gonna put anything by
default and whenever you're programming
your website or something you don't
insert any value in here explicitly you
let MySQL take care of it
so the first user comes along and his
name is Bucky he must be a pretty cool
guy and it's password is password
because he's an idiot
now the next user comes along in her
name is Sally and her password is slam
bacon so now we go ahead and hit go and
it says two rows inserted but now we go
ahead and look at our bacon table and
check it out
MySQL automatically gave Bucky the user
ID of one and he gave Sally the user ID
of two how awesome is that and if we
were to add more we would just need the
username and password in MySQL would
automatically give them the next ID of
three four five six and seven so on and
so forth
so there you go that is how you well
make awesome tables and um is there
anything else let me think
no that's it
so I mean I guess I could sit here and
not talk about anything else but what
would I do that for so anyways I'm gonna
go out some videos and maybe go get a
milkshake at McDonald's I know what's
unhealthy but come on guys give me a
break so anyways thank you guys for
watching and no idea what I'm gonna be
teaching in the next lesson but it's
gonna be awesome so I will see you then