MySQL NOT NULL Constraint

The NOT NULL constraint prevents a column from storing NULL values.

CREATE TABLE students (
    student_id INT,
    name VARCHAR(100) NOT NULL
);

Every student record must have a value in the name column.

NOT NULL is used when a value is required.

• Student name
• Course name
• Admission date
• Employee name
• Product name
• Required email address

The basic syntax is:

column_name data_type NOT NULL

Example:

name VARCHAR(100) NOT NULL

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100)
);

Here, the name column cannot contain NULL.

INSERT INTO students
(student_id, name, course)
VALUES
(1, 'Amit', 'Python');

This works because the name column contains a value.

The following statement fails because name is NOT NULL:

INSERT INTO students
(student_id, name, course)
VALUES
(2, NULL, 'Java');

MySQL rejects the row because name cannot contain NULL.

NULL means that a value is missing or unknown. It is not the same as:

• 0
• An empty string ''
• The word 'NULL'

NULL
0
''
'NULL'

These represent different values or concepts in MySQL.

You can apply NOT NULL to several columns.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100) NOT NULL,
    mobile VARCHAR(15) NOT NULL
);

All three columns require values.

When inserting a row, required NOT NULL columns should be supplied with valid values.

INSERT INTO students
(student_id, name, course, mobile)
VALUES
(1, 'Amit', 'Python', '9876543210');

This record satisfies the NOT NULL requirements.

If a NOT NULL column is omitted from an INSERT statement and has no suitable default value, MySQL rejects the insert in strict SQL modes.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

INSERT INTO students
(student_id)
VALUES
(1);

The name column has no value, so the insert fails in normal strict configurations.

NOT NULL can be combined with DEFAULT.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Active'
);

If status is omitted during INSERT, MySQL uses the default value.

A PRIMARY KEY is implicitly NOT NULL.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

The student_id column cannot contain NULL because it is the PRIMARY KEY.

UNIQUE and NOT NULL can be combined when a value must both exist and be unique.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE
);

Every user must provide an email, and duplicate emails are not allowed.

You can modify an existing column using ALTER TABLE.

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

Before making the column NOT NULL, existing rows must not contain NULL values.

You can allow NULL values again by modifying the column without NOT NULL.

ALTER TABLE students
MODIFY name VARCHAR(100) NULL;

The name column can now store NULL values.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(100)
);

The employee name must be provided, while department can be NULL.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age INT NOT NULL
);

The age column cannot contain NULL.

Note that 0 is a value and is different from NULL.

CREATE TABLE admissions (
    admission_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    admission_date DATE NOT NULL
);

Both student_name and admission_date are required.

CREATE TABLE fees (
    fee_id INT PRIMARY KEY,
    student_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

Both student_id and amount must have values.

NOT NULL prevents NULL, but an empty string is still a string value.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

This is technically not NULL:

INSERT INTO students
(id, name)
VALUES
(1, '');

If an application should also reject empty strings, additional validation may be needed.

Database constraints and application validation can work together.

For example, a registration form can check whether the name is entered before sending data to MySQL.

if(empty($name)){
    echo "Name is required.";
}

The database should still use NOT NULL when the value is required at the database level.

Use DESCRIBE to inspect whether a column allows NULL.

DESCRIBE students;

The Null column shows whether NULL is allowed for each column.

SHOW CREATE TABLE displays the complete table definition.

SHOW CREATE TABLE students;

You can use it to check NOT NULL, DEFAULT, PRIMARY KEY, UNIQUE, and other constraints.

• Trying to insert NULL into a NOT NULL column
• Forgetting to provide required values during INSERT
• Adding NOT NULL when existing rows contain NULL
• Confusing NULL with an empty string
• Confusing NULL with zero
• Assuming NOT NULL automatically prevents duplicate values
• Forgetting to use a DEFAULT value when appropriate

You cannot change a NOT NULL column to NULL unless the column allows NULL.

UPDATE students
SET name = NULL
WHERE student_id = 1;

If name is NOT NULL, MySQL rejects the update.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100) NOT NULL,
    mobile VARCHAR(15) NOT NULL,
    admission_date DATE NOT NULL
);

Here, every student must have a student code, name, course, mobile number, and admission date.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Active',
    admission_date DATE NOT NULL
);

INSERT INTO students
(name, admission_date)
VALUES
('Amit', '2026-09-21');

The status column is NOT NULL, but because it has a DEFAULT value, MySQL can automatically use Active when status is omitted.

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_code VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    course VARCHAR(100) NOT NULL,
    mobile VARCHAR(15) NOT NULL,
    fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(20) NOT NULL DEFAULT 'Active'
);

INSERT INTO students
(student_code, name, course, mobile)
VALUES
('STU001', 'Amit Kumar', 'Python', '9876543210');

INSERT INTO students
(student_code, name, course, mobile)
VALUES
('STU002', 'Priya Singh', 'Java', '9876543211');

SELECT *
FROM students;

DESCRIBE students;

SHOW CREATE TABLE students;

In this example, the important student information cannot be NULL, while fee and status receive default values when they are not supplied.