Adding Unique Constraint to City Names

Introduction

In this project, you will learn how to add a unique constraint to the city names in the country table of a MySQL database. This is a common task in database management, where you need to ensure that each city name is unique within the database to maintain data integrity.

🎯 Tasks

In this project, you will learn:

• How to start the MySQL server and log into the MySQL terminal
• How to import a SQL script to create a database
• How to add a unique constraint to a field in a MySQL table

🏆 Achievements

After completing this project, you will be able to:

• Understand the importance of unique constraints in database management
• Confidently add unique constraints to fields in MySQL tables
• Apply your knowledge to maintain data integrity in your own database projects

Start MySQL and Import the Database

In this step, you will learn how to start the MySQL server and import the world.sql database into MySQL.

1. Open a terminal on the server.

2. Start the MySQL server:

   sudo /etc/init.d/mysql start
   

3. Log into the MySQL terminal:

   mysql -uroot
   

4. Import the world.sql database:

   SOURCE ~/project/world.sql
   

After completing these steps, you will have the world database imported into your MySQL server, and you can proceed to the next step.

Add a Unique Constraint to the City Names

In this step, you will learn how to add a unique constraint to the LocalName field in the country table.

1. Create a new file named changeUni.sql in the ~/project directory.

2. Add the following SQL statement to the file:

   ALTER TABLE country
   ADD CONSTRAINT UC_LocalName UNIQUE (LocalName);
   

   This statement adds a unique constraint named UC_LocalName to the LocalName field in the country table.

3. Save the changeUni.sql file.

4. Run the changeUni.sql script in the MySQL terminal:

   SOURCE ~/project/changeUni.sql
   

   This will execute the script and add the unique constraint to the country table.

After completing these steps, the unique constraint will be added to the LocalName field in the country table.

Summary

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