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.

Query:

CREATE TABLE Student (
    StuID INT NOT NULL,
    FullName VARCHAR(50),
    City VARCHAR(50)
);

--Insert data into Student table

INSERT INTO Student (StuID, FullName, City) VALUES
(101, 'Bob', 'London'),
(102, 'Lucas', 'London');

Output:

• StuID is defined as NOT NULL, so it must always have a value.
• The database will reject any NULL value inserted into StuID.
• Trying to insert NULL will cause a NOT NULL constraint error.

Query:

INSERT INTO Student (StuID, FullName, City) VALUES
(NULL, 'John', 'London');

Error:

• The insert fails because StuID has a NULL value.
• A NOT NULL column requires a valid value, so it rejects NULL.

Syntax:

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

• 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.

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 of SQL NOT NULL Constraint

Let's understand NOT NULL in SQL with examples. Here we will look at different examples of the SQL NOT NULL Constraint command. First, we create a demo SQL database and table, on which we use the NOT NULL Constraint command.

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)
);

-- Insert valid rows (EmpID has values, so inserts succeed)

INSERT INTO Emp (EmpID, Name, Country, Age, Salary) VALUES
(1, 'John', 'USA', 28, 35000),
(2, 'Emma', 'Canada', 32, 55000),
(3, 'Lucas', 'Germany', 26, 42000);

Output:

• All rows insert successfully because EmpID has valid (non-NULL) values.
• NOT NULL constraint is satisfied, so the database accepts the rows.

Query:

-- Attempt to insert a row with NULL in NOT NULL column (this will throw an error)
-- ERROR: Column 'EmpID' cannot be NULL

INSERT INTO Emp (EmpID, Name, Country, Age, Salary) VALUES
(NULL, 'Oliver', 'France', 30, 50000);

Error:

• Fails because EmpID cannot be NULL due to the NOT NULL constraint.
• Database returns an error saying the EmpID column cannot accept NULL values.

Example 2: Adding NOT NULL to an Existing Table

You can add a NOT NULL constraint to an existing table using ALTER TABLE. Suppose the Student table already exists which allows NULL values, and we now want to make the StuID column non-nullable.

Query:

ALTER TABLE Student
MODIFY COLUMN StuID SET NOT NULL;

• StuID cannot store NULL values anymore.
• Any future insert with NULL StuID will fail with a NOT NULL error.

Query:

INSERT INTO Student (StuID, FullName, City) VALUES
(NULL, 'Mark', 'London');

Error:

• Insert fails because StuID is NULL.
• The NOT NULL column cannot accept NULL values.