Database Constraints | SQL Programming Tutorial

Introduction

In this project, you will learn how to add constraints to the course schedule in a database. Specifically, you will learn how to add a check constraint to the course table, ensuring that the credits field value is greater than or equal to 3.

🎯 Tasks

In this project, you will learn:

How to start the MySQL server and import a database
How to add a check constraint to a table in the database
How to run a SQL script to apply the constraint

🏆 Achievements

After completing this project, you will be able to:

Understand the importance of adding constraints to a database schema
Implement check constraints to enforce data integrity
Apply SQL scripts to modify the structure of a database

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-301280{{"`Adding Constraints to the Course Schedule`"}} mysql/alter_table -.-> lab-301280{{"`Adding Constraints to the Course Schedule`"}} mysql/int -.-> lab-301280{{"`Adding Constraints to the Course Schedule`"}} sql/alter_table -.-> lab-301280{{"`Adding Constraints to the Course Schedule`"}} sql/constraints -.-> lab-301280{{"`Adding Constraints to the Course Schedule`"}} end

Start MySQL and Import the Database

In this step, you will learn how to start the MySQL server and import the database into it. Follow the steps below to complete this step:

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 edusys.sql script into MySQL:
```
SOURCE ~/project/edusys.sql
```

This will import the data from the edusys.sql script into the MySQL database.

Add a Check Constraint to the Course Table

In this step, you will learn how to add a check constraint to the course table in the database. Follow the steps below to complete this step:

Create a new file named addCheck.sql in the ~/project directory.
Add the following code to the file:
```
ALTER TABLE course
ADD CONSTRAINT credCheck CHECK (credits >= 3);
```
This code adds a check constraint named credCheck to the course table, which ensures that the credits field value is greater than or equal to 3.
Save the addCheck.sql file.
Run the addCheck.sql script in the MySQL terminal:
```
SOURCE ~/project/addCheck.sql
```
This will apply the check constraint to the course table.

Now, the course table in the database has a check constraint that ensures the credits field value is greater than or equal to 3.

Summary

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

Adding Constraints to the Course Schedule