Article #1 MYSQL (DDL)

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

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

• 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.

create the user and role tables with the script below.

The tables are seen on the left after successful running on the scripts.

1. 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.

1. 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.

1. 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

1. 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.

After that create a foreign key constraint as shown below.

1. 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.

1. 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.

1. 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.

1. 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.