MySQL NOT NULL Constraint

Introduction

In MySQL, constraints are rules applied to table columns to ensure that the data stored in the database remains accurate and reliable. One of the most commonly used constraints is the NOT NULL constraint.

The NOT NULL constraint ensures that a column cannot contain NULL values. This means every record must have a value for that column.

What is NULL in MySQL

In databases, NULL represents the absence of a value. It does not mean zero or an empty string. It simply means that no value has been assigned.

Example of NULL values:

IDNameEmail
1Rahulrahul@email.com
2AishaNULL

If a column has a NOT NULL constraint, NULL values cannot be inserted.

Why Use NOT NULL Constraint

The NOT NULL constraint is useful when certain fields must always contain data.

Common examples include:

  • User ID

  • Username

  • Product name

  • Order ID

Without this constraint, incomplete or missing data could be stored in the database.

Syntax of NOT NULL Constraint

The NOT NULL constraint is defined while creating a table.

Example:

CREATE TABLE students (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
age INT
);

In this example:

  • id cannot be NULL

  • name cannot be NULL

  • Age can contain NULL values

Adding NOT NULL to an Existing Table

The constraint can also be added later using ALTER TABLE.

Example:

ALTER TABLE students
MODIFY name VARCHAR(50) NOT NULL;

Key Points to Remember

  • NOT NULL prevents columns from storing NULL values

  • It ensures important fields always contain data.

  • It improves data accuracy and reliability.

  • It can be defined while creating or modifying a table