Now that we have our schema in place, it is time for us to build our first table! You can see down here is a list of database schemas. If you click the schema that you made in the last guide. you’ll notice that there aren’t any tables in it.

Now how can we make a table? Well, there's a couple of ways of doing it. One of the ways is to make sure that you've clicked into the schema that you want to work with. You then could come up to the left-hand corner and hit the “create a new table in the active schema in connected server”

I personally just like to come down to where the schemas are listed and right-click the ‘Tables’ and click on create table.

When I do that it brings up this “new table” dialog box. I'm going to call this table “users” and that is going to be the name that we reference anytime we want to query, add, edit, or delete items in that table. Now technically until we hit apply this is not going to be a live table, but before we do that we need to add some columns. If you've never worked with databases before you can think of columns in the way you have probably always thought about columns. Columns are very similar to how you'd see them in an Excel spreadsheet.

You're going to have columns and it's not going to be quite as easy as Excel where you can just start typing anything that you want in. So right here I'm going to double click on the column <click to edit> and I'm going to call this users_id. You need to tell MySQL what type of data is going to be assigned to those columns.

Now there are a number of different practices when it comes to naming columns. Whenever I'm working in a Rails application this part is pretty much taken care of automatically. When I'm doing it manually so say I'm creating a very basic kind of database only application. In those cases, I would simply do something like this I like to put the name of the table in plural form. Then followed by an underscore followed by the actual name so users_id.

Now we're going to come to data types, this is where things start to get a little bit interesting. If you have never worked with data types before and this essentially means that we need to give some hints to MySQL. We need to let MySQL know the kind of data that's going to be contained in this column. For example, if we pick out the “decimal” data type and then we try to put someone's name in it. MySQL is going to throw an error and that's for a very good reason. You really want to be careful about the data types you select.

Imagine a scenario, where you want to sum up the total sales inside of some kind of sales table. If you store the wrong kind of data on accident or someone makes a mistake and puts a name inside of a decimal or integer value. What's going to happen when you try to call sum on the column? It's not going to work because you're going to have a bunch of numbers but then it is not going to know how to total up somebody's name on top of the numbers.

That's why this is so important, we're essentially setting some ground rules for how we want the data to be placed inside of the table. That way if for some reason we accidentally try and put the wrong kind of data in one of the columns. MySQL workbench is going to stop us. So we can fix that before we have caused an issue inside of the data itself.

Now for the first column, this is going to be what is called the primary key. So a primary key in SQL is the key that is like the master key for the entire table. If you want to access a unique value inside of the table, you want to make sure that you have some type of identifying number. So take for example this “users table”.

We need to have a way of making sure that every record can have one type of unique value. If I were to have two users that are both named “Jordan Hudgens” in the database. It's perfectly natural that you could run into a situation where you have user names that are identical.

That's fine, we need to be able to have some way of knowing which user is which user. You can also do this kind of thing on the e-mail address but I always like to have some type of actual integer-based ID. If you work in frameworks such as a Ruby on Rails, you will know that default an ID which is called the primary key.

Years ago back when relational databases started to come out I would see a number of database tables that didn't have an integer-based ID. They would find one column that they knew was going to have unique values. So in this example, we have the ability to add the ability to have email addresses.

Now technically those email addresses should all be unique and in fact we're going to make sure they're all going to be unique by implementing some validations. The reason why I don't want to use that email address as a primary key is because that is of the varchar data type.

A varchar data type is a string data type. We can put pretty much anything that we want inside of this column data type(varchar). Using that datatype is much slower for the database to index and then parse through than an integer data type. So while I like to have a primary key that's an integer is because one I know it’s always going to be a unique value. It's easy to increment and I will show you how we can increment this value.

In other words, when the first users created they're going to have an users_id of 1. Then when the second user is created they're going to have an ID of 2. Then it's also going to be much faster when we're wanting to create relations between the tables. We're going to create guides table and by doing this we're going to be able to quickly see which user wrote which guide. So that is an introduction to data types.

Now we have these kind of confusing looking two initial columns here.

If you hover over them they give some more details. If you want to make life even easier on yourself just come down here on the bottom right-hand side and you'll see that all of these things are right here.

I can set my integer value and I want to have a default, I can put it in here now. Just as a rule never put a default in your primary key. That would not make any sense. Your primary key by default needs to be unique. So you'd never have a need to do that. We're going to select "primary key". We're also going to say we do not want this to be normal we want it to be unique. Those are all important things and make sure your users_id looks like this!

If we come up here then we can click on AI which stands for auto-increment. UN is unsigned. We have unique. This is a reason why I don't like looking up here is because I read UN as unique when really UK is a unique one. So here you have a primary key that is not null, unique, and auto-increment. Now that is not all we're going to have because that would be pretty boring to just have a bunch of auto-incrementing numbers.

So next we're gonna come down here and let's put in another column called users_name. Now this one is going to be a varchar datatype. Inside of these parentheses, we can put however many characters as you want to in here. We could say that we will allow a hundred characters in here but anything usually within reason. You wouldn't want to put a giant number because you will run into some memory spacing issues.

You usually want to keep this at whatever the maximum amount of characters you're going to allow in “user's name”. This is not going to be a primary key and It is not going to be not null. In other words, we want to give the ability for a user account to be created without a name. Now we do not want this to be unique and we don't want any of the other items selected. That's the only thing we need to do for the “users name”.

Now the next column is going to be users_email. This is also going to be a varchar datatype and for this one, we’re gonna do 80 characters. That means that we can’t have an email with more than 80 characters. Now this one is not a primary key and It is not null. In other words, we are going to add that one because we do not want a users account to be created if it doesn't have an email address. We also want it to be unique.

It doesn't make any sense for us to allow accounts that have identical email addresses. If you're building out an application this is just logical. If you're having them use their email address to sign in. You couldn't have a situation where two users that have identical email addresses because then you wouldn't know if they would be able to sign in or not. This is all we need.

So we've created a user's table with a users_name and users_email with both of these being var car data types. The users_ id being the primary key and it auto increments. So let's come down here and let's see what else we have. So we have indexes now by default. We're having an index for the users_id.

We don't index for name or e-mail. We're going to get into indexes later on in the course. Just know right now that indexes are a way for MySQL to help you perform rapid queries. That's all you have to know for right now. We'll get into what this means later on! Foreign keys we'll get into this later it's not possible for us to have a foreign key at the moment because we don't have any other tables. A foreign key by definition connects a table to another one. So we'll get into that in one of the next guides.

Right now we created a users table. If I click “Apply” this is going to bring up the script that is actually going to run. It's going to run this and then store this in the database. So it's going to create a table and it's going inside the devcamp_sql_course schema users. Then it's going to create a user's_id, users_name, and a users_email.

Notice it puts in all of these data types for us and it has items like auto-increment and not null. Then from there, it sets a primary key to the users_id and then creates an index for the ID, so that it's unique. Then for the users_email because it's going to be unique as well.

So when I click “apply” this runs successfully. So we have this little green dot says it was able to execute the statement. Now click on close. Now come down here you can see that automatically we have our tables so now we have a users table here, click on it. You can see that we have column indexes, foreign keys, and triggers. Right-click on columns there is our set of columns right there.

Great job, going through that you now know how to create a table In MySQL.