Wondering how you can work with the database in MySql by using Workbench? It is not that hard but quite opposite. Creating schemas, tables, relations and populating those tables is very easy, once you are familiar with MySql Workbench tool.
By following instructions from this post, you will master skills for creating the database, creating and populating tables, creating relations between those tables and having the backup of your data. Later on, we will use this database to connect .Net Core project to it.
If you want to see all the basic instruction and complete navigation for this tutorial, please click on the following link: Introduction page for this tutorial.
This post is divided into several parts:
- MySQL, creating schema model diagram
- Creating tables in schema model diagram
- Adding table relations
- Exporting schema to the script file
- Creating database from the script file
- Populating tables with the data
After MySQL server has been installed, we will start MySQL Workbench, which will be our tool for creating tables. When you open Workbench, click on existing connection, add a password and you will be redirected to the database view. Return on a home screen and click on Models icon.
By clicking on plus button, next to Models text, we will enter into model view which will allow us to create our new schema.
Click twice on Add Diagram button and you’ll see the screen for creating a schema. In here we will create our tables and relationships between them.
We will create two tables. Click on the icon for the table and then click again on the work ground. Repeat this step one more time to create another table. After, click twice on the first table so we could create columns and name our table. You will have something like this.
Name first table Owner and add columns as follows:
Double click on the second table add name Account and add columns as follows:
Just want to explain why value Char (36) for Primary Keys in our tables. It is because we will use GUID’s for our keys, and representation of GUID’s in MySQL is Char (36).
You can save your model by pressing CTRL+S and after that, we will add the relation to our tables. We will assume that one owner can have multiple accounts (Domestic, Foreign currency, Savings…), therefore we are seeing relation one-to-many between owner and account. Taking this knowledge into consideration, we want to connect two tables by making the OwnerId foreign key in the Account table, so it could provide us with the relationship between those two tables.
Click on 1:N relation, one marked in above picture, then first click on Account table and then click on Owner table. Right after that action, you will see new column inside Account table. That is our foreign key. Double click on that new column to change its name. Name it OwnerId.
Now if you look all the way down, you will see several tabs. We are currently on Columns tab. If you go on right and click on foreign keys tab you will see information about our foreign key in Account table.
We just want to change default settings for OnUpdate and OnDelete. For OnUpdate we will choose Cascade, which means if a row with a primary key, in Owner table, is updated automatically update a row with the corresponding foreign key in Account table. Also, for OnDelete, we will choose Restrict, because we don’t want to allow deleting a row with a primary key, on Owner table, without previously removing the row with the corresponding foreign key. That way we are preserving referential integrity of our database.
If you look in Catalog Tree, part of the schema view, you will see that database name is “mydb”. We don’t want to call our database like that, so to change it, right click on mydb and choose edit schema. Give it a name AccountOwner and just save your model.
We are finished with schema creation. Now, we want to export our schema to script file, which will provide us with code to create our database with tables, by simple executing our generated script.
Click on the File menu, then hover over Export and choose Forward Engineering SQL Create Script. You will see a new window, in which you have to add the name of the script file and other options for generating scripts. In field “Output SQL Script File”, write the name of the script, if left blank, you will just view generated script and be able to copy it but not save it. Also, click on “Generate DROP Statements Before Each CREATE Statement” option. This option will drop any existing table, prior creation new one with the same name. This way you’ll avoid errors if any table already exists inside database while starting the script file.
Click Next button. You will see SQL Object Explorer Filter and in that window just check first option: Export MySQL Table Objects. Click Next again. Here you will see how your script looks like. Just click finish and script will be exported to your desired location.
If your database view is opened from the beginning of this part of the tutorial, redirect yourself on that view. If it is not open, click on the home tab, then click the first icon from the top (one with Dolphin on it) and then click on Local Instance part to enter database view.
In the database view, choose the Schemas tab in the Navigator part of the page. There you will find some default databases created and in that section, your new database will appear.
Go to the File menu and option Run SQL Script. Select your saved script and then just click the run button. This will create your database. If you refresh your schemas, you will be able to see our database.
Because account table depends on owner table, we need to populate owner table first and account table next. Right click on owner table, choose the first option: Select Rows – Limit …, and you’ll see a window like this.
For populating our tables with the data open new SQL tab for executing queries
And execute the code below:
INSERT INTO `owner`
VALUES ('24fd81f8-d58a-4bcc-9f35-dc6cd5641906','John Keen','1980-12-05','61 Wellfield Road'),
('261e1685-cf26-494c-b17c-3546e65f5620','Anna Bosh','1974-11-14','27 Colored Row'),
('a3c1880c-674c-4d18-8f91-5d3608a2c937','Sam Query','1990-04-22','91 Western Roads'),
('f98e4d74-0f68-4aac-89fd-047f1aaca6b6','Martin Miller','1983-05-21','3 Edgar Buildings');
INSERT INTO `account`
If you want to back up your data, all you have to do is following: Click on Server menu, choose Data Export, select your database and below check Export to Self-Contained File. Choose the destination for your backup file and click Start Export.
That is all for the Part 1 of this tutorial. We have learned how to create MySQL schema, how to create tables and table relationship. Also, we have learned to create database scripts and to insert data into our tables.
Even though we covered the basics of MySQL, you have enough knowledge to start creating your own relational database schemas.
Thank you for reading and check out the next part, where we will start diving into .NET CORE world.
For any additional question, please do not hesitate to ask by leaving the comment below.