ASP.NET Core Web API – Creating MySQL Database

 me tell you that it is not that hard at all but quite opposite. Creating schemas, tables, relations, and populating those tables is very easy, once you are familiar with the MySql Workbench tool.

By following instructions from this post, you will master skills for creating a 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 the .NET Core project to it.

So, let’s start.

MySQL, Creating Schema Model Diagram

After the MySQL server has been installed (we are using 5.7.17 version for this series), we will start the MySQL Workbench, which will be our tool for creating tables. When you open the Workbench, click on the existing connection, add a password and you will be redirected to the database view. Go back on a home screen and click on the Models icon:

By clicking on the plus button, next to the Models text, we will enter into the model view which will allow us to create our new schema:

Click twice on the Add Diagram button and you’ll see the screen for creating a schema. Here we will create our tables and the relationships between them:

Creating Tables in Schema Model Diagram

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 give a name to the table:

Name the first table Owner and add the columns:

Double click on the second database table, name it Account and add the columns:

Just want to explain why the value Char (36) for the Primary Keys in our tables. It is because we will use GUID’s for our keys, and representation of a GUID in MySQL is the Char (36).

Adding Table Relations

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 a 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 the 1:N relation, one marked in the above picture, then first click on the Account table and then click on the Owner table. Right after that action, you will see the new column inside the 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 in the Columns tab. If you go right and click on the foreign keys tab you will see information about our foreign key in the Account table:

Let’s change the default settings for OnUpdate and OnDelete. For the OnUpdate, we will choose the Cascade option. That means if a row with a primary key, in the Owner table, is updated automatically, update a row with the corresponding foreign key in the Account table. Also, for the OnDelete, we will choose the Restrict option. That’s because we don’t want to allow deleting a row with a primary key, from the Owner table, without previously removing the row with the corresponding foreign key. That way we are preserving the referential integrity of our database.

If you look in the Catalog Tree, which is the part of the schema view, you will see “mydb” as the database name. We don’t want to call our database like that, so to change it, right-click on mydb and choose edit schema. Give it the name AccountOwner and just save your model.

 

Exporting Schema to the Script File

We are finished with the create schema actions. Now, we want to export our database schema to a script file, which will provide us with a code to create our database with the tables, by simply executing our generated script.

Click on the File menu, then hover over the Export and choose the Forward Engineering SQL Create Script. You will see a new window, in which you have to add the name of the script file and the other options for generating scripts. In the 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 the “Generate DROP Statements Before Each CREATE Statement” option. This option will drop any existing table, prior to the creation of a new one with the same name. This way you’ll avoid errors if any table already exists inside the database while starting the script file.

 

Click the Next button. You will see the SQL Object Explorer Filter and in that window just check the first option: Export MySQL Table Objects. Click the Next again. Here you will notice the way your script looks like. Just click the finish and the script will be exported to your desired location.

Creating the Database from the Script File

If your database view is still opened, redirect yourself to 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 the Local Instance part to enter the 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 choose the option Run SQL Script. Select the saved script and then just click the run button. This will create a database. If you refresh your schemas view, you will be able to see our database.

Because the account table depends on the owner table, we need to populate the owner table first and the account table next. Right-click on the owner table, choose the first option: Select Rows – Limit …, and you are going to see the query window:

Populating Tables With the Data

Now we want to populate our tables, and for that open a new SQL tab for executing the queries:

By executing the code above, we will have our tables populated with the required data.

If you want to back up your data, all you have to do is the following: Click on the Server menu, choose the Data Export, select your database, and below check Export to Self-Contained File. Choose the destination for your backup file and click Start Export.

Conclusion

That is all for Part of this tutorial. We have learned how to create MySQL schema, how to create tables and table relationships. Furthermore, we now know how to create database scripts and 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.