MySQL Database Management | Unique Constraints Tutorial

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

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("`MySQL`")) -.-> mysql/BasicKeywordsandStatementsGroup(["`Basic Keywords and Statements`"]) mysql(("`MySQL`")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["`Database Functions and Data Types`"]) sql(("`SQL`")) -.-> sql/BasicSQLCommandsGroup(["`Basic SQL Commands`"]) sql(("`SQL`")) -.-> sql/DataDefinitionandIntegrityGroup(["`Data Definition and Integrity`"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/alter_table("`Table Modification`") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/int("`Integer Type`") sql/BasicSQLCommandsGroup -.-> sql/alter_table("`ALTER TABLE statements`") sql/DataDefinitionandIntegrityGroup -.-> sql/constraints("`Constraints`") subgraph Lab Skills mysql/source -.-> lab-301282{{"`Adding Unique Constraint to City Names`"}} mysql/alter_table -.-> lab-301282{{"`Adding Unique Constraint to City Names`"}} mysql/int -.-> lab-301282{{"`Adding Unique Constraint to City Names`"}} sql/alter_table -.-> lab-301282{{"`Adding Unique Constraint to City Names`"}} sql/constraints -.-> lab-301282{{"`Adding Unique Constraint to City Names`"}} end

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.

Open a terminal on the server.
Start the MySQL server:
```
sudo /etc/init.d/mysql start
```
Log into the MySQL terminal:
```
mysql -uroot
```
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.

Create a new file named changeUni.sql in the ~/project directory.
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.
Save the changeUni.sql file.
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.

✨ Check Solution and Practice

Summary

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

Adding Unique Constraint to City Names