Guide to MySQL Constraints

The Complete Guide to MySQL Constraints: A Comprehensive Explanation

MySQL constraints are essential rules enforced on database columns to maintain data integrity, accuracy, and consistency. Constraints prevent invalid data from entering the database and ensure relationships between tables are correctly maintained.

This guide covers all MySQL constraints, including their syntax, variations (single-column, multi-column, and dependent constraints), and real-world examples.

---

1. Understanding MySQL Constraints

A constraint in MySQL is a rule applied to a column or table to restrict the values that can be stored. Constraints ensure that only valid and meaningful data is stored in a database.

Types of MySQL Constraints

1. NOT NULL – Ensures a column cannot have NULL values.
2. UNIQUE – Guarantees that all values in a column are distinct.
3. PRIMARY KEY – Uniquely identifies each record in a table.
4. FOREIGN KEY – Enforces referential integrity between tables.
5. CHECK – Ensures a condition is met before inserting or updating data.
6. DEFAULT – Assigns a default value if no value is provided.
7. AUTO_INCREMENT – Automatically generates unique numeric values.

---

2. Creating Sample Database & Tables

Before demonstrating constraints, let's create a sample database and tables.

Step 1: Create a Database

CREATE DATABASE CompanyDB;
USE CompanyDB;

Step 2: Create Employees Table with Constraints

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT CHECK (age >= 18 AND age <= 65),
    salary DECIMAL(10,2) DEFAULT 3000.00,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

---

3. NOT NULL Constraint

✅ Definition

Ensures that a column cannot have NULL values.

Single Column Example

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

🔹 name cannot be NULL.

Multi-Column Example

CREATE TABLE Employees (
    employee_id INT NOT NULL,
    name VARCHAR(50) NOT NULL
);

🔹 Both employee_id and name must have values.

---

4. UNIQUE Constraint

✅ Definition

Ensures that all values in a column are distinct.

Single Column Example

CREATE TABLE Employees (
    email VARCHAR(100) UNIQUE
);

🔹 email values must be unique.

Multi-Column (Composite) Example

CREATE TABLE Employees (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    UNIQUE (first_name, last_name)
);

🔹 Ensures no two employees have the same first_name and last_name.

---

5. PRIMARY KEY Constraint

✅ Definition

A PRIMARY KEY uniquely identifies each record and cannot be NULL.

Single Column Example

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY
);

🔹 Each employee_id must be unique and not NULL.

Multi-Column (Composite) Example

CREATE TABLE EmployeeProjects (
    employee_id INT,
    project_id INT,
    PRIMARY KEY (employee_id, project_id)
);

🔹 A composite key ensures uniqueness across multiple columns.

---

6. FOREIGN KEY Constraint

✅ Definition

A FOREIGN KEY ensures referential integrity by linking a column to another table’s primary key.

Single Column Example

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);

🔹 department_id in Employees must exist in Departments.

Multi-Column (Composite) Example

CREATE TABLE Orders (
    order_id INT,
    product_id INT,
    customer_id INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (customer_id, product_id) REFERENCES Customers(customer_id, product_id)
);

🔹 Ensures that the combination of customer_id and product_id exists in the Customers table.

---

7. CHECK Constraint

✅ Definition

Ensures a column satisfies a condition.

Single Column Example

CREATE TABLE Employees (
    age INT CHECK (age >= 18 AND age <= 65)
);

🔹 age must be between 18 and 65.

Multi-Column Example (Dependent Values)

CREATE TABLE Employees (
    salary DECIMAL(10,2),
    bonus DECIMAL(10,2),
    CHECK (salary >= bonus * 2)
);

🔹 Ensures salary is at least twice the bonus.

---

8. DEFAULT Constraint

✅ Definition

Assigns a default value to a column.

Example

CREATE TABLE Employees (
    salary DECIMAL(10,2) DEFAULT 3000.00
);

🔹 If salary is not provided, it defaults to 3000.00.

---

9. AUTO_INCREMENT Constraint

✅ Definition

Automatically generates a unique numeric value for each row.

Example

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT
);

🔹 employee_id auto-increments with each new record.

---

Summary of MySQL Constraints

Constraint	Purpose
NOT NULL	Prevents NULL values
UNIQUE	Ensures column values are unique
PRIMARY KEY	Uniquely identifies records
FOREIGN KEY	Enforces referential integrity
CHECK	Validates data based on a condition
DEFAULT	Assigns a default value if none provided
AUTO_INCREMENT	Auto-generates unique numbers
INDEX	Improves search performance

---

Conclusion

MySQL constraints ensure data integrity, accuracy, and consistency. Understanding how to use constraints properly is crucial in designing robust databases.

🚀 Master these constraints to build high-quality, error-free MySQL databases!