In MySQL, a constraint is a rule applied to a column or table that limits the type of data that can be stored. Constraints help maintain data integrity and enforce business rules at the database level.
Here’s a breakdown of the main types of constraints in MySQL:
1. NOT NULL
- Ensures that a column cannot have NULL values.
- Example:
CREATE TABLE Users (
id INT NOT NULL,
name VARCHAR(50) NOT NULL
);
Here, both id and name must have a value; NULL is not allowed.
2. UNIQUE
- Ensures that all values in a column are distinct.
- Example:
CREATE TABLE Users (
id INT NOT NULL,
email VARCHAR(100) UNIQUE
);
No two users can have the same email.
3. PRIMARY KEY
- A combination of NOT NULL and UNIQUE.
- Uniquely identifies each record in a table.
- Example:
CREATE TABLE Users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
id uniquely identifies each row.
4. FOREIGN KEY
- Ensures referential integrity between two tables.
- Links a column in one table to the primary key of another table.
- Example:
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES Users(id)
);
user_id in Orders must match an existing id in Users.
5. CHECK (MySQL 8.0+ only)
- Ensures that a column satisfies a specific condition.
- Example:
CREATE TABLE Products (
id INT PRIMARY KEY,
price DECIMAL(10,2),
CHECK (price > 0)
);
Price cannot be negative or zero.
6. DEFAULT
- Sets a default value for a column if no value is provided.
- Example:
CREATE TABLE Users (
id INT PRIMARY KEY,
status VARCHAR(10) DEFAULT 'active'
);
If no status is provided, it will automatically be 'active'.
✅ Summary: Constraints enforce rules like uniqueness, mandatory values, valid relationships, and valid data ranges, which keeps the database consistent and reliable.