NOT NULL constraint 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 user has an email address in your database.

When storing product prices, and you want to avoid missing price values.

When recording dates of events that must always be known.

When you want to ensure a username is always provided during registration.

When you want to prevent saving incomplete records in any table.

Syntax

SQL

CREATE TABLE table_name (
  column_name data_type NOT NULL
);

You add NOT NULL right after the data type of the column.

This rule applies to each row, so every row must have a value in that column.

Examples

This creates a Users table where both id and name must have values.

SQL

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

Here, product_id and price cannot be empty for any product.

SQL

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

Every event must have an event_id and a date.

SQL

CREATE TABLE Events (
  event_id INT NOT NULL,
  event_date DATE NOT NULL
);

Sample Program

This example creates an Employees table where none of the columns can be empty. The first insert works fine. The second insert fails because email is NULL, which is not allowed. The SELECT shows the rows that were successfully added.

SQL

CREATE TABLE Employees (
  employee_id INT NOT NULL,
  first_name VARCHAR(30) NOT NULL,
  last_name VARCHAR(30) NOT NULL,
  email VARCHAR(50) NOT NULL
);

INSERT INTO Employees (employee_id, first_name, last_name, email) VALUES (1, 'Alice', 'Smith', '[e-mail]');

-- This will fail because email is NOT NULL
INSERT INTO Employees (employee_id, first_name, last_name, email) VALUES (2, 'Bob', 'Jones', NULL);

SELECT * FROM Employees;

Important Notes

NOT NULL helps keep your data complete and reliable.

If you try to insert NULL into a NOT NULL column, the database will give an error.

You can add NOT NULL when creating a table or alter an existing column to add it.

Summary

NOT NULL means a column must always have a value.

It prevents empty or missing data in important columns.

Use NOT NULL to keep your data clean and trustworthy.