NOT NULL Constraint in MySQL

The NOT NULL constraint is one of the most commonly used constraints in MySQL that prevents NULL values from being stored in a column.

This constraint ensures that every row in the table must contain a value for that column. The NOT NULL constraint improves data accuracy and reliability by enforcing mandatory values in specific columns. In simple words, a column with a NOT NULL constraint cannot remain empty during data insertion or update operations.

By default, a column can hold NULL values, meaning that when you create a column in MySQL without using the NOT NULL constraint, MySQL automatically stores NULL values in that column.

What is NULL in MySQL?

In MySQL, NULL represents a missing, unknown, or undefined value. It means that no value has been stored in a particular column of a table. A NULL value is different from zero, an empty string, or a space.

Examples of NULL Value

For example, suppose a student has not provided a phone number. In this case, the phone number column may contain NULL.

In this example:

• John has a phone number.
• Smith does not have a phone number stored in the database. Therefore, MySQL stores NULL in that column.

Similarly, if an employee has not yet received an official email address, the email column may store NULL.

In this example, Alex does not yet have an email address assigned, so the Email column contains NULL.

Why Use NOT NULL Constraint in MySQL?

The NOT NULL constraint in MySQL is used for the following purposes:

• Prevents empty values from being stored in important columns.
• Maintains data consistency.
• Improves data accuracy.
• Ensures required fields always contain values.
• Reduces the chances of invalid records.
• Helps in application validation.

Syntax of NOT NULL Constraint

When you create a table in MySQL, you can define a NOT NULL constraint while creating the table. You simply need to add the NOT NULL constraint after the data type of the column. The general syntax to use NOT NULL constraint in MySQL is:

CREATE TABLE table_name (
    column_name1 datatype NOT NULL,
    column_name2 datatype,
    column_name3 datatype NOT NULL
);

In this syntax:

• CREATE TABLE: Used to create a new table in the database.
• table_name: Name of the table to be created.
• column_name: Name of the column in the database table.
• datatype: Specifies the type of data (e.g., INT, VARCHAR, etc.).
• NOT NULL: Ensures the a column must always contain NULL value.

You can add a NOT NULL constraint to an existing table using the ALTER TABLE statement. The general syntax to add NOT NULL constraint to an existing table is:

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

Example Without Using NOT NULL Constraint

In this example, we will create a table without using the NOT NULL constraint. Since we have not applied NOT NULL constraint, all columns can store NULL values by default if we do not provide values.

Step 1: Create a Table

CREATE TABLE Students (
    student_id INT,
    student_name VARCHAR(100),
    age INT,
    city VARCHAR(50)
);

This SQL statement creates a table named Students in the MySQL database. The table contains four columns, such as student_id, student_name, age, and city. All columns can store NULL values because we have not used NOT NULL constraint.

Step 2: Insert Complete Data into the Table

To insert records into a table, type the following command.

INSERT INTO Students
VALUES(101, 'John', 20, 'Dhanbad');

Step 3: View Table Data

To view all records stored in a table, type the following commands.

SELECT * FROM Students;

Output:

Now we will insert partial data into the table.

INSERT INTO Students(student_id, student_name)
VALUES(102, 'Smith');

Output:

Since there is no NOT NULL constraint in the table, MySQL automatically allows columns to store NULL values by default. Therefore, the age and city columns became NULL.

Example of NOT NULL Constraint in MySQL

Let’s take an example in which we will use NOT NULL constraint with columns while creating a table.

Step 1: Create Employee Table

CREATE TABLE Employees (
    emp_id INT NOT NULL,
    emp_name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    department VARCHAR(50)
);

This table contains four columns: emp_id, emp_name, salary, and department. The NOT NULL constraint ensures that the emp_id, emp_name, and salary columns cannot remain empty. NULL values are not allowed in these columns. However, the NOT NULL constraint is not applied to the department column. Therefore, this column can contain NULL values.

Step 2: Describe the Table

To view the table structure, type the following command.

DESCRIBE Employees;

Output:

+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_id     | int           | NO   |     | NULL    |       |
| emp_name   | varchar(100)  | NO   |     | NULL    |       |
| salary     | decimal(10,2) | NO   |     | NULL    |       |
| department | varchar(50)   | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

In the above output:

• NO means NULL values are not allowed in the columns.
• YES means NULL values are allowed in the columns.

Therefore, the columns emp_id, emp_name, and salary cannot be NULL, whereas the column department can be NULL.

Step 3: Insert Complete Record

To insert data into the table, type the following command.

INSERT INTO Employees
VALUES(101, 'John', 55000.00, 'IT');

Output:

Query OK, 1 row affected

All required columns contain values, so the record is inserted successfully.

Step 4: Insert Record Without Department

INSERT INTO Employees(emp_id, emp_name, salary)
VALUES(102, 'David', 48000.00);

Output:

Query OK, 1 row affected

Since the column department is optional, MySQL automatically stores NULL in the department column.

Step 5: Insert Invalid Record

INSERT INTO Employees(emp_id, department)
VALUES(103, 'HR');

Output:

ERROR: Field 'emp_name' doesn't have a default value

This query fails because the columns, such as emp_name and salary, are defined with NOT NULL constraint. Since both mandatory columns are missing, MySQL does not allow the insertion.

Step 6: View Table Data

SELECT * FROM Employees;

Output:

+--------+----------+----------+------------+
| emp_id | emp_name | salary   | department |
+--------+----------+----------+------------+
| 101    | John     | 55000.00 | IT         |
| 102    | David    | 48000.00 | NULL       |
+--------+----------+----------+------------+

In this example, the NOT NULL constraint is applied to the emp_id, emp_name, and salary columns to ensure they always contain values. The department column does not use the NOT NULL constraint, so it can store NULL values. This helps maintain accurate and reliable employee records in the database.

Adding NOT NULL Constraint to Existing Table

Suppose we already have a table in a database like this:

CREATE TABLE Customers (
    customer_id INT,
    customer_name VARCHAR(100),
    email VARCHAR(100)
);

Now we want to make customer_name mandatory. To make it mandatory, type the following command.

ALTER TABLE Customers
MODIFY customer_name VARCHAR(100) NOT NULL;

Now the customer_name column can no longer store NULL values.

How to Remove NOT NULL Constraint in MySQL?

To remove a NOT NULL constraint from a column so that the column can accept NULL values again, use the following commands:

ALTER TABLE table_name
MODIFY column_name datatype NULL;

Example:

ALTER TABLE Customers
MODIFY customer_name VARCHAR(100) NULL;

Now the customer_name column can contain NULL values because we have removed the NOT NULL constraint.

Difference Between NULL and NOT NULL

Real-Time Example of NOT NULL Constraint

A common real-time example of the NOT NULL constraint is an online user registration system. Consider the following user registration table:

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Why NOT NULL is Important Here?

There are the following reasons for using NOT NULL constraint with columns.

• A username is required for login.
• A password is mandatory for authentication.
• Email is necessary for communication.

Without NOT NULL constraints, the user can insert incomplete records into the database table.

The NOT NULL constraint in MySQL is an essential feature used to enforce mandatory data entry in database tables. It prevents NULL values from being stored in important columns and helps maintain data integrity, consistency, and reliability. We can avoid incomplete records by using NOT NULL constraints and build secure, accurate, and well-structured database applications.