How to Create a Table in MySQL Workbench

The MySQL Workbench is a useful tool with a GUI (Graphical User Interface) that is easy to use. We can recreate the tables in an entire database using the Forward Engineer option in the MySQL Workbench. In this article, we will create a table in the MySQL Workbench with step-by-step examples and screenshots.

An Introduction to the MySQL Workbench

MySQL Workbench has a list of tools that we can use to our advantage. We can do the following:

• Create and recreate databases
• Prepare ERD (Entity Relationship Diagram) models using the Create New ERD Model option
• Easily understand the database structure containing tables, primary and foreign keys, and indexes

By using the MySQL Workbench, we can get an output that allows us to recreate tables and other database objects. Using this option, we are sure that all properties of a table, including primary, foreign, and unique keys are included.

The MySQL Workbench also has robust handling for errors, allowing the user to easily copy and correct each error as they are encountered in the database.

MySQL Workbench Forward Engineer

This tool is used to create the scripts for creating and backing up a database. This can be found under the Menu, and selecting the option Database -> Forward Engineer menu. We can export database objects, as well as models, and produce outputs in various file formats including SQL files. These output files can then be applied as a script to other databases. 

Opening the MySQL Workbench

In the following example, we first create the database/schema and the tables, followed by using the Forward Engineer option to create the output script.

Upon opening the MySQL Workbench, we see the following:

Figure 1. Opening the MySQL Workbench.

Example 1: MySQL Workbench Create Schema

To create a schema using MySQL Workbench, we do the following:

1. Select File -> New Model. Then a worksheet with an automatic schema named mydb will appear. The schema in MySQL refers to the database. 

2. When the user clicks on the new model, a new database will appear with the name mydb. Double-click on the name mydb, then rename the schema to the desired database name. In this article, we create a database named library.

Figure 2. Creating a new schema in MySQL Workbench.

3. Click the Save button on the toolbar, and then save in the destination folder with the desired MySQL Workbench file name.

Example 2: Creating Tables in MySQL Workbench

We can also create tables in MySQL Workbench by double-clicking on the Add Table button. Here, we will create several tables, namely: user, book, and category table. The following are the details for each table.

user table

Column Name	Datatype
id	INT
name	VARCHAR(50)
email	VARCHAR(45)
username	VARCHAR(45)
password	VARCHAR(45)
id_book	INT

Figure 3. Creating a table user in the MySQL Workbench.

book table

Column Name	Datatype
id	INT
title	VARCHAR(50)
author	VARCHAR(20)
publisher	VARCHAR(20)
category	INT

Figure 4. Creating a table book in the MySQL Workbench.

category table

Column Name	Datatype
id	INT
name	VARCHAR(45)

Figure 5. Creating a table category in the MySQL Workbench.

Example 3: Creating MySQL Relationship Diagram

After we create tables for our database, the next step is to create a MySQL entity relationship diagram. These include relationships that exist between tables. This can also be done in the MySQL Workbench. 

In this case, we use our three tables. The following illustrates the steps that we can do in MySQL Workbench.

1. In the user table, click the Foreign Key tab to create a database relation. We can specify foreign key names, parent and child tables, and the fields. In our example, we name our foreign key fk_id_book for the column id_book in the child table user, referencing the column book in the parent table books.

Figure 6. Creating a foreign key in the MySQL Workbench.

2. We also do the same for the book table. In this example, we name our foreign key fk_id_category for the column id in the child table book, referencing the column category in the parent table category.

Figure 7. Creating a foreign key in the MySQL Workbench.

3. To display the tables and keys that we have created, in the Menu bar, we click on Model -> Add Diagram to create a new ERD (Entity Relationship Diagram) diagram. This opens up a drag-and-drop interface where we can organize our tables and have a visual representation of our database.

Figure 8. ERD (Entity Relationship Diagram) in MySQL Workbench.

4. To generate these table into a file with the ‘.sql’ extension, select File -> Export -> SQL Forward Engineer Create Script. The figure below shows us how we can do this in MySQL Workbench.

Figure 9. Using the Forward Engineer feature in MySQL Workbench.

5. The following shows us a sample output of the Forward Engineer feature in MySQL Workbench.

Figure 10. The header section of the sample output.

Figure 11. CREATE SCHEMA script (library) of sample output.

Figure 12. CREATE TABLE script (category) of sample output.

Figure 13. CREATE TABLE script (book) of the sample output.

Figure 14. CREATE TABLE script (user) of sample output.

Figure 15. The footer section of the sample output.