How to use NOT NULL in SQL

In this SQL article, you will learn how to use NOT NULL to the column in Structured Query Language.

What is NOT NULL constraint?

The NOT NULL is a constraint in SQL which does not allow you to insert NULL values in the specified column.

If any column is defined as a NOT NULL constraint in the table, we cannot insert a new record in the table without adding the value to the column.

The following syntax adds the NOT NULL constraint to the column at the time of table creation:

We can define NOT NULL constraint to one or more columns in one SQL table.

The following syntax adds the NOT NULL constraint to the column when the table already exists:

If you want to use NOT NULL constraint at the time of table creation, you have to follow the steps given below:

1. Create the new database
2. Create a new table and add NOT NULL
3. View table structure

Step 1: Create the Simple new database

Firstly, you have to make a new database in Structured Query Language.

The following query creates the Fortis_Hospital Database:

Step 2: Create the New table and add NOT NULL

The following query creates the Doctor_Info table in the Fortis_Hospital Database and adds the NOT NULL constraint to the Doctor_ID column of the table:

The following CREATE TABLE query adds the NOT NULL constraint to each column of the Doctor_Info table:

Step 3: View the Table Structure

The following query describes the structure of the Doctor_Info table:

Output:

Delete NOT NULL constraint from the table

The MODIFY keyword with ALTER statement allows the database users to remove the NOT NULL constraint from the column of the table.

If you want to remove the NOT NULL constraint from the SQL table, you can delete it by using the following syntax:

The following query deletes the NOT NULL from the Doctor_Country column of the Doctor_Info table:

To check the result of the above ALTER query, you have to type the following DESC command, which describes the structure of the Doctor_Info table:

Output:

As we can see in the above SQL table, the value of the NULL column is Yes for the Doctor Country field, which shows that the Doctor_Country column will accept NULL values.

Add NOT NULL constraint to Existing table.

Any database user can easily add NOT NULL constraint to the existing table by using the SQL ALTER TABLE syntax.

Syntax to Add NOT NULL constraint to Existing table:

The following SQL statement defines the NOT NULL constraint to the Doctor_Info table:

To check the result of the above ALTER query, you have to type the following DESC command to view the structure of the Doctor_Info table:

Output: