MySQL Constraints

In MySQL, constraints are rules applied to table columns to control what kind of data gets stored. They ensure accuracy, consistency, and reliability of data in a database. Constraints prevent invalid or incomplete data from being inserted into a table.

Why Constraints Are Important

Without constraints, a database can store incorrect or incomplete data. For example, a student record might be saved without a name, or two employees might receive the same ID. Constraints prevent such situations by enforcing rules at the database level.

Types of Constraints in MySQL

MySQL supports the following constraints:

Constraint	Purpose
NOT NULL	Ensures a column cannot store a NULL (empty) value
UNIQUE	Ensures all values in a column are different
PRIMARY KEY	Uniquely identifies each row in a table
FOREIGN KEY	Links two tables together based on a related column
DEFAULT	Sets a default value when no value is provided
CHECK	Ensures column values meet a specific condition

How Constraints Are Applied

Constraints can be added in two ways:

1. Column-Level Constraint

The constraint is defined directly with the column during table creation.

CREATE TABLE employees (
    emp_id INT NOT NULL,
    emp_name VARCHAR(100) NOT NULL
);

2. Table-Level Constraint

The constraint is defined separately after all column definitions.

CREATE TABLE employees (
    emp_id INT,
    emp_name VARCHAR(100),
    CONSTRAINT pk_emp PRIMARY KEY (emp_id)
);

Adding Constraints to an Existing Table

Constraints can also be added to an existing table using the ALTER TABLE statement.

ALTER TABLE employees
ADD CONSTRAINT chk_name CHECK (emp_name != '');

Removing a Constraint

A named constraint can be dropped using ALTER TABLE.

ALTER TABLE employees
DROP CONSTRAINT chk_name;

Example: Table with Multiple Constraints

CREATE TABLE students (
    student_id INT NOT NULL,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INT CHECK (age >= 5),
    city VARCHAR(50) DEFAULT 'Delhi',
    PRIMARY KEY (student_id)
);

In this example:

student_id cannot be NULL and uniquely identifies each student
student_name must always have a value
email must be unique across all rows
age must be 5 or greater
city defaults to Delhi if not provided

Key Points

Constraints enforce rules on the data stored in a table.
They can be applied at the column level or table level.
Constraints can be added during table creation or later using ALTER TABLE.
Named constraints are easier to manage and remove.
Using constraints reduces the chance of storing invalid or inconsistent data.

Previous lessons

Back to courses

Next lessons