Modifying MySQL Database Table Constraints

Introduction

In this project, you will learn how to modify the countrylanguage table in a MySQL database by removing the "not null" constraint from the IsOfficial field.

👀 Preview

MariaDB [world]> DESC countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | YES  |     | NULL    |       |
| Percentage  | decimal(4,1)  | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.002 sec)

🎯 Tasks

In this project, you will learn:

• How to start the MySQL database and access it using the sudo command
• How to import the world.sql data file into the MySQL database
• How to modify the countrylanguage table by removing the "not null" constraint from the IsOfficial field
• How to verify the changes made to the countrylanguage table

🏆 Achievements

After completing this project, you will be able to:

• Understand how to work with MySQL databases using the command-line interface
• Modify table structures by altering the constraints on fields
• Verify the changes made to a table by describing its structure

Start MySQL Database and Access MySQL

In this step, you will learn how to start the MySQL database and access it using the sudo command without any password.

1. Start the MySQL database service:

sudo service mysql start

2. Access the MySQL command-line interface:

sudo mysql

Now you are ready to work with the MySQL database.

Import the World Database

In this step, you will learn how to import the world.sql data file into the MySQL database and switch to the world database.

1. Import the world.sql data file:

MariaDB [None]> SOURCE /home/labex/project/world.sql;

2. Switch to the world database:

MariaDB [None]> USE world;

Now you have the world database loaded and ready to work with.

Modify the countrylanguage Table

In this step, you will learn how to remove the "not null" constraint from the IsOfficial field in the countrylanguage table.

1. Open the changeNull.sql file in a text editor.
2. Add the following SQL statement to the file:

ALTER TABLE countrylanguage MODIFY COLUMN IsOfficial ENUM('T', 'F') NULL;

This statement will remove the "not null" constraint from the IsOfficial field in the countrylanguage table.

3. Save the changeNull.sql file.

Execute the changeNull.sql Script

In this step, you will learn how to execute the changeNull.sql script in the MySQL command-line interface.

1. In the MySQL command-line interface, run the following command to execute the changeNull.sql script:

MariaDB [world]> SOURCE /home/labex/project/changeNull.sql;

This will execute the SQL statements in the changeNull.sql file and modify the countrylanguage table.

Verify the Changes

In this step, you will learn how to verify the changes made to the countrylanguage table.

1. In the MySQL command-line interface, run the following command to describe the countrylanguage table:

MariaDB [world]> DESC countrylanguage;

The output should show that the "not null" constraint has been removed from the IsOfficial field.

+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | YES  |     | NULL    |       |
| Percentage  | decimal(4,1)  | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.002 sec)

Congratulations! You have successfully modified the countrylanguage table by removing the "not null" constraint from the IsOfficial field.

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.