MySQL NOT NULL Constraint

The NOT NULL constraint in MySQL ensures that a column must always have a value. It prevents empty (NULL) entries from being stored in that column. This is one of the most commonly used constraints in database design.

What is NULL?

In MySQL, NULL means the absence of a value — it is not zero, not an empty string, and not a blank space. It simply means "no value has been provided."

Simple Analogy

Think of a paper form where some fields are mandatory. If a required field is left blank, the form is incomplete. NOT NULL works the same way — it forces a value to be provided for that field.

Syntax

At Table Creation

CREATE TABLE table_name (
    column_name datatype NOT NULL
);

Using ALTER TABLE

ALTER TABLE table_name
MODIFY column_name datatype NOT NULL;

Example: Creating a Table with NOT NULL

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

Here, emp_id and emp_name must always have a value. The department column is optional and can be left empty.

What Happens When NOT NULL is Violated?

If an attempt is made to insert a row without providing a value for a NOT NULL column, MySQL returns an error.

Failing Insert Example

INSERT INTO employees (emp_id, emp_name, department)
VALUES (1, NULL, 'HR');

MySQL returns:

ERROR 1048 (23000): Column 'emp_name' cannot be null

Correct Insert Example

INSERT INTO employees (emp_id, emp_name, department)
VALUES (1, 'Ravi Kumar', 'HR');

Adding NOT NULL to an Existing Column

ALTER TABLE employees
MODIFY department VARCHAR(50) NOT NULL;

This makes the department column mandatory going forward. Note: if the table already has rows with NULL in that column, this command will fail until those values are updated.

Removing NOT NULL from a Column

ALTER TABLE employees
MODIFY department VARCHAR(50) NULL;

This allows department to accept empty values again.

NOT NULL with DEFAULT

Combining NOT NULL with DEFAULT ensures the column always has a value — either the one provided or the default.

CREATE TABLE orders (
    order_id INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Pending'
);

If no status is provided during insert, it automatically uses Pending.

Checking for NOT NULL Columns

Use DESCRIBE to check which columns have the NOT NULL constraint:

DESCRIBE employees;

The Null column in the output shows NO for columns that do not allow NULL.

Key Points

• NOT NULL ensures a column always has a value.
• NULL means the absence of any value — not zero or blank.
• Inserting a NULL into a NOT NULL column causes an error.
• NOT NULL can be added or removed using ALTER TABLE.
• Combining NOT NULL with DEFAULT provides a fallback value automatically.