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

  • Overview
  • Transcript

2.3 Creating Tables With Additional Options

In the previous lesson, you learned how to create very simple tables. In this lesson, you will learn how to add some additional constraints to your table and columns.

Related Links

2.3 Creating Tables With Additional Options

In the last lesson, we talked about creating and deleting tables. And you saw how we were going to create a guest table. And in there, we had an id, which is gonna be that kind of unique identifier that we were gonna use. So that later on, when we're creating a little bit more of an elaborate table structure, we could have an easy way to uniquely identify each record in our guest table. But that way we defined it before was that you could have it be empty. And it was not a primary key. We're gonna talk a little bit about what those things are. And how that we can take advantage of MySQL to do some of those things for us? How we can be a little bit more specific about how we're creating those records? And how we're creating those columns in our table, that are gonna help us with inserting, and updating, and retrieving records later on? So let's go ahead and revisit this concept of creating. So let's go ahead and create a table again. And in this case, I wanna once again create a guest table. And I wanna stick with the concept of an ID, and I do want it to be an integer. But now, I'm gonna add a couple modifiers onto this columns so that I can be a little bit more specific about what I want this table structure to look like. So the first thing is, when we have a unique identifier for a table we don't want it to be able to be null. So that's very important to us. It needs to have a value. We want to ensure and create a rule basically that says this column must always have a value in it. It can never be null or empty. And the way that we do that is by using the not null keyword. So by specifying that, we are going to be able to ensure that this column always has a value in it. So that's good. But, how are we actually gonna get that value in there? And what's gonna make this unique? Because if we wanna be able to create this table and insert records into it and make sure that it always has a value, and that it always has a unique value. Because, if I wanna retrieve a specific row from a table, I need an identifier for that row but it has to be unique. I can't have multiple rows in there that have the same IDs. Because, then if I were try to retrieve something out of there by a unique identifier, I would get multiple records and it kind of defeats the purpose of having this id be unique. So the way that we're gonna start to ensure that this is unique is we're gonna specify this as being a primary key. And what that means is a number of things. It's going to ensure that this is going to be unique. So by default, the primary key which can be a single column or spread across multiple columns. But the basic process is to create it on a single column which means it's going to be unique and it's also going to have an index, which is basically just a fancy way of saying that it's going to be organized in such a fashion inside MySQL. That if I were to say, give me the record that has an id of five, it's optimized to retrieve this id based on this row based on this id because it's a primary key. Now, we also wanna make sure that really I don't wanna have to put data in here. I would really prefer that MySQL would handle that for me, ensuring this uniqueness. And the way that you do that is by using another little helper here called auto_ increment. And what this is going to do is, it's going to say every time you insert a record into this table, it's going to look at the next value that is available for this column. Because it's an integer, so it'll start at one. And the next time I insert a value, it's gonna insert it as a two, and then a three, and then a four, and it's gonna keep going up until it reaches the end of the integers. So I know that seems like a lot there, so we had to do a little bit of work here to say we want our id to NOT BE NULL, which means it has to have a value. It's gonna be a primary key which is going to ensure that this value is going to be unique as well as indexed. So that my searching for records based on id is going to be fast and it's going to auto increment itself every time I insert Into this table. All right, so that was a lot going on but we're going to keep forging ahead. So once again, I want to know the first name of the people. So we're gonna stick with the varchar again of 20, and we're going to go with the last name. And we're gonna make sure that this is a varchar as well and I can already see that I made a mistake in my typing. But this is going to be very interesting. So I'm gonna continue here and show how we're gonna fix this. And then finally we're going to have confirmed. And once again this is going to be a character. And we will close off our first parentheses and add in a semi-colon. So now I know that I created an error here. And if I try to execute this, I'm actually going to get an error. And it's gonna tell you that you have a syntax error near, this guy right here, which is obviously the problem. So now I want to change that, I want to fix that, but I have to type all that in again. Well not necessarily, you can actually use the up arrow to go ahead and bring back the last command, or the last several commands that you've typed in by keep hitting up, which is actually kind of nice. So now, if I do that it gives me back the last command that I executed, so I can come back over here and I can fix my typing error. And now that I've done that, I'm thinking that maybe I want to change something else in here as well. Let's say that I'm going to require that there's always at least a first name. Maybe I don't always know people with last names. Maybe I don't know their last names. Maybe I only met them in the neighborhood or something like that. So maybe first name is not gonna be null, so we're gonna say not null here. So as you can see, I can start to pepper these things all throughout my table when I'm defining it. So I think that's gonna be good for now. So let's go ahead and hit Enter, and you're gonna see Query OK. So we can do our show tables. We have our guests and then let's go ahead and explain guest. Now when we do that, you're gonna see, it looks fairly similar to what it did last time with a little bit of a change. So we have our field id, which is once again an integer. But you can see now the null is set to NO, and that's because we used this NOT NULL keyword. And then you can see Key here, we have PRI for primary and that happened because of our PRIMARY KEY keyword and then we have Extra for auto_increment. So you can see that's where that comes from as well. And you're gonna see how we're gonna take advantage of this in the upcoming lessons. When we start to actually insert data. And you can see we also still have our first name, last name, and confirmed like we did before. But first name this time is NOT NULL, and the other two are. Maybe we wanna change that confirmed as well to NOT BE NULL. But we're gonna continue to build on this as we go. So let's go ahead and call it a lesson with now having our guest table ready to go.

Back to the top