SQL Notes
How to use the NOT NULL constraint in SQL to require values in table columns, prevent incomplete records, and enforce mandatory fields with practical examples and real-world scenarios.
Every database has columns that absolutely must have a value. A customer without a name, an order without a date, an employee without an email — these are records that would break your application. The NOT NULL constraint is how you tell the database: "This column must always contain a value. No exceptions."
When you mark a column as NOT NULL, the database will reject any INSERT or UPDATE that tries to leave that column empty. It is the simplest constraint in SQL, but also one of the most important for maintaining clean, reliable data.
Think about it from an application perspective. If your code expects every customer to have an email address and you try to send a welcome email to a NULL value, your application crashes or sends to nowhere. If an order has no date, your reporting queries produce incorrect results. NOT NULL enforces at the database level what your application assumes to be true, creating a safety net that catches mistakes before they propagate through your system and cause downstream failures.
Basic Syntax
Add NOT NULL directly after the column's data type:
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(255) NOT NULL,
Department VARCHAR(50) NOT NULL,
JoinDate DATE NOT NULL,
Phone VARCHAR(20), -- optional
Address TEXT -- optional
);The rule is simple: if a field is essential for the record to make sense, mark it NOT NULL. If it is optional or might not be available at the time of insertion, leave it nullable.
NOT NULL with INSERT Statements
Let us see how NOT NULL behaves with different INSERT patterns:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Name VARCHAR(200) NOT NULL,
Price DECIMAL(10,2) NOT NULL,
Description TEXT,
Stock INT NOT NULL DEFAULT 0
);-- Valid: All NOT NULL columns have values
INSERT INTO Products (ProductID, Name, Price, Description, Stock)
VALUES (1, 'Wireless Mouse', 599.00, 'Ergonomic design', 50);
-- Valid: Description is nullable, Stock has a default
INSERT INTO Products (ProductID, Name, Price)
VALUES (2, 'USB Cable', 149.00);
-- FAILS: Name is NOT NULL
INSERT INTO Products (ProductID, Price)
VALUES (3, 299.00);
-- ERROR: Column 'Name' cannot be null
-- FAILS: Explicitly passing NULL to a NOT NULL column
INSERT INTO Products (ProductID, Name, Price)
VALUES (4, NULL, 399.00);
-- ERROR: Column 'Name' cannot be nullNOT NULL with UPDATE Statements
NOT NULL is also enforced during UPDATE operations:
-- This FAILS: Cannot set a NOT NULL column to NULL
UPDATE Products SET Name = NULL WHERE ProductID = 1;
-- ERROR: Column 'Name' cannot be null
-- This is fine: Updating to a valid value
UPDATE Products SET Name = 'Bluetooth Mouse' WHERE ProductID = 1;Adding NOT NULL to an Existing Table
You can add NOT NULL to a column that already exists, but there is a catch — all existing rows must already have non-NULL values in that column. If any row has NULL, the ALTER fails.
MySQL
ALTER TABLE Customers
MODIFY COLUMN Phone VARCHAR(20) NOT NULL;PostgreSQL
ALTER TABLE Customers
ALTER COLUMN Phone SET NOT NULL;SQL Server
ALTER TABLE Customers
ALTER COLUMN Phone VARCHAR(20) NOT NULL;If existing rows have NULLs, fix them first:
-- Fill in NULL values before adding the constraint
UPDATE Customers SET Phone = 'Not Provided' WHERE Phone IS NULL;
-- Now you can safely add NOT NULL
ALTER TABLE Customers MODIFY COLUMN Phone VARCHAR(20) NOT NULL;Removing NOT NULL
To make a NOT NULL column nullable again:
MySQL
ALTER TABLE Customers
MODIFY COLUMN Phone VARCHAR(20) NULL;PostgreSQL
ALTER TABLE Customers
ALTER COLUMN Phone DROP NOT NULL;NOT NULL with DEFAULT — The Power Combination
Combining NOT NULL with DEFAULT is one of the most common and useful patterns in SQL. NOT NULL guarantees the column always has a value, and DEFAULT provides that value when none is specified:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATE NOT NULL DEFAULT CURRENT_DATE,
Status VARCHAR(20) NOT NULL DEFAULT 'Pending',
TotalAmount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
Notes TEXT
);-- Only need to provide CustomerID - everything else has defaults
INSERT INTO Orders (OrderID, CustomerID)
VALUES (1, 101);| OrderID | CustomerID | OrderDate | Status | TotalAmount | Notes |
|---|---|---|---|---|---|
| 1 | 101 | 2026-06-14 | Pending | 0.00 | NULL |
Real-World Example: Student Records System
CREATE TABLE Students (
RollNo INT PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
Email VARCHAR(255) NOT NULL,
DateOfBirth DATE NOT NULL,
Course VARCHAR(50) NOT NULL,
Semester INT NOT NULL DEFAULT 1,
Address TEXT,
Phone VARCHAR(15),
GuardianName VARCHAR(100)
);Essential information like name, email, date of birth, and course are marked NOT NULL because a student record is meaningless without them. Optional fields like address, phone, and guardian name remain nullable.
Real-World Example: E-Commerce Products
CREATE TABLE Inventory (
SKU VARCHAR(20) PRIMARY KEY,
ProductName VARCHAR(200) NOT NULL,
Category VARCHAR(50) NOT NULL,
Price DECIMAL(10,2) NOT NULL,
CostPrice DECIMAL(10,2) NOT NULL,
Stock INT NOT NULL DEFAULT 0,
MinStock INT NOT NULL DEFAULT 5,
Supplier VARCHAR(100),
ImageURL TEXT,
Description TEXT
);How NOT NULL Interacts with Other Constraints
PRIMARY KEY implies NOT NULL: A primary key column is automatically NOT NULL. You do not need to specify it separately.
-- Both are equivalent
CustomerID INT PRIMARY KEY
CustomerID INT PRIMARY KEY NOT NULL -- redundant but not wrongUNIQUE does NOT imply NOT NULL: A unique column can contain NULL values (in most databases, multiple NULLs are allowed in a UNIQUE column).
Email VARCHAR(255) UNIQUE -- allows one or more NULLs
Email VARCHAR(255) UNIQUE NOT NULL -- no NULLs allowedFOREIGN KEY does NOT imply NOT NULL: A foreign key column can be NULL unless you explicitly add NOT NULL.
DepartmentID INT REFERENCES Departments(DeptID) -- allows NULL (unassigned)
DepartmentID INT NOT NULL REFERENCES Departments(DeptID) -- must have a departmentCommon Mistakes to Avoid
Mistake 1: Making everything NOT NULL. Not every column needs to be mandatory. Over-constraining your table makes insertions unnecessarily difficult and forces users to provide dummy data.
Mistake 2: Forgetting to handle NULLs before adding the constraint. Adding NOT NULL to an existing column with NULL values will fail. Always clean up data first.
Mistake 3: Confusing empty string with NULL. An empty string '' is not NULL. A NOT NULL column accepts empty strings, which might still be invalid for your use case. Combine with CHECK if needed.
-- NOT NULL alone allows empty strings
Name VARCHAR(100) NOT NULL -- allows ''
-- Add CHECK to prevent empty strings too
Name VARCHAR(100) NOT NULL CHECK (Name <> '')Best Practices
- Mark essential business columns as NOT NULL — name, email, date, amount, status
- Leave truly optional fields nullable — middle name, fax, secondary phone
- Combine NOT NULL with DEFAULT for columns that have sensible initial values
- Use NOT NULL with CHECK when you need both presence and value validation
- Review NULLability during table design — it is easier to add constraints early than to fix data later
- Document which fields are required in your API documentation
Quick Summary
| Scenario | NOT NULL? |
|---|---|
| Customer name | Yes |
| Order date | Yes |
| Employee email | Yes |
| Middle name | No |
| Optional phone | No |
| Status with default | Yes, with DEFAULT |
| Foreign key (optional relationship) | No |
NOT NULL is deceptively simple but profoundly important. It is your first line of defense against incomplete, unusable data. Use it wisely on every column that your application truly needs to function correctly.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for NOT NULL Constraint in SQL.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, constraints, not, null
Related SQL Complete Guide Topics