← Back to Chapters

MySQL NOT NULL Constraint

? MySQL NOT NULL Constraint

⚡ Quick Overview

The NOT NULL constraint in MySQL ensures that a column cannot store NULL values. It is commonly used on important fields like IDs, names, and other required data.

✅ Use for mandatory fields

? Key Concepts

  • NULL means “no value” or “unknown value”, not zero or an empty string.
  • NOT NULL forces the column to always have some value when a row is inserted or updated.
  • If you try to insert NULL (or omit the value) for a NOT NULL column, MySQL will raise an error.
  • Often combined with PRIMARY KEY, UNIQUE, and default values.

? Syntax

Basic syntax to define a column with the NOT NULL constraint while creating a table:

? View Code Example 
-- Defining a column with NOT NULL constraint
CREATE TABLE table_name (
column1 datatype NOT NULL
);

? Example: Employees Table

Here is an example showing how to use NOT NULL on multiple columns in an Employees table:

? View Code Example 
-- Creating a table with NOT NULL columns
CREATE TABLE Employees (
ID INT NOT NULL,
Name VARCHAR(100) NOT NULL,
Age INT,
Salary DECIMAL(10,2)
);

? What This Means

  • ID and Name must always have values when inserting a row.
  • Age and Salary are optional and can be NULL.
  • Trying to insert a row without ID or Name will cause an error.

? Inserting Data with NOT NULL

Let’s see valid and invalid insert statements for this table:

? View Code Example 
-- ✅ Valid insert: all NOT NULL columns have values
INSERT INTO Employees (ID, Name, Age, Salary)
VALUES (1, 'Alice', 28, 55000.00);

-- ✅ Valid insert: optional columns can be NULL
INSERT INTO Employees (ID, Name, Age, Salary)
VALUES (2, 'Bob', NULL, NULL);

-- ❌ Invalid insert: Name is NOT NULL but omitted
INSERT INTO Employees (ID, Age, Salary)
VALUES (3, 30, 60000.00);

? Output / Behavior

  • The first two INSERT statements succeed because ID and Name have values.
  • The third INSERT fails because Name is defined as NOT NULL but no value is supplied.

? Modifying NOT NULL on Existing Columns

You can add or remove the NOT NULL constraint using ALTER TABLE:

? View Code Example 
-- Adding NOT NULL to an existing column
ALTER TABLE Employees
MODIFY COLUMN Age INT NOT NULL;

-- Removing NOT NULL from an existing column
ALTER TABLE Employees
MODIFY COLUMN Age INT NULL;

ℹ️ Explanation

  • Before adding NOT NULL, existing rows must not contain NULL in that column.
  • If any row has NULL in Age, the first ALTER will fail until you update those rows.

✅ Tips & Best Practices

  • Use NOT NULL for columns where a value is required for every record, such as identifiers, usernames, or mandatory fields.
  • If a column is part of a PRIMARY KEY, MySQL will automatically treat it as NOT NULL.
  • Combine NOT NULL with DEFAULT values to avoid errors when no value is provided explicitly.
  • If you want to allow missing or unknown values, do not use NOT NULL on that column.

? Try It Yourself / Practice Tasks

  • Create a Students table with columns: StudentID (INT, NOT NULL), FullName (VARCHAR, NOT NULL), Email (VARCHAR, optional), and Age (INT, optional).
  • Insert records into the Students table and try omitting StudentID or FullName to observe the NOT NULL error.
  • Use ALTER TABLE to add a NOT NULL constraint to the Email column, then try inserting a row without an email.
  • Experiment with removing NOT NULL from a column and verify that it now accepts NULL values.