Modify Primary Key of Countrylanguage Table

Introduction

In this project, you will learn how to modify the primary key of the countrylanguage table in a MySQL database. The initial primary key was based solely on the CountryCode column, but for better organization, you will create a composite primary key using both the CountryCode and Language columns.

👀 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') | NO   |     | F       |       |
| Percentage  | decimal(4,1)  | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.001 sec)

🎯 Tasks

In this project, you will learn:

• How to access MySQL using the sudo command without a password
• How to import data from an SQL file into a MySQL database
• How to modify the primary key of a table to a composite key

🏆 Achievements

After completing this project, you will be able to:

• Understand the importance of using a composite primary key for better data organization
• Confidently make changes to the primary key structure of a MySQL table
• Apply your knowledge of SQL statements to achieve desired database modifications

Access MySQL and Import the Data

In this step, you will learn how to access MySQL using the sudo command without any password, and import the data from /home/labex/project/world.sql into MySQL.

1. Open a terminal and run the following command to start the MySQL service:

sudo service mysql start

2. Access MySQL using the sudo command without any password:

sudo mysql

3. Import the data from /home/labex/project/world.sql into MySQL and switch to the world database:

MariaDB [(none)]> SOURCE /home/labex/project/world.sql;

Modify the Primary Key

In this step, you will learn how to modify the primary key of the countrylanguage table to a composite key consisting of CountryCode and Language.

1. Open the changePri.sql file.
2. Add the following SQL statements to the changePri.sql file:

-- Delete the original primary key constraint
ALTER TABLE countrylanguage DROP PRIMARY KEY;

-- Add the compound primary key constraints
ALTER TABLE countrylanguage ADD PRIMARY KEY (CountryCode, Language);

3. Save the changePri.sql file.
4. Run the changePri.sql script in MySQL:

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

5. Verify the changes to the countrylanguage table:

MariaDB [world]> DESC countrylanguage;

The output should now show the composite primary key consisting of CountryCode and Language.

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

Summary

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