Step 8
6 min read

NOT NULL

Learn how to make sure important fields always have values - explained in the simplest way.

What You Will Learn

Users Table with NOT NULL
user_idname (NOT NULL)email (NOT NULL)phone (optional)
1Johnjohn@email.com555-0001
2Marymary@email.comNULL
3Peterpeter@email.comNULL
3 rows
Required vs Optional Fields
Field TypeConstraintCan Be Empty?Example
RequiredNOT NULLNoName, Email
OptionalNo constraintYesPhone, Address
Primary KeyPRIMARY KEYNouser_id
UniqueUNIQUEYes (can be NULL)Username
4 rows
  • What NOT NULL means (super simple)
  • When to use it
  • How it protects your data
  • Easy examples

What is NOT NULL?

Users Table with NOT NULL
user_idname (NOT NULL)email (NOT NULL)phone (optional)
1Johnjohn@email.com555-0001
2Marymary@email.comNULL
3Peterpeter@email.comNULL
3 rows
Required vs Optional Fields
Field TypeConstraintCan Be Empty?Example
RequiredNOT NULLNoName, Email
OptionalNo constraintYesPhone, Address
Primary KeyPRIMARY KEYNouser_id
UniqueUNIQUEYes (can be NULL)Username
4 rows

NOT NULL means "this field must have a value". It cannot be empty.

Think of it like:

  • A form where certain fields are required (marked with red asterisk)
  • You cannot submit without filling them
  • Name, email are required. Phone number is optional.

Real-Life Example

Users Table with NOT NULL
user_idname (NOT NULL)email (NOT NULL)phone (optional)
1Johnjohn@email.com555-0001
2Marymary@email.comNULL
3Peterpeter@email.comNULL
3 rows
Required vs Optional Fields
Field TypeConstraintCan Be Empty?Example
RequiredNOT NULLNoName, Email
OptionalNo constraintYesPhone, Address
Primary KeyPRIMARY KEYNouser_id
UniqueUNIQUEYes (can be NULL)Username
4 rows

Job Application Form:

  • Name: Required (NOT NULL)
  • Email: Required (NOT NULL)
  • Phone: Optional (can be NULL)
  • Address: Optional (can be NULL)

You must provide name and email, but phone and address are optional.

How to Use NOT NULL

Users Table with NOT NULL
user_idname (NOT NULL)email (NOT NULL)phone (optional)
1Johnjohn@email.com555-0001
2Marymary@email.comNULL
3Peterpeter@email.comNULL
3 rows
Required vs Optional Fields
Field TypeConstraintCan Be Empty?Example
RequiredNOT NULLNoName, Email
OptionalNo constraintYesPhone, Address
Primary KeyPRIMARY KEYNouser_id
UniqueUNIQUEYes (can be NULL)Username
4 rows

Example: User Registration

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL, phone VARCHAR(20) );

What this means:

  • name is required (cannot be empty)
  • email is required (cannot be empty)
  • phone is optional (can be empty)

Inserting Data

This works:

INSERT INTO users (name, email, phone) VALUES ('John', 'john@email.com', '555-0001'); INSERT INTO users (name, email) VALUES ('Mary', 'mary@email.com'); -- Phone is NULL (OK)

This fails:

INSERT INTO users (name) VALUES ('Peter'); -- ERROR: email is required INSERT INTO users (email) VALUES ('john@email.com'); -- ERROR: name is required

When to Use NOT NULL

Users Table with NOT NULL
user_idname (NOT NULL)email (NOT NULL)phone (optional)
1Johnjohn@email.com555-0001
2Marymary@email.comNULL
3Peterpeter@email.comNULL
3 rows
Required vs Optional Fields
Field TypeConstraintCan Be Empty?Example
RequiredNOT NULLNoName, Email
OptionalNo constraintYesPhone, Address
Primary KeyPRIMARY KEYNouser_id
UniqueUNIQUEYes (can be NULL)Username
4 rows

Use NOT NULL for:

  • Important information that is always needed
  • Name (everyone has a name)
  • Email for login (required to sign in)
  • Product name (every product needs a name)
  • Order date (every order has a date)

Do NOT use NOT NULL for:

  • Optional information
  • Phone number (not everyone provides it)
  • Middle name (not everyone has one)
  • Company name (not all users have a company)

Real-World Examples

Users Table with NOT NULL
user_idname (NOT NULL)email (NOT NULL)phone (optional)
1Johnjohn@email.com555-0001
2Marymary@email.comNULL
3Peterpeter@email.comNULL
3 rows
Required vs Optional Fields
Field TypeConstraintCan Be Empty?Example
RequiredNOT NULLNoName, Email
OptionalNo constraintYesPhone, Address
Primary KeyPRIMARY KEYNouser_id
UniqueUNIQUEYes (can be NULL)Username
4 rows

Example 1: Product Catalog

CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(200) NOT NULL, price DECIMAL(10, 2) NOT NULL, description TEXT );

Required: product_name, price Optional: description

Example 2: Student Records

CREATE TABLE students ( student_id SERIAL PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, phone VARCHAR(20), address TEXT );

Required: first_name, last_name, email Optional: phone, address

Example 3: Orders

CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INTEGER NOT NULL, order_date DATE NOT NULL, total DECIMAL(10, 2) NOT NULL, notes TEXT );

Required: customer_id, order_date, total Optional: notes

What is NULL?

Users Table with NOT NULL
user_idname (NOT NULL)email (NOT NULL)phone (optional)
1Johnjohn@email.com555-0001
2Marymary@email.comNULL
3Peterpeter@email.comNULL
3 rows
Required vs Optional Fields
Field TypeConstraintCan Be Empty?Example
RequiredNOT NULLNoName, Email
OptionalNo constraintYesPhone, Address
Primary KeyPRIMARY KEYNouser_id
UniqueUNIQUEYes (can be NULL)Username
4 rows

NULL means "no value" or "unknown" or "not provided yet".

Examples of NULL:

  • Phone number not provided: NULL
  • Middle name does not exist: NULL
  • Birthday not entered yet: NULL

NULL is NOT:

  • NULL is not zero (0)
  • NULL is not empty text ("")
  • NULL is not the word "NULL"

Common Mistakes

Users Table with NOT NULL
user_idname (NOT NULL)email (NOT NULL)phone (optional)
1Johnjohn@email.com555-0001
2Marymary@email.comNULL
3Peterpeter@email.comNULL
3 rows
Required vs Optional Fields
Field TypeConstraintCan Be Empty?Example
RequiredNOT NULLNoName, Email
OptionalNo constraintYesPhone, Address
Primary KeyPRIMARY KEYNouser_id
UniqueUNIQUEYes (can be NULL)Username
4 rows

Mistake 1: Forgetting NOT NULL on Important Fields

Wrong:

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, email VARCHAR(100) -- No NOT NULL ); -- Can create user without email (bad!) INSERT INTO users (user_id) VALUES (1);

Correct:

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, email VARCHAR(100) NOT NULL ); -- Must provide email INSERT INTO users (email) VALUES ('john@email.com');

Mistake 2: Using NOT NULL on Everything

Wrong:

CREATE TABLE contacts ( contact_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL, phone VARCHAR(20) NOT NULL, -- Too strict! fax VARCHAR(20) NOT NULL, -- Too strict! address TEXT NOT NULL -- Too strict! );

Better:

CREATE TABLE contacts ( contact_id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL, phone VARCHAR(20), -- Optional fax VARCHAR(20), -- Optional address TEXT -- Optional );

Summary

Users Table with NOT NULL
user_idname (NOT NULL)email (NOT NULL)phone (optional)
1Johnjohn@email.com555-0001
2Marymary@email.comNULL
3Peterpeter@email.comNULL
3 rows
Required vs Optional Fields
Field TypeConstraintCan Be Empty?Example
RequiredNOT NULLNoName, Email
OptionalNo constraintYesPhone, Address
Primary KeyPRIMARY KEYNouser_id
UniqueUNIQUEYes (can be NULL)Username
4 rows

Think of NOT NULL like:

  • Required fields on a form (marked with asterisk)
  • Must fill out before submitting
  • Cannot skip these fields

Key points:

  1. NOT NULL makes a field required
  2. Like mandatory fields on a form
  3. Must provide a value
  4. Cannot be empty
  5. Use for important information
  6. Do not use for optional fields
  7. NULL means "no value"
  8. Protects data quality

What Comes Next

Users Table with NOT NULL
user_idname (NOT NULL)email (NOT NULL)phone (optional)
1Johnjohn@email.com555-0001
2Marymary@email.comNULL
3Peterpeter@email.comNULL
3 rows
Required vs Optional Fields
Field TypeConstraintCan Be Empty?Example
RequiredNOT NULLNoName, Email
OptionalNo constraintYesPhone, Address
Primary KeyPRIMARY KEYNouser_id
UniqueUNIQUEYes (can be NULL)Username
4 rows

Next, you will learn about CHECK Constraint to validate that data meets specific rules.

Finished this topic?

Mark it complete to track your progress and maintain your streak!

Previous
UNIQUE Constraint
Next
CHECK Constraint
SkillsetMaster - AI, Web Development & Data Analytics Courses