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 NOT NULL – Ensures a column cannot have NULL values. UNIQUE – Guarantees that all values in a column are distinct. PRIMARY KEY – Uniquely identifies each record in a table. FOREIGN KEY – Enforces referential integrity between tables. CHECK – Ensures a condition is met before inserting or updating data. DEFAULT – Assigns a default value if no value is provided. 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 = 18 AND age = bonus * 2) );

Mar 6, 2025 - 18:55

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

NOT NULL – Ensures a column cannot have NULL values.
UNIQUE – Guarantees that all values in a column are distinct.
PRIMARY KEY – Uniquely identifies each record in a table.
FOREIGN KEY – Enforces referential integrity between tables.
CHECK – Ensures a condition is met before inserting or updating data.
DEFAULT – Assigns a default value if no value is provided.
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
);