MySQL Constraints

MySQL Constraints

When we create a table in MySQL, data types define what kind of data a column can store.
But that is not enough.

We also need rules that control:

• Whether a value is required
• Whether it must be unique
• Whether it must match another table
• Whether it follows certain conditions

These rules are called Constraints.

In MySQL, constraints help maintain data integrity, which means the data remains accurate, consistent, and reliable.

1. Why Constraints Are Important

Without constraints:

• Duplicate data may enter the database.
• Important fields may be left empty.
• Invalid relationships may be created.
• Incorrect values may be stored.

Example problem without constraints:

INSERT INTO Users VALUES (1, NULL, 'abc@gmail.com');

If the name should never be empty, we must enforce it using NOT NULL.

So constraints:

• Prevent invalid data
• Protect data accuracy
• Maintain relationships between tables
• Reduce logical errors

2. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot store NULL values.

Example:

CREATE TABLE Students (

    id INT NOT NULL,

    name VARCHAR(100) NOT NULL,

    age INT

);

Here:

• id and name must have values.
• Age can be empty.

If you try:

INSERT INTO Students (id, age) VALUES (1, 20);

It will give an error because a name is required.

Use NOT NULL for:

• Names
• IDs
• Required fields

3. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different.

Example:

CREATE TABLE Users (

    id INT,

    email VARCHAR(100) UNIQUE

);

Now:

• No two users can have the same email.

If you insert:

INSERT INTO Users VALUES (1, 'abc@gmail.com');

INSERT INTO Users VALUES (2, 'abc@gmail.com');

The second insert will fail.

Important:

• A table can have multiple UNIQUE columns.
• UNIQUE allows NULL (unless NOT NULL is also used).

4. DEFAULT Constraint

The DEFAULT constraint assigns a value automatically if no value is provided.

Example:

CREATE TABLE Orders (

    id INT,

    status VARCHAR(20) DEFAULT 'Pending'

);

If you insert:

INSERT INTO Orders (id) VALUES (101);

The status will automatically be:

Pending

Use DEFAULT for:

• Status fields
• Boolean fields
• Timestamps

5. CHECK Constraint

The CHECK constraint ensures that a column value satisfies a specific condition.

Example:

CREATE TABLE Employees (

    id INT,

    age INT CHECK (age >= 18)

);

Now:

• Age must be 18 or greater.
• If the age is 16, insertion fails.

Another example:

salary DECIMAL(10,2) CHECK (salary > 0)

This ensures the salary is always positive.

CHECK helps prevent logically invalid data.

6. PRIMARY KEY

The PRIMARY KEY uniquely identifies each row in a table.

Rules of Primary Key:

• Must be UNIQUE
• Cannot be NULL
• Only one primary key per table

Example:

CREATE TABLE Students (

    id INT PRIMARY KEY,

    name VARCHAR(100)

);

Here:

• Each student must have a unique id.
• id cannot be NULL.

Primary Key = NOT NULL + UNIQUE (combined).

You can also create a composite primary key:

PRIMARY KEY (order_id, product_id)

7. FOREIGN KEY

A FOREIGN KEY creates a relationship between two tables.

It links:

• A column in one table
• To the PRIMARY KEY in another table

Example:

CREATE TABLE Departments (

    dept_id INT PRIMARY KEY,

    dept_name VARCHAR(100)

);

 

CREATE TABLE Employees (

    emp_id INT PRIMARY KEY,

    name VARCHAR(100),

    dept_id INT,

    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)

);

Now:

• An employee cannot have a dept_id that does not exist in the Departments table.

This maintains referential integrity.

ON DELETE and ON UPDATE

You can control behavior like:

FOREIGN KEY (dept_id)

REFERENCES Departments(dept_id)

ON DELETE CASCADE

This means:
If a department is deleted → all related employees are deleted automatically.

8. How Constraints Maintain Data Integrity

Data Integrity means:

• Data is correct
• Data is consistent
• Relationships are valid

Let’s see how each constraint helps:

Constraint	Maintains What?
NOT NULL	Required data is not missing
UNIQUE	No duplicate values
DEFAULT	Consistent default values
CHECK	Logical correctness
PRIMARY KEY	Unique row identification
FOREIGN KEY	Valid table relationships

Together, they ensure:

• No duplicate IDs
• No invalid foreign references
• No negative salary
• No empty required fields

That is how databases stay reliable.

Final Summary

Constraints in MySQL are rules applied to table columns to enforce data accuracy and consistency.

Main constraints:

• NOT NULL
• UNIQUE
• DEFAULT
• CHECK
• PRIMARY KEY
• FOREIGN KEY

Always remember:

Data types define the format of data.
Constraints define the rules for data.