# Module 6: MySQL Constraints

MODULE OVERVIEW

This module introduces MySQL constraints and explains how they help maintain
data accuracy, consistency and integrity within relational databases.



Learners will understand how constraints define rules for the information stored
in database tables and help prevent invalid or inconsistent records.



The module covers Primary Keys, Foreign Keys, disabling foreign key checks and
the UNIQUE, NOT NULL, DEFAULT and CHECK constraints.



Through these concepts, learners will understand how to establish relationships
between tables, control data entry and enforce database validation rules.






WHAT WILL BE COVERED


6.1 PRIMARY KEY

 * Understanding the purpose of a Primary Key in MySQL.

 * Learning how primary keys uniquely identify records in a table.

 * Defining primary keys when creating tables.

 * Adding primary keys to existing tables.

 * Understanding primary key requirements, including uniqueness and non-null
   values.

 * Applying primary keys to identify customer, employee, product and transaction
   records.


6.2 FOREIGN KEY

 * Understanding the purpose of Foreign Keys in relational databases.

 * Exploring relationships between parent and child tables.

 * Defining foreign keys when creating tables.

 * Establishing relationships between related database records.

 * Understanding how foreign keys maintain referential integrity.

 * Exploring the relationship between primary keys and foreign keys.

 * Applying foreign keys to connect related business information across tables.


6.3 DISABLE FOREIGN KEY CHECKS

 * Understanding the purpose of foreign key checks in MySQL.

 * Exploring the FOREIGN_KEY_CHECKS system variable.

 * Disabling foreign key checks during specific database operations.

 * Enabling foreign key checks after completing the required operations.

 * Understanding how disabling checks affects referential integrity validation.

 * Exploring situations where temporarily disabling foreign key checks may be
   required.

 * Understanding the importance of maintaining valid relationships when
   re-enabling checks.


6.4 UNIQUE CONSTRAINT

 * Understanding the purpose of the UNIQUE constraint.

 * Ensuring that column values remain unique.

 * Applying UNIQUE constraints when creating tables.

 * Adding UNIQUE constraints to existing tables.

 * Working with unique values across one or more columns.

 * Understanding how UNIQUE constraints help prevent duplicate records.

 * Applying unique constraints to fields such as email addresses and
   identification numbers.


6.5 NOT NULL CONSTRAINT

 * Understanding the purpose of the NOT NULL constraint.

 * Preventing columns from storing NULL values.

 * Defining NOT NULL when creating tables.

 * Applying NOT NULL to existing columns.

 * Understanding the difference between NULL and an empty value.

 * Identifying fields that require mandatory information.

 * Applying NOT NULL to maintain complete business records.


6.6 DEFAULT CONSTRAINT

 * Understanding the purpose of the DEFAULT constraint.

 * Defining default values for table columns.

 * Applying DEFAULT when creating tables.

 * Setting default values for existing columns.

 * Understanding how MySQL uses default values when no value is supplied.

 * Applying default values to fields such as status, category and record
   creation details.


6.7 CHECK CONSTRAINT

 * Understanding the purpose of the CHECK constraint.

 * Defining conditions that column values must satisfy.

 * Applying CHECK constraints when creating tables.

 * Adding CHECK constraints to existing tables.

 * Using expressions to validate data values.

 * Understanding how CHECK constraints help enforce business rules.

 * Applying validation conditions to maintain accurate database records.


SKILLS YOU WILL GAIN

 * Understanding MySQL constraints and their role in database management.

 * Defining primary keys to uniquely identify records.

 * Establishing table relationships using foreign keys.

 * Managing foreign key checks during database operations.

 * Applying UNIQUE constraints to prevent duplicate values.

 * Using NOT NULL constraints to enforce mandatory fields.

 * Defining default values using DEFAULT constraints.

 * Applying CHECK constraints to validate data.

 * Maintaining referential integrity and data consistency.

 * Designing relational database structures with appropriate validation rules.


LEARNING OUTCOMES

By the end of this module, learners will be able to:

 * Design database tables with appropriate constraints to maintain accurate
   business information.

 * Define primary keys to uniquely identify customer, employee, product and
   transaction records.

 * Establish relationships between related tables using foreign keys.

 * Understand when foreign key checks may need to be temporarily disabled during
   database operations and the importance of maintaining referential integrity.

 * Apply UNIQUE constraints to prevent duplicate values in fields such as email
   addresses and identification numbers.

 * Use NOT NULL constraints to ensure mandatory business information is
   provided.

 * Define default values for fields that require predefined values when no input
   is supplied.

 * Apply CHECK constraints to enforce business rules and validate stored
   information.

 * Maintain data integrity and consistency across related MySQL database tables.


CERTIFICATION ALIGNMENT

This module develops MySQL database integrity and constraint management
knowledge that supports the broader learning pathway towards the Oracle
Certified Professional, MySQL 5.6 Database Administrator certification
referenced in the course brochure.

The topics covered help learners understand primary keys, foreign keys,
constraint management and data validation within MySQL databases.


CONTINUE TO THE NEXT MODULE

Continue to Module 7: MySQL Querying Data
[https://teamacademy.help.center/article/1369-MySQL-Querying-Data] to learn how
to retrieve, filter, sort and organise database records using SELECT, WHERE,
ORDER BY, DISTINCT, AND, OR, IN, BETWEEN, LIKE, LIMIT and other SQL query
conditions.