Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Sometimes, you may need to change the definition of a table after it's already been defined, and populated with data. You can do this with the alter table statement. We're going to be using the scratch database here for this lesson, and I'm just going to copy a little bit of code out of our exercise file. So this is the chapter 3 exercise file starting around line 201. From the create table to the select from test. Just copy that and paste it into the SQL box here.
So we're creating a simple table with 3 columns and populating some data into them and it looks like that. Now suppose some time is passed, and I decide I want a new column. I can use the alter tables statement like this. I'm just going to leave that select statement there and I'm going to say alter table test add d. And I'll make it the same varchar 10. And when I say go, you see we have a new column d, and it's filled with nulls.
The part after the, so we have alter table test, and the part after the add keyword is really just a normal column definition just like in a create table statement. I can also remove a column with drop. I can say alter table, drop d like this, and that'll drop this column, and all the data that's in it. If I drop a column that has data, that data will be deleted. For example, if I drop column b here, now column b is gone, and all the data is gone with it.
So you have to be very careful removing columns obviously from a live database. As you can see, the data is permanently lost. It cannot be recovered. But you also need to be aware that any code that may depend upon columns that you're removing, that code needs to be modified, make sure that it's not referencing columns that are no longer there. By default, new columns are added after the last column. You saw that our column d ended up there at the end of the table. If I want to, I can add a column in a particular position like this.
I can say alter table, add and I'll call it bb, and it'll be Varchar after a. And we'll have a new column bb after the a column there. And you notice that it nulls that data that was there before, it's gone forever. New columns are filled with null by default, you can specify a different default like this. I can add a new column d and just say default panda like that.
Of course everything here after add is just like a column definition and so when I say go now that d column has panda in it, in fact you can even define auto incrementing columns. I can say, add id serial, and if I want to add a column that's not after anything, that's actually the first column, I can just say first like that. And serial if you remember, is an auto incrementing column. And so it says 1, 2, 3. You can always see the full definition of your table schema with the show create table statement like that.
And that shows you a create table statement that would create exactly this table. And you'll notice there's our big end unsigned not null auto increment for the id column serial. So the alter table command is a powerful tool, and as such, it should be used with care and caution. You need to be careful to track and update any code that may depend on the existing schema before you change it. Sometimes this sort of change is necessary, so it's good to understand how to accomplish it, but extra special care is necessary especially for live databases.
So I'm going to go ahead and drop this table now to return our database to it's original state.
Get unlimited access to all courses for just $25/month.Become a member