NOT NULL Constraint in SQL

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

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

Here, both student_id and name must have values.

NOT NULL is used when a field is required for every record.

For example, a student record may require:

• Student name
• Mobile number
• Course name

These fields should not be left NULL.

The basic syntax is:

column_name data_type NOT NULL

Example:

name VARCHAR(100) NOT NULL

You can define NOT NULL while creating a table.

CREATE TABLE students (
    id INT,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15) NOT NULL
);

The name and mobile columns cannot contain NULL.

A valid record provides values for NOT NULL columns.

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

This record satisfies the NOT NULL requirement.

If you try to insert NULL into a NOT NULL column, the database rejects the operation.

INSERT INTO students (id, name, mobile)
VALUES (2, NULL, '9876543211');

This causes an error because name is defined as NOT NULL.

NULL means that a value is missing or unknown.

An empty string '' is a string containing zero characters.

INSERT INTO students (name)
VALUES ('');

Depending on the database and column definition, an empty string is not the same as NULL.

NULL and zero are different values.

marks = NULL
marks = 0

NULL means no value is stored, while 0 is an actual numeric value.

NOT NULL can be applied to numeric columns.

CREATE TABLE courses (
    course_id INT NOT NULL,
    fee INT NOT NULL
);

Both columns must contain values.

NOT NULL is commonly used with text columns.

CREATE TABLE students (
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL
);

Both name and email are required.

Date columns can also use NOT NULL.

CREATE TABLE admissions (
    student_id INT NOT NULL,
    admission_date DATE NOT NULL
);

Every admission record must contain an admission date.

NOT NULL can be combined with a DEFAULT value.

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

If no status is supplied, the default value can be used.

A PRIMARY KEY identifies each record uniquely and cannot contain NULL values.

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

The primary key is inherently non-null.

UNIQUE and NOT NULL serve different purposes.

• UNIQUE prevents duplicate values.
• NOT NULL prevents NULL values.

email VARCHAR(150) NOT NULL UNIQUE

This requires an email value and prevents duplicate email values.

A table can contain many NOT NULL columns.

CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(15) NOT NULL,
    salary DECIMAL(10,2) NOT NULL
);

All four columns require values.

Before changing an existing column to NOT NULL, check whether NULL values already exist.

SELECT *
FROM students
WHERE mobile IS NULL;

If rows are returned, those NULL values should be handled before adding NOT NULL.

In MySQL, you can modify an existing column using ALTER TABLE.

ALTER TABLE students
MODIFY mobile VARCHAR(15) NOT NULL;

The existing data must satisfy the new NOT NULL requirement.

You can define both NOT NULL and DEFAULT while modifying a column.

ALTER TABLE students
MODIFY status VARCHAR(20) NOT NULL DEFAULT 'Active';

This makes the column required and provides a default value.

You cannot update a NOT NULL column to NULL.

UPDATE students
SET name = NULL
WHERE id = 1;

This violates the NOT NULL constraint.

Use IS NOT NULL to find records where a column contains a value.

SELECT *
FROM students
WHERE mobile IS NOT NULL;

This returns records where mobile is not NULL.

Use these operators to check NULL values.

SELECT * FROM students
WHERE mobile IS NULL;

SELECT * FROM students
WHERE mobile IS NOT NULL;

Do not use = NULL to test for NULL.

A practical student table can use NOT NULL for required information.

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

Each student must have these basic details.

Employee tables can also require important information.

CREATE TABLE employees (
    employee_id INT NOT NULL,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(100) NOT NULL
);

These fields cannot be NULL.

Course information can use NOT NULL for required fields.

CREATE TABLE courses (
    course_id INT NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    fee DECIMAL(10,2) NOT NULL
);

A course must have an ID, name, and fee.

In MySQL, you can modify a column without NOT NULL to allow NULL values.

ALTER TABLE students
MODIFY mobile VARCHAR(15);

The column is no longer explicitly defined as NOT NULL.

If a NOT NULL column does not have a default value, an INSERT must provide an appropriate value for it.

INSERT INTO students (student_id)
VALUES (101);

If other required columns have no default values, this statement can fail because their values are missing.

A common mistake is trying to store NULL in a required column.

INSERT INTO students (student_id, name)
VALUES (101, NULL);

If name is NOT NULL, the database rejects the operation.

NOT NULL helps maintain data integrity by ensuring that required information is always present.

For example, if every student must have a name, defining name as NOT NULL helps prevent incomplete records.

Let's create a simple student table using NOT NULL.

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

Here, the student's ID, name, mobile, course, and status are required.

Here is a complete example using NOT NULL and inserting valid data.

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

INSERT INTO students
(student_id, name, mobile, course)
VALUES
(1, 'Rahul', '9876543210', 'Python'),
(2, 'Priya', '9876543211', 'SQL');

All required columns contain values, so these records satisfy the NOT NULL constraint.