Article #1 MYSQL (DDL)
Tags: mysql, datascience
Published: 2023-10-12 11:49:10

Introduction:
In this blog, we will delve into DDL and its powerful role in shaping the foundations of databases. We'll use SQL statements to illustrate how this is accomplished in MySQL workbench.
1. **Creating a Database and Tables**:
In this section, we'll explore the process of creating a database and defining tables within it. 
we are going to create a database named UserManagement

- First, open a connection to your MySQL workbench


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/od7u5c4kamq2wnory50f.png)

- After we open let's write our first script to create the database.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p5l3ulknfegymkerrxmk.png)

- The database has been created, we have to create two tables user table and a role table.
Importantly begin by selecting the database to add tables to with the script below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yehzmg0m0ww7r2q876o3.png)
create the user and role tables with the script below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ad38wibdgdzl5yg9kg79.png)
The tables are seen on the left after successful running on the scripts.


2. Adding Columns:
Discover how to enhance the structure of an existing table by adding columns. using our created tables we shall add a role_id column to the user table. The script is on line 9 in the image below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b3t703f67wp3y7rbktd9.png)

3. Modifying Columns
Altering the data types of existing columns. The column we created above is varchar, it is better if it's an integer, so we shall modify our column as shown below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1sb44twtkew6vp50p3xv.png)

3. Dropping a Column of Table:
Learn about the importance of managing database structures by removing unwanted columns in tables. SQL commands for dropping columns in tables will be demonstrated. using the user table we shall drop a column address with the command below

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/771suwdv364r42uh2f8k.png)
4. Establishing Foreign Key Relationships:
Understand how foreign key constraints link data between tables, enhancing database integrity. 
To create a foreign key constraint between two tables in MySQL, you'll use the ALTER TABLE statement. A foreign key establishes a relationship between a column in one table (the referencing table) and a column in another table (the referenced table). 
in our example, we shall use role_id as a foreign key from role table referencing the id. 
NB: column id must have a constraint primary key, to do that the command below is used.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kkauvpwkfnelnfzau5r8.png)

After that create a foreign key constraint as shown below.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/11zqb2rlljwdpz8equ9b.png)

5. Dropping Foreign Key Constraints:
Sometimes, adjustments are needed in database relationships. We'll demonstrate how to drop foreign key constraints to accommodate changes while maintaining data integrity.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fiuwadw1ezpgir4ajj8v.png)

6. Renaming a Table:
Explore the SQL command that enables the renaming of a table, a useful operation for maintaining a consistent and organized database structure.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/o5yrvupcleeddyx14fl8.png)

7. Drop a table.
 Deleting a table that seems to be unnecessary in your database, we use the drop command. for example, we shall delete a role table.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8xxss7pf9hheksygy7c5.png) 

8. Summary of the commands used;

```
create database UserManagement;

use UserManagement;

create table user ( id int, first_name varchar(30), last_name varchar(30), address varchar(50), date_of_birth datetime , phone_number varchar(10) );

create table role (id int, name varchar(30), description varchar(50));

alter table user add role_id varchar(30);

alter table user modify role_id int;

alter table user drop column address;

alter table role add primary key(id);

alter table user add primary key(id);

alter table user 
add constraint fk_role_id foreign key (role_id) references role (id);

alter table user drop foreign key fk_role_id;

alter table user rename to guests;

drop table role;
```

**Conclusion**:
By understanding and mastering Data Definition Language (DDL), you unlock the ability to design and maintain an efficient and robust database structure. This blog has walked you through the essential operations of DDL, providing a foundation for effective database management.