MySQL: A Detailed Explanation of MySQL Constraints and Data…
Last updated: 2026-08-26
Constraints are the guardians of data integrity—they prevent dirty data from entering the database.
This lesson provides a systematic overview of all constraint types and their management.
graph TB
A[MySQL Constraints] --> B[PRIMARY KEY<br/>Primary Key Constraint]
A --> C[FOREIGN KEY<br/>Foreign Key Constraints]
A --> D[UNIQUE<br/>Unique Constraint]
A --> E[NOT NULL<br/>Non-empty constraint]
A --> F[CHECK<br/>Check Constraints]
C --> C1[CASCADE]
C --> C2[SET NULL]
C --> C3[RESTRICT]
1. What You'll Learn
- PRIMARY KEY Primary Key Constraint
- FOREIGN KEY Foreign Key Constraints and Cascading
- UNIQUE Unique Constraint
- NOT NULL non-null constraint
- CHECK Check Constraints (8.0+)
2. A True Story
(1) Pain Point: Dirty Data Is Everywhere
Six months after the order system went live, the data quality was alarming: there were orders with negative amounts, “ghost orders” with no associated customers, and a single username used to register five accounts. The development team attempted to implement validation at the application layer, but with front-end and back-end code scattered across different parts of the system, there were always loopholes. During a promotional campaign, a hacker bypassed the front-end validation and submitted an order with a negative amount, resulting in direct financial losses.
(2) Methods for Solving Constrained Problems
Enforcing data integrity at the database level—PRIMARY KEY ensures uniqueness, FOREIGN KEY ensures relationships, CHECK ensures valid ranges, and UNIQUE ensures no duplicates.
| Dimension | Application-Level Validation | Database Constraints |
|---|---|---|
| Protection Scope | This App Only | All Connection Sources |
| Bypassability | Frontend/API can be bypassed | Cannot be bypassed |
| Maintenance Costs | Scattered across multiple locations | Centralized in table definitions |
| Data Security | Medium | Highest |
3. Overview of the Five Restrictions
| Constraint | Function | Allows NULL | Allows Duplicates |
|---|---|---|---|
| PRIMARY KEY | Uniquely identifies each row | ❌ | ❌ |
| FOREIGN KEY | Links to other tables | ✅ | ✅ |
| UNIQUE | Values cannot be duplicated | ✅ | ❌ |
| NOT NULL | Cannot be NULL | ❌ | ✅ |
| CHECK | Meets criteria | ✅ | ✅ |
4. PRIMARY KEY
▶ Example: Primary Key Constraint
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Query OK, 0 rows affected
SQL
-- Single-column primary key
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50)
);
-- Composite Primary Key
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);
Output:
TEXT
📖 Display only
Output displayed
5. FOREIGN KEY
▶ Example: Foreign Keys and Cascading
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
SQL
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE CASCADE -- Cascade order deletions when deleting a customer
ON UPDATE CASCADE -- Cascade update when customer ID changes
);
-- Cascade Options
-- CASCADE: Delete Synchronously/Update
-- SET NULL: Set as NULL
-- RESTRICT: Operation Rejected（Default）
-- NO ACTION: Same as RESTRICT
Output:
TEXT
📖 Display only
Output displayed
6. UNIQUE Constraint
▶ Example: Unique Constraint
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Query OK, 0 rows affected
SQL
-- Single-column, unique
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
username VARCHAR(50) UNIQUE
);
-- Composite Unique
CREATE TABLE user_roles (
user_id INT,
role_id INT,
UNIQUE (user_id, role_id)
);
Output:
TEXT
📖 Display only
Output displayed
7. NOT NULL Constraint
▶ Example: Not-Null Constraint
SQL
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
description TEXT -- Allow NULL
);
Output:
TEXT
📖 Display only
Output displayed
8. CHECK Constraints (8.0+)
▶ Example: CHECK Constraint
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
SQL
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
age INT CHECK (age >= 18 AND age <= 65),
salary DECIMAL(10,2) CHECK (salary > 0),
email VARCHAR(100) CHECK (email LIKE '%@%.%')
);
-- Add CHECK Constraints
ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);
-- Delete CHECK Constraints
ALTER TABLE employees DROP CHECK chk_age;
Output:
TEXT
📖 Display only
Output displayed
9. Constraint Management
▶ Example: Adding/Removing Constraints
Output:
TEXT
📖 Display only
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
SQL
-- Add a Primary Key
ALTER TABLE users ADD PRIMARY KEY (id);
-- Add a Foreign Key
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);
-- Add a unique constraint
ALTER TABLE users ADD UNIQUE (email);
-- Delete Foreign Key
ALTER TABLE orders DROP FOREIGN KEY fk_customer;
-- Delete the unique constraint
ALTER TABLE users DROP INDEX email;
Output:
TEXT
📖 Display only
Output displayed
❓ FAQ
Q Do foreign keys affect performance?
A They do, to some extent (due to constraint checks during writes), but they ensure data integrity. In high-concurrency scenarios, checks can be performed at the application layer.
Q What is the relationship between NULL and UNIQUE?
A UNIQUE allows multiple NULL values (in MySQL, NULL != NULL).
Q Can functions be used in CHECK constraints?
A MySQL 8.0 and later support the use of expressions in CHECK constraints, but do not support subqueries.
Q Are CHECK constraints valid in 5.7?
A No. MySQL 5.7 only accepts CHECK syntactically but does not enforce them; enforcement is not enforced until version 8.0.16 or later.
Q Is the data still there after deleting a foreign key?
A Yes. Deleting a foreign key only removes the constraint; it does not delete any data.
📖 Summary
- PRIMARY KEY: Unique identifier; automatically creates a clustered index
- FOREIGN KEY Establishes table relationships; supports CASCADE, SET NULL, and RESTRICT
- UNIQUE: Values must be unique; NULL values are allowed
- NOT NULL Does not allow NULL values
- CHECK (8.0+) Ensure the value meets the criteria
📝 Exercises
-
Basic Question (Difficulty: ⭐): Create a user table with complete constraints.
-
Advanced Exercise (Difficulty: ⭐⭐): Create a foreign key and test cascading deletes.
-
Challenge (Difficulty: ⭐⭐⭐): Design a product table with a CHECK constraint and verify that the constraint is enforced.
