NOT NULL constraint behavior in SQL

Introduction

The NOT NULL constraint makes sure a column always has a value. It stops empty or missing data in that column.

When you want to make sure every person in a contact list has a phone number.

When you need every product in a store to have a price.

When a user's email address must always be recorded in a signup form.

When a date of birth is required for every employee in a company database.

Syntax

SQL

CREATE TABLE table_name (
  column_name datatype NOT NULL
);

The NOT NULL constraint is added right after the datatype of a column.

It prevents inserting or updating a row with a missing value in that column.

Examples

Creates a Users table where 'id' and 'name' cannot be empty.

SQL

CREATE TABLE Users (
  id INT NOT NULL,
  name VARCHAR(100) NOT NULL
);

Changes the 'email' column in Employees table to NOT NULL, so it must have a value.

SQL

ALTER TABLE Employees
MODIFY email VARCHAR(255) NOT NULL;

Ensures every product has an ID and a price.

SQL

CREATE TABLE Products (
  product_id INT NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

Sample Program

This creates a Customers table where both columns must have values. The first insert works. The second insert fails because customer_name is NULL.

SQL

CREATE TABLE Customers (
  customer_id INT NOT NULL,
  customer_name VARCHAR(50) NOT NULL
);

INSERT INTO Customers (customer_id, customer_name) VALUES (1, 'Alice');

-- This will fail because customer_name is NOT NULL
INSERT INTO Customers (customer_id, customer_name) VALUES (2, NULL);

Important Notes

Trying to insert or update a NULL value in a NOT NULL column causes an error.

NOT NULL helps keep your data complete and reliable.

You can add NOT NULL when creating a table or later by altering the table.

Summary

NOT NULL makes sure a column always has a value.

It stops empty or missing data in that column.

Use it to keep important data complete and accurate.