NOT NULL in SQL

đŸšĢ The NOT NULL constraint in SQL is used to ensure that a column cannot store NULL values. It guarantees that every row must contain a valid value for that column, helping maintain data integrity and preventing incomplete records.

📖 What is the NOT NULL Constraint?

By default, many SQL databases allow columns to contain NULL values, which represent missing or unknown data. Applying the NOT NULL constraint makes a column mandatory, meaning every new record must provide a value for that column.

Information

NULL is different from an empty string ( '') or the number 0. It represents the absence of a value.

đŸŽ¯ Why Use NOT NULL?

The NOT NULL constraint ensures that important information is always provided.

  • 📌 Prevent incomplete records.
  • 📌 Improve data quality and consistency.
  • 📌 Enforce mandatory fields.
  • 📌 Reduce unexpected NULL-related errors.
  • 📌 Support reliable querying and reporting.

📝 Basic Syntax

NOT NULL Syntax

CREATE TABLE table_name
(
    column_name data_type NOT NULL
);

💡 Create a Table with NOT NULL

Create a Students table where the student's name and department are required.

NOT NULL Example

CREATE TABLE Students
(
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Department VARCHAR(100) NOT NULL,
    Age INT
);

In this table:

  • StudentID must contain a value because it is the primary key.
  • Name cannot be NULL.
  • Department cannot be NULL.
  • Age may contain NULL.

✅ Valid INSERT

This statement succeeds because all required columns have values.

Valid INSERT

INSERT INTO Students
(StudentID, Name, Department, Age)
VALUES
(101, 'Alice', 'Computer Science', 20);

❌ Invalid INSERT

This statement fails because the Name column is defined as NOT NULL.

Invalid INSERT

INSERT INTO Students
(StudentID, Department, Age)
VALUES
(102, 'Mathematics', 21);

Error

The database rejects the insert because a value for the Name column is required.

🔄 Add NOT NULL to an Existing Column

If a table already exists, you can modify a column to make it mandatory. Ensure that existing rows do not contain NULL values before applying the constraint.

MySQL Example

ALTER TABLE Students
MODIFY Name VARCHAR(100) NOT NULL;

SQL Server Example

ALTER TABLE Students
ALTER COLUMN Name VARCHAR(100) NOT NULL;

Warning

If existing rows contain NULL values, most database systems will not allow the constraint to be added until those values are updated.

📊 Example Table

StudentIDNameDepartmentAge
101AliceComputer Science20
102BobMathematics21

⚖ī¸ NULL vs NOT NULL

FeatureNULLNOT NULL
Allows Missing Values✅ Yes❌ No
Requires a Value❌ No✅ Yes
Typical UseOptional information.Mandatory information.

đŸ’ŧ Real-World Example

In an employee management system, every employee must have an employee ID, full name, and date of joining. These fields should never be empty.

Employee Table

CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY,
    FullName VARCHAR(100) NOT NULL,
    JoinDate DATE NOT NULL,
    PhoneNumber VARCHAR(20)
);

Here, PhoneNumber is optional, but the employee's identity and joining date are mandatory.

⚠ī¸ Common Mistakes

  • ❌ Applying NOT NULL to columns that should remain optional.
  • ❌ Adding the constraint before cleaning existing NULL values.
  • ❌ Confusing an empty string with NULL.
  • ❌ Forgetting to provide values during INSERT operations.

Warning

Before adding a NOT NULL constraint to an existing table, update or replace any existing NULL values to avoid errors.

⚠ī¸ Best Practices

Best Practice

Apply NOT NULL only to columns that are truly mandatory, combine it with constraints such as PRIMARY KEY and UNIQUE where appropriate, validate user input before inserting data, and carefully design mandatory fields during database planning.

🚀 Key Points to Remember

  • 📌 NOT NULL prevents a column from storing NULL values.
  • 📌 It helps maintain complete and reliable data.
  • 📌 Required values must be provided during INSERT and UPDATE operations.
  • 📌 Existing NULL values must usually be removed before adding the constraint.
  • 📌 The syntax for modifying existing columns varies across database systems.
  • 📌 Use NOT NULL only for fields that are truly required.
>>"Mandatory data leads to reliable databases, and the NOT NULL constraint is the first step toward enforcing it."

Summary

✅ The NOT NULL constraint is one of the most fundamental SQL constraints. It ensures that essential columns always contain valid values, improving data integrity, consistency, and the overall reliability of your database applications.