SQL NOT NULL Constraint

In SQL, NOT NULL constraint in SQL ensures a column must always contain a value and cannot be left empty. Unlike a PRIMARY KEY, which uniquely identifies each record and also disallows NULLs, NOT NULL only enforces the presence of data without requiring uniqueness.

• NOT NULL is used to enforce mandatory fields.
• It prevents NULL values from being inserted or updated.
• It is applicable at the column level.
• It can be used during table creation or modification (with the ALTER command).

Syntax:

CREATE TABLE table_Name
(
column1 data_type(size) NOT NULL,
column2 data_type(size) NOT NULL,
....
);

In the above syntax:

• CREATE TABLE table_name: creates a new table.
• column1 data_type(size): defines a column with its name, data type, and size.
• NOT NULL: ensures the column cannot have NULL (empty) values.
• Repeat for other columns as needed.

SQL NOT NULL Constraint Syntax

The syntax for applying the NOT NULL constraint can be as follows:

1. During Table Creation:

CREATE TABLE table_Name (
    column1 data_type(size) NOT NULL,
    column2 data_type(size) NOT NULL,
    ...
);

2. Modifying an Existing Table:

You can also add a NOT NULL constraint to an existing column in a table using the ALTER TABLE statement:

ALTER TABLE table_name
MODIFY column_name data_type(size) NOT NULL;

Example 1: Applying NOT NULL in Table Creation

Let’s look at an example where we create an EMPLOYEES table with a NOT NULL constraint applied to the EmpID column to ensure that each employee has a unique, non-null ID.

Query:

CREATE TABLE Emp (
    EmpID INT NOT NULL PRIMARY KEY,
    Name VARCHAR(50),
    Country VARCHAR(50),
    Age INT(2),
    Salary INT(10)
);

In this table:

• EmpID is a mandatory field, ensuring no employee record can exist without an ID.

Example 2: Adding NOT NULL to an Existing Table

If you have an existing table, you can add a NOT NULL constraint to a column using the ALTER TABLE statement. Let’s assume the Emp table already exists, and we now want to make the Name column non-nullable.

Query:

ALTER TABLE Emp 
MODIFY Name VARCHAR(50) NOT NULL;

• Now, the Name column will not accept NULL values, meaning every employee record must have a name.

Real-World Use Case of NOT NULL

Consider an Orders table in an e-commerce database. For each order, you would likely have mandatory fields such as OrderID, CustomerID, ProductID, and OrderDate. These fields should never be NULL, as they represent essential information for tracking and processing orders.

Example Orders Table:

CREATE TABLE Orders (
    OrderID INT NOT NULL PRIMARY KEY,
    CustomerID INT NOT NULL,
    ProductID INT NOT NULL,
    OrderDate DATE NOT NULL
);

This table ensures that:

• Every order has a unique OrderID.
• CustomerID, ProductID, and OrderDate must always be provided.