MySQL NOT NULL Constraint

By default, any column in a MySQL table can hold a NULL value — meaning a row can be saved without providing a value for that column. The NOT NULL constraint changes this behavior: it forces a column to always contain a value. If you try to insert or update a row without providing a value for a NOT NULL column, MySQL will reject it with an error.

This is one of the simplest yet most important constraints in MySQL, because it protects your data from being incomplete in places where a value is absolutely required.

---

NULL vs NOT NULL — What is the Difference?

Understanding NULL in MySQL is important before working with the constraint. NULL does not mean zero or an empty string — it means no value at all, or unknown.

---

Syntax

You apply NOT NULL directly after the column's data type when creating or modifying a table:

column_name data_type NOT NULL

---

Using NOT NULL in CREATE TABLE

The most common place to define NOT NULL is when you first create a table. Any column where a value is always required should be marked with NOT NULL.

The example below creates a students table where both name and email are required fields:

CREATE TABLE students (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(20),
    PRIMARY KEY (id)
);

In this table:

• id, name, and email are required — they cannot be left empty.
• phone has no constraint, so it is optional — a student can be added without a phone number.

---

What Happens When NOT NULL is Violated?

If you try to insert a row and leave out a NOT NULL column, MySQL immediately rejects the operation and returns an error:

-- This will fail because 'name' is NOT NULL
INSERT INTO students (email) VALUES ('sara@example.com');

-- ERROR 1364 (HY000): Field 'name' doesn't have a default value

To insert successfully, every NOT NULL column must receive a value:

-- Correct — all NOT NULL columns are provided
INSERT INTO students (name, email) VALUES ('Sara Ahmed', 'sara@example.com');

---

Adding NOT NULL to an Existing Column

If a table already exists and you want to enforce NOT NULL on a column, use ALTER TABLE with MODIFY COLUMN:

ALTER TABLE students
MODIFY COLUMN phone VARCHAR(20) NOT NULL;

You can find rows with missing values before making the change:

-- Check for rows where phone is NULL
SELECT * FROM students WHERE phone IS NULL;

Then update those rows with a value before altering the column:

UPDATE students SET phone = 'N/A' WHERE phone IS NULL;

Now the ALTER TABLE command will succeed.

---

Removing the NOT NULL Constraint

To make a column optional again — allowing NULL values — use ALTER TABLE and simply omit NOT NULL from the column definition:

ALTER TABLE students
MODIFY COLUMN phone VARCHAR(20);

After this change, the phone column will accept NULL values again, making it optional when inserting rows.

---

Verifying NOT NULL Constraints

You can inspect which columns have NOT NULL enforced by using the DESCRIBE command:

DESCRIBE students;

MySQL returns a result like this:

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NULL	auto_increment
name	varchar(100)	NO		NULL
email	varchar(150)	NO		NULL
phone	varchar(20)	YES		NULL

The Null column shows NO for columns with the NOT NULL constraint and YES for columns that allow NULL.

---

Key Points to Remember

• By default, every MySQL column allows NULL. NOT NULL overrides this behavior.
• NOT NULL is declared directly after the column data type in a CREATE TABLE or ALTER TABLE statement.
• Inserting a row without providing a value for a NOT NULL column will cause MySQL to return an error.
• Before adding NOT NULL to an existing column, ensure no current rows have NULL in that column.
• Use DESCRIBE table_name to check which columns enforce NOT NULL — look for NO in the Null column.
• NULL is not the same as 0 or an empty string — it means the value is completely absent.