Detailed Notes
1. Fix constraints and autoincrement. 2. setting primary key before autoincrement 3. drop a constraint 4. Add the primary keys 5. Create an index 6. Unique index creation
alter table address add constraint address_pk PRIMARY KEY (t_one_id); alter table app_user add constraint app_user_pk PRIMARY KEY (t_parent_id);
alter table address modify column t_one_id int auto_increment;
create index auth_inx on app_user(userLogin);
alter table address add constraint FOREIGN KEY (type_id) references lkp_type(type_id);
alter table app_user add constraint user_address_fk FOREIGN KEY (t_one_id) references address(t_one_id);
Transcript Text
[Music] well hello and welcome back we are continuing our series when we're going through my sequel maria db development type things and we're really just in this episode up to day eight uh episode eight we are continuing or actually start going back and working through some constraints and talk a little bit more about those now in the last episode one of the things we did is we moved our tables we basically did we used a create table as select star from and ended up taking our tutorial 1 and tutorial parent table and basically created address and app user the problem with those so if we look at like address is we didn't get the constraints or anything else it just pulled the field names the column names the type whether they were null or not and basically that was it i may have pulled a default oh i think it did pull it a fault over but not in all cases um because let's see or maybe it did we will oh it did so pulled over the and it pulled over the default values the key no pun intended issue with this is that it did not pull across primary key it did not pull across auto increment and it did not pull across foreign key relationships so the first thing i want to do is fix those the way we do that is we can come in after the fact and if i can get some stuff out of the way so i can work my mouse then and come in here and we're going to alter table address we're going to do a constraint that we're going to call the address primary key it's going to be on the t1 id and it works fine we're gonna do the same thing and we're gonna do app user on that one and so now if we go back to our describes so describe address we now see the primary key as t1 id but it's not doing the alternate the auto increment and app user okay we've got our primary key now we're going to work on adding that adjusting that auto increment a key thing about this is that order matters when you do this once you create when you create the table from just creating it from a query like we did before where we do like uh do something like this where we just build out our whole table notice that we can put on our increment we can do primary key so if i want to create two table tutorial one from that then for some reason didn't like what it printed but basically so now if i do promote one i'm going to see boom i have my primary key it's auto increment is that extra notice if i do describe what's that address so i managed to somehow lose name oh because i probably changed over the name i added that later and i don't have my auto increment if you do it then it's fine if you wait till after the fact an auto increment cannot be done and actually i guess you could run into this during the script an auto increment cannot be added to a non-key has to be a primary key first so we have created those so we're going to be safe when we do that we go in here and we're going to take the table address here and we're going to modify that column t1 ib to auto increment and if we describe address now we'll see that's on our increment we can do the same thing with the parent id and no we could have gone in because we sort of you know we're changing stuff on the fly we could do this and just drop a table and rebuild it or we could do alter and do alter the column names as well however we're still going to have to worry about things like making sure that we get our keys and our primary keys and our foreign keys together now if we wanted to one thing we run into is once we start adding data there can be issues where we can't delete a record because we still have a constraint we've seen that or we want to insert a record and there's a constraint issue and so one of the things we can do is we can drop a constraint so we let's see so let's first create a foreign key because i don't think we have any right now yeah we don't have any so let's create that foreign key from uh type id address so again that's what we're going to do is we're going to do alter table that's address i'm sorry it's add constraint instead of primary like we did here primary key it's gonna be foreign key so let's do four and it doesn't have to be all caps but that sometimes helps and so the foreign key is gonna be type id and we have to tell it what it references and so it's going to references look up what's that called lookup type type id whoops okay and now if i do describe address and i didn't create i didn't give the constraint a name which we normally have done so here when i do add constraint i give it a constraint name i didn't this time so let's see what happens what is that so now if we do select star f from lookup type let's get one that's invalid so it's one two three so if we insert into address address name cannot be no comma type id that'll be the shortest one we can do here values whoops there you go got off of that somewhere so values um address name will be just oop i should hit the right key delete me and its type is going to be four so this should give us a constraint issue which is going to boom it does and now notice it just generated a constraint name since we didn't give it one it generates that but now we can take that and we can do drop constraint that guy let's come over here and do just to make sure i get the i could do it exist but it should be so if i go here oh it's alter table i can't do just a straight drop so i need to do alter table address whoops drop constraint there we go so now if i come back and try to do the insert it allows it now looks what happens if i try to create that constraint again where was that constraint there we go now i can't because it's going to blow up when it tries to do it because i already have an issue so what i need to do is uh select star from so you don't really want to drop constraints unless you're like cleaning stuff out or if you know that you're 100 sure that they're gonna you know they're getting in the way but they're 100 sure when you get your data in it's going to be clean uh so i'll just like start from address i'm going to delete from address where t1 id equals 5. and now i can go back i can create the there we go and i can create the foreign key i'm also going to go in let's keep that here and i also want to do let me create one real quick i'm going to alter table this is going to be app user and constraint and this will be user address foreign key and it's going to be on the t1 id and it's going to reference the address t1 id so if i take that whoops uh ch alter table app user a constraint user okay i did something wrong um ultra table and constraint user address so it's foreign key references oh because it has to i have to spell the table right there okay so now if we look at address we see that we have our order increments and our primary key if we look at app user we're gonna see that we have our foreign key here and our primary key so we're in pretty good shape so with this we've got our table sort of straightened out but while we're here let's talk about indexes when you are doing a search in a database when you're doing a select with a where clause or an inner join the value or values that it joins on the case where you say this value equals that value when you do that for example if i do select star from app user where t1 id equals two i think that's one that's um there we go so in this case where i do this t1 id equals one um i guess i could i'd probably want to do it sorry but in searching this it's actually going to do what's called a table scan it's going to look through the entire table to find that then one of the things we can do is add indexes now for a primary key it's already going to be indexed for a foreign key it's indexed because it's indexed on the the primary key your foreign key always relates to a primary key on another table however let's say we wanted to do um we're going to search by user login which probably is not uncommon so what we would want to do is we can do a index and so we could try create index uh let's see email look up on uh who's that app user email and i think or sorry user login and so now we're not going to see here a difference i don't think oh yes it is it's going to say so you're going to see that you've got a key there which is an index which means now there is a sort of like a table of contents that is going to more quickly direct you to records now when you're talking about a string it may or may not be useful although you can use hashes and things like that that may get you there closely but particularly in a number and a strings assuming that there's a a fairly even distribution and that they are you know easily found by uh order you know like if everything's starts with a aaaaa then it's not going to get you there or you know or the same multiple letters together numbers are usually going to be the fastest because it can very quickly jump to a number via an index it knows real fast that i'm gonna you know if i'm looking for something between a hundred thousand and a hundred thousand and ten it can very quickly go oh i'm gonna jump to this index to the hundred thousand point into the hundred and ten thousand and that is going to get you huge performance changes as you get into this when you're using indexes properly then your queries are going to be really fast if not you're going to do these table scans which means every time it looks for something it being the database it has to go through every single record in the database and that can be very time consuming very quickly and so it's very useful to note that you can make you can create indexes real quick now you don't want too many because when you insert then all the indexes have to be updated so you can you know that can slow down your inserts but particularly if you've got a database that is mostly static data or data that exists and sometimes you're making changes to it but mostly you're searching against it or for example a reporting database then you're indexing you know is going to be awesome you want to make sure that that's put everywhere that you are searching for that kind of value or you can do multi multi-column indexes we'll see later when you do that if you hit that index then it's going to be a lot faster and there you can go search like database performance and indexes show plan things like s-h-o-w space p-l-a-n-w i'm sorry p-l-a-n just that you know that string and you will see graphical examples of such things so you do want to have a you want to have indexes like in your mind you can also do um let's say i want that was email lookup i want to be email unique so let's say that i don't want to allow an index email address to show up more than once for an app user creating xmlc on app user email all right so if i do this then what this does is this means that i cannot add i will get the same problem i had with the primary keys or the foreign keys when we tried it at like foreign key we tried to add one that doesn't exist in this email as you can see it's got uni which is unique so that means if i try to add an app user from app user that has an email address that already exists so let's say user test one so now i do insert into app user what do i have to have i have to have a username an email a user login a password and an update date oh which i don't because that's automatically done so i think i can go with just those values so username can be anything so he's going to be blah this case i want my email to be this because this is going to blow me out of there and then user login can be blah and password can be blah if i use that then what i'm going to do is i'm going to see that i get a duplicate entry for that see so it it's telling me right now that cannot be used i need a new one so there's a lot of uses for indexes i wanted to touch on those before we move on to some of the other things we will touch on after this good time while we're cleaning up our data a little bit our database that we could take a look at this that being said i think we'll wrap this one up so as always go out there and have yourself a great day a great week and we will talk to you next time you
Transcript Segments
[Music]
well hello and welcome back we are
continuing our series when we're going
through
my sequel maria db development type
things
and we're really just in this episode up
to day eight uh episode eight we are
continuing or actually start going back
and working through some constraints
and talk a little bit more about those
now in the last episode
one of the things we did
is we moved our tables we basically did
we used a create table as select star
from
and ended up taking our
tutorial 1 and tutorial parent table
and basically created address and app
user the problem with those so if we
look at like
address
is we didn't get the constraints or
anything else it just pulled
the field names the column names
the type whether they were null or not
and
basically that was it
i may have pulled a default oh i think
it did pull it a fault over but not in
all cases um because let's see or maybe
it did
we will
oh it did so pulled over the and it
pulled over the default values
the key no pun intended issue with this
is that it did not pull across primary
key it did not pull across auto
increment and it did not pull across
foreign key relationships so the first
thing i want to do is fix those
the way we do that is we can come in
after the fact
and
if i can get some stuff out of the way
so i can work my mouse
then and come in here and we're going to
alter table address we're going to do a
constraint that we're going to call the
address primary key
it's going to be on the t1 id
and it works fine we're gonna do the
same thing and we're gonna do app user
on that one and so now if we go back to
our describes so describe address we now
see the primary key as t1 id but it's
not doing the alternate the
auto increment and app user
okay we've got our primary key now
we're going to work on
adding that adjusting that auto
increment
a key thing about this is that order
matters when you do this
once you create when you create the
table from
just creating it from a query like we
did before where we do like uh
do something like this where we just
build out our whole table
notice that we can put on our increment
we can do primary key so if i want to
create two table tutorial one from that
then for some reason didn't like what it
printed but basically
so now if i do
promote one
i'm going to see
boom i have my primary key it's auto
increment is that extra notice if i do
describe
what's that address
so i managed to somehow lose name oh
because i probably changed over the name
i added that later and
i don't have my auto increment
if you do it then it's fine if you wait
till after the fact an auto increment
cannot be done and actually i guess you
could run into this during the script an
auto increment cannot be added to a
non-key
has to be a primary key first
so
we have created those so we're going to
be safe when we do that we go in here
and we're going to take the
table address here and we're going to
modify that column t1 ib to auto
increment
and if we describe address now we'll see
that's on our increment we can do the
same thing
with the
parent
id
and no we could have gone in because we
sort of you know we're changing stuff on
the fly we could do this and just drop a
table and rebuild it
or we could do alter and do alter the
column names as well however we're still
going to have to worry about things like
making sure that we get our keys and our
primary keys and our foreign keys
together
now if we wanted to
one thing we run into is once we start
adding data
there can be issues where we can't
delete a record because we still have a
constraint we've seen that
or we want to insert a record and
there's a constraint issue
and so one of the things we can do is we
can drop a constraint so we
let's see
so let's first create
a foreign key because i don't think we
have any right now yeah we don't have
any so let's create that foreign key
from
uh type id address
so again that's what we're going to do
is we're going to do alter table
that's address
i'm sorry it's add constraint
instead of primary like we did here
primary key it's gonna be foreign key
so let's do four and it doesn't have to
be all
caps but that sometimes helps and so the
foreign key is gonna be type id
and we have to tell it what it
references and so it's going to
references
look up
what's that called
lookup type
type id
whoops
okay
and now if i do describe address
and i didn't create i didn't
give the constraint a name which we
normally have done
so here when i do add constraint i give
it a constraint name i didn't this time
so let's see what happens what is that
so now if we do select star f
from
lookup type
let's get one that's invalid so it's one
two three so if we insert into
address
address name
cannot be no comma type id that'll be
the shortest one we can do
here values
whoops
there you go
got off of that somewhere so values um
address name will be just oop
i should hit the right key delete me
and its type is going to be four so this
should give us a constraint issue which
is going to boom
it does and now notice it just generated
a constraint name since we didn't give
it one it generates that but now we can
take that
and we can do
drop constraint
that guy
let's come over here and do
just to make sure i get the
i could do it exist but it should be
so if i go here oh it's alter table
i can't do just a straight drop
so i need to do
alter table
address
whoops
drop constraint there we go so now if i
come back and try to do the insert
it allows it
now looks what happens if i try to
create that constraint again
where was that constraint there we go
now i can't because
it's going to blow up when it tries to
do it because
i already have an issue so what i need
to do is
uh select star from
so you don't really want to drop
constraints unless you're like cleaning
stuff out or
if you know that you're 100 sure that
they're gonna you know they're getting
in the way but they're 100 sure when you
get your data in it's going to be clean
uh so i'll just like start from address
i'm going to delete
from address
where
t1 id equals
5.
and now i can go back i can create the
there we go and i can create the foreign
key i'm also going to go in
let's keep that here
and i also want to do
let me create one real quick i'm going
to alter table
this is going to be app user
and constraint and this will be
user
address
foreign key
and it's going to be on the t1 id
and it's going to reference the address
t1 id
so if i take that
whoops
uh ch
alter table app user a constraint user
okay i did something wrong
um
ultra table and constraint user address
so it's foreign key
references
oh
because it has to i have to spell the
table right
there okay so now if we look at address
we see that we have our order increments
and our primary key if we look at app
user
we're gonna see that
we have our
foreign key here and our primary key
so we're in pretty good shape
so with this
we've got our table sort of straightened
out but while we're here
let's talk about indexes
when you are doing a
search in a database when you're doing a
select with a where clause or an inner
join
the value or values that it joins on the
case where you say this value equals
that value
when you do that
for example if i do select star from
app user
where t1 id
equals two i think that's one that's um
there we go
so in this case where i do this t1 id
equals one
um i guess i could i'd probably want to
do it sorry but in searching this it's
actually going to do what's called a
table scan it's going to look through
the entire table to find that
then one of the things we can do is add
indexes now for a primary key it's
already going to be indexed
for a foreign key it's indexed because
it's
indexed on the the primary key your
foreign key always relates to a primary
key on another table however let's say
we wanted to do um we're going to search
by user login which
probably is not uncommon so what we
would want to do is we can do a
index and so we could try create index
uh
let's see email
look up
on
uh who's that app user
and i think or
sorry user login
and so now
we're not going to see here a difference
i don't think
oh yes it is it's going to say so you're
going to see that you've got a key there
which is an index which means now
there is a
sort of like a table of contents that is
going to more quickly direct you to
records
now when you're talking about a string
it may or may not be useful although you
can use hashes and things like that that
may get you there closely
but particularly in a
number and a strings assuming that
there's a
a fairly even distribution and that they
are
you know easily found by uh order you
know like if everything's starts with a
aaaaa then
it's not going to get you there or you
know or the same
multiple letters together
numbers are usually going to be the
fastest because it can very quickly jump
to
a number via an index it knows real fast
that i'm gonna you know if i'm looking
for something between
a hundred thousand and a hundred
thousand and ten it can very quickly go
oh i'm gonna jump to this index to the
hundred thousand point into the hundred
and ten thousand
and that is
going to get you huge
performance changes as you get into this
when you're using indexes properly then
your queries are going to be really fast
if not you're going to do these table
scans which means every time it looks
for something it being the database it
has to go through every single record in
the database
and that can be very time consuming very
quickly
and so it's very useful to note that you
can make you can create indexes real
quick
now you don't want too many because when
you insert
then all the indexes have to be updated
so you can you know that can slow down
your inserts but particularly if you've
got a database that is
mostly static data or data that exists
and
sometimes you're making changes to it
but mostly you're searching against it
or for example a reporting database then
you're indexing you know is going to be
awesome you want to make sure that
that's
put everywhere that you are
searching for that kind of value or you
can do multi
multi-column indexes we'll see later
when you do that if you hit that index
then it's going to be a lot faster and
there you can go search
like database performance and indexes
show plan things like s-h-o-w space
p-l-a-n-w i'm sorry p-l-a-n
just that you know that
string and you will see graphical
examples of such things
so you do want to have a you want to
have indexes like in your mind
you can also do um let's say i want
that was email lookup i want to be email
unique
so let's say that i don't want to allow
an index
email address to show up more than once
for an app user
creating xmlc on app user email
all right
so if i do this then what this does
is this means that i cannot add i will
get the same problem i had with the
primary keys
or the foreign keys when we tried it at
like foreign key we tried to add one
that doesn't exist
in this email as you can see it's got
uni which is unique so that means if i
try to add an app user
from app user that has an email address
that already exists so let's say user
test one
so now i do insert into
app user
what do i have to have i have to have a
username
an email
a user login
a password
and an update date oh which i don't
because that's automatically done so i
think i can go with just those
values so username can be anything so
he's going to be blah
this case i want my email to be this
because this is going to blow me out of
there and then user login can be blah
and password can be blah
if i use that
then what i'm going to do is i'm going
to see
that i get a duplicate entry for that
see so it it's telling me right now that
cannot be used i need a new one
so there's a lot of uses for indexes i
wanted to touch on those before we move
on to
some of the other things we will touch
on after this good time while we're
cleaning up our data a little bit our
database
that we could take a look at this
that being said i think we'll wrap this
one up so
as always go out there and have yourself
a great day
a great week and we will talk to you
next time
you