CodeGym/Java Course/Module 4. Working with databases/Creating tables

Creating tables

Module 4. Working with databases

Level 5, Lesson 8

Available

Create a table

Our list of tables is empty, so it's time to create our first table. There are three ways to do this:

Create Table button in the top toolbar
local menu
SQL script

Let's use the local menu this time. Just right-click on the Tables field and get this picture:

Next, you will see a panel for creating a table - it is scarier than it seems:

You only need 2 places here:

Specify the name of the table in the field above.
Specify the name and type of columns in the field in the center.

Designing: Choosing the Right Column Names and Types

Let's create a table that will store users. In Java we would write something like this:

class User {
   public int userId;
   public String name;
   public int level;
   public Date createdDate;
}

How do we create such a table in SQL?

First, let's define the naming convention. Java uses camelCase, but since SQL is mostly case-insensitive, the underscore is usually used here. So userId becomes user_id and createdDate becomes created_date .

Next, you need to decide on the types. Let's create a table named user , which will contain 4 columns:

id of type INT
name of type VARCHAR(100)
level of type INT
created_date of type DATE

Instead of user_id, we wrote id, since this is how it is accepted in SQL, we would write user_id if somewhere in another table we referred to the id column of the user table.

We also set a 100 character limit for the name field. We do not want someone to save a couple of million characters there and break something for us. Reliability is everything.

Specifying field names

Now let's add the desired columns - there are only 4 of them:

Pay attention to the two columns on the top left:

Column Name are the names of the columns.
DataType are column types.

Everything is as we planned.

And in the lower half of the picture we see a detailed decoding of the current row of the table , which describes the column of the user table. I hope everything is clear.

Important! If you think that the values of some column should definitely not be NULL, then you need to mark it as Not Null (in the lower right corner). In this case, the MySQL server will make sure that this is always the case.

We also have id marked as Primary Key, which, as you remember, means that these are unique Id records.