7 days of WordPress plugins, themes & templates - for free!* Unlimited asset downloads! Start 7-Day Free Trial
FREELessons: 16Length: 1.5 hours

Next lesson playing in 5 seconds

Cancel
  • Overview
  • Transcript

3.5 Modifying Table Structures

Very rarely in the world of software development will you ever create the perfect database from the outset so that it will never need to change. It just doesn't happen. Software changes. Requirements change. You need to be able to change your tables as well. Let's talk about making structural modifications to your tables.

Related Links

3.5 Modifying Table Structures

As I started to explain in the last lesson, at this point we have an empty table and there's some modifications that I wanna make. So maybe in my design early on I really didn't do a good job of thinking about all the scenarios and things that I wanted to be able to handle. But now I can go back and start to modify my guest table a little bit. And so here's some of the changes that I wanna make. I'd like to add a column, I'd like to add a created date column which is basically going to say when I added this information. So maybe it's a date, time stamp and I think maybe that's what we'll go with. And then I wanna be able to modify an existing column, I would like to change my Confirmed to Assume No. Because I'm going to tell people that I need them to RSVP and when they RSVP back to me and tell me they can, then I will update their value to a Yes, that they are going. So those are the two main things that I wanna do. So I can add columns, I can modify columns, and of course, I could delete them as well. So we'll see and example of that. So let's go ahead and start with the basic process of doing an addition of a column. So let's start by talking about how we're gonna do this. So what are we actually doing here? We're actually changing this table, we're altering it. So in the world of SQL, we're actually going to do an alter table command. And we need to specify what table we're altering. So I wanna alter guest. So I'm gonna alter my table guest, and what is it I wanna do? Well, in this case, I want to add a column. So I'm going to say add. Now, what is it that I wanna add? Well, let's say I wanted this to be the created_date. And then, I wanna specify what data value or what data type it is. And, in this case, we are actually going to specify this as being a timestamp. And what a timestamp is in the world of MySQL is basically a combination of the current date and time sandwich together. So that's whats gonna be in this particular column. So at this point, I can go ahead put in a semicolon, and then I will go ahead and do an explained guess again. And now, we're gonna see here that we have a create a date, that's gonna be a time stamp, it is not nullable. Because by default it's going to add it in as not nullable and the default here is going to be the CURRENT_TIMESTAMP. So basically, what it's done here is it's done a little bit of help for me. So it knows that I added in a created_date that is of type timestamp, and so it is putting itself in here as being automatically inserted. So I don't have to worry about specifying that value just the way that I did for the IDs when I was doing my insert into. Remember when I did insert into guest I could skip ID. Well I can do the same thing here with created_date. It's going to set it to the current timestamp, which is actually pretty cool. Now, that's nice, but let's say I didn't necessarily wanna do that. Maybe I didn't wanna add that created_date or I wanted to do something else instead or maybe I thought I was gonna need that information but I don't really need it and now I wanna actually want to remove a column. So how am I gonna do that? Well, I'm once again gonna do an alter table and I'm gonna alter my guest table. And once again, like anytime you want to delete values or for the most part you wanna delete things, you're going to be using a drop command. So in this case I want to drop created_date. So now, we go ahead and take a look at our table again, and we're back down to where we were. We have our id, first_name, last_name, confirmed, and all of this other information, which is fine. So at this point now I really wanna make some changes. So there's two things that I wanna do here, I want to modify confirmed to be not nullable. So I want to change this to be a no, so it's gonna require a value. And I wanna specify that it has a default and that default is going to be no. Cuz once again, like I said, I want to make sure that when records get inserted in here that by default are null or they are no. And that they have to tell me when they're going to come through an RSVP and then I'll go ahead and update that record. So let's go ahead and clear this out again, and let's see how we're going to do that. So let's go ahead and once again do an alter table, we're gonna alter guest. And in this case, I wanna make a modification, so I'm gonna specify that I want to modify and I want to modify confirmed to continue to be a char1, that's fine. But in this case, I wanna make sure that it is not null. So let's go ahead and do that, we'll go ahead and do an explain guest. So now, you're gonna see here, that confirmed is actually going to be required. So now, I've got that going for me, which is good. And like I said, in addition to that, I also wanna specify that it has a default value. And the way that we're gonna do that is we're gonna break out our altered table again here. So I wanna alter table guest, I wanna modify confirmed. It's going to be a character, it's gonna be a single value, just like it was before, either yes or no. It's gonna be not null and then I also wanna specify a default of the single character of capital N. So let's go ahead and execute that query. We'll come back and explain guest. So now we see that our table now has our confirmed column, just like we wanted it to. It is not going to allow null values, and it has a default of n. So what does all that really do for us? Well, it kinda makes our life a little bit easier for creating that data. So let's go ahead and clear this out again, and we'll do an explain here. So let's go back and take a look at our insert into again. So let's say I want to insert into guest, and I wanted to insert a first_name, I wanted to insert a last_name and really that's all I wanna do. And I'm gonna specify the values to be Derek, Jensen just like that. And let's go ahead and execute that and it says one row affected. Now, I didn't specify the confirmed so lets just go ahead and do a select. And now you see in here, I have an ID of 3. Now, that's because, yes, I did have a 1 and a 2 in there before, but auto_increment keeps an incrementation counter internally. So even if I delete old records that get rid of the old auto-incremented IDs, it continues to go along with the values that it was tracking to prior to that, so that's why I have a 3 here. Then I have a first name of Derek, last name of Jensen, and confirmed by default is no because it has a default value of N and it is not going to allow nulls. And just to show you that, if I try to come in here and do an update, so if I wanted to update guest, and I wanted to set confirmed equal to NULL and go ahead and try to do that. I'm actually going to get an error that says the column confirmed cannot be null. And you would get an error like this on updates or inserts if you were trying to update or insert a null into a not nullable column. So there you have it, those are the basic processes that you can use to add columns, drop columns and modify existing columns within your table.

Back to the top