Database Design | Data Integrity Constraints | MySQL

Introduction

In this project, you will learn how to add various constraints to a database schema, including primary keys, foreign keys, unique constraints, default values, and check constraints. By the end of this project, you will have a better understanding of how to design and enforce data integrity rules in a relational database.

👀 Preview

Database schema constraints preview

🎯 Tasks

In this project, you will learn:

How to create a composite primary key on the salgrade table
How to specify a foreign key on the deptno field in the emp table
How to use a unique constraint to prevent duplicates in the ename field in the emp table
How to set a default value for the comm field in the emp table
How to use a check constraint to prevent the entry of hire dates later than February 28, 2022 in the hiredate field in the emp table

🏆 Achievements

After completing this project, you will be able to:

Understand the importance of data integrity constraints in database design
Implement various types of constraints in a MySQL database
Apply best practices for maintaining data quality and consistency
Troubleshoot and debug issues related to database constraints

Start the MySQL Server and Import the Database

In this step, you will learn how to start the MySQL service and import the personnel.sql database. Follow the steps below to complete this step:

Start MySQL:
```
sudo /etc/init.d/mysql start
```
Import the personnel.sql database into MySQL. You can do this by running the following command in your terminal:
```
mysql -u root < /home/labex/project/personnel.sql
```

This command will import the personnel.sql database into MySQL using the root user.

Add a Composite Primary Key to the ‘salgrade’ Table

In this step, you will learn how to add a composite primary key to the salgrade table. Follow the steps below to complete this step:

Log into the MySQL terminal:
```
mysql -uroot
```
Use the personnel database:
```
USE `personnel`;
```
Add a composite primary key to the salgrade table, with the fields grade, losal, and hisal as the composite primary key:
```
ALTER TABLE salgrade
ADD PRIMARY KEY (grade, losal, hisal);
```

This will create a composite primary key on the salgrade table, using the grade, losal, and hisal fields.

Add Constraints to the ‘emp’ Table

In this step, you will learn:

How to specify a foreign key on the 'deptno' field in the 'emp' table that references the 'deptno' field in the 'dept' table.
How to use unique constraints to prevent duplication of the 'name' field in the 'emp' table.
How to use the default value constraint to set the default value of the 'comm' field in the 'emp' table to 100.
How to prevent entering a hire date after 28 February 2022 in the 'hiredate' field of the 'emp' table using the check constraint.

Follow the steps below to complete this step.

Use the personnel database:
```
USE `personnel`;
```
Specify a foreign key on the deptno field in the emp table, referencing the deptno field in the dept table:
```
ALTER TABLE emp
ADD FOREIGN KEY (deptno) REFERENCES dept(deptno);
```
Use a unique constraint to prevent duplicates in the ename field in the emp table:
```
ALTER TABLE emp
ADD UNIQUE (ename);
```
Use a default value constraint to set the default value of the comm field in the emp table to 100:
```
ALTER TABLE emp
ALTER COLUMN comm SET DEFAULT 100;
```
Use a check constraint to prevent the entry of hire dates later than February 28, 2022 in the hiredate field in the emp table:
```
ALTER TABLE emp
ADD CHECK (hiredate <= '2022-02-28');
```

View Table Structure

In this step, you will use commands to display the structure of tables salgrade and emp:

Show the structure of table salgrade:
```
desc salgrade;
```
Show the structure of table emp:
```
desc emp;
```

You should see the following output:

MariaDB [personnel]> desc salgrade;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| grade | int(4)      | NO   | PRI | NULL    |       |
| losal | double(7,2) | NO   | PRI | NULL    |       |
| hisal | double(7,2) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

MariaDB [personnel]> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| empno    | int(4)      | NO   | PRI | NULL    |       |
| ename    | varchar(20) | YES  | UNI | NULL    |       |
| job      | varchar(20) | YES  |     | NULL    |       |
| mgr      | varchar(20) | YES  |     | NULL    |       |
| hiredate | date        | YES  |     | NULL    |       |
| sal      | double(7,2) | YES  |     | NULL    |       |
| comm     | double(7,2) | YES  |     | 100.00  |       |
| deptno   | int(4)      | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.001 sec)

Congratulations! You have successfully completed the project.

Summary

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

Database Constraint Design and Implementation