What is NOT NULL in SQL?

What is NOT NULL in SQL?

The NOT NULL constraint helps our data to become free of unknown or missing values.

By default in SQL, our columns can hold NULL values, i.e. the value in the specified columns might be missing or unknown. But there can be a different instance where we do not want any column to have a null value. What do we do in such a situation? We make use of the NOT NULL constraint. It works in a very similar manner as the NULL constraint. Using this constraint, we can prevent inserting a no null value.

One common reason for using the NOT NULL constraint is that we do not want missing or unknown values. Missing data can affect the summary of our data & the insights we get from them.

Syntax for adding the NOT NULL constraint:

We can use the not null constraint in many cases. This article will cover the usage with two different clauses :

1. CREATE TABLE
2. ALTER TABLE

How to add the NOT NULL Constraint in SQL?

Using the CREATE TABLE & ALTER TABLE commands, we can add the not null constraint in SQL.

Let us look at the syntax of the NOT NULL constraint with the CREATE TABLE command:

Using the syntax defined above, let us use it to create the GRADES table:

How to Remove the NOT NULL Constraint in SQL?

The ALTER TABLE command also helps remove the NOT NULL constraint in SQL. In some situations, it might be required to remove the NOT NULL constraint from some columns of our table. Let us suppose the GRADES table is created using the following query:

The marks in physics column does not allow NULL values due to the NOT NULL constraint applied on it. But, suppose the physics teacher wants to allow NULL values in the marks in physics (MI_PHYSICS) column. How do we remove the NOT NULL constraint? We make use of the ALTER TABLE clause. The syntax used here is similar to the one we have defined before. The only difference is that we make use of null constraint:

Now we want to remove the not null constraint from the marks in the physics column:

It solves our problem in the specified situation. But this is not the only reason to remove the not-null constraint. There can be a change in the requirement from the client (teacher in this case), or maybe you want to take a new route for your case study.

Examples of NOT NULL constraint in SQL

Now that we have gone through the basic syntax of the NOT NULL constraint and how to add/remove it, let us go through some more examples to make our understanding better. We will make use of our GRADES table example only.

Example 1: Selecting NOT NULL Columns

Let us look at the simple syntax before looking at the example :

We have three columns in our table accepting NULL values. But in some instances, we only wish to look at all the NOT NULL values to analyze and take out conclusions (if any). We make use of the SELECT command as follows :

The above statement ensures only to show columns with some valid value.

Example 2: Counting NOT NULL Columns

Now we want to calculate the total class marks in physics. We use the SELECT & COUNT() function to calculate marks of all NOT NULL values. The below statement helps us accomplish our result:

Example 3: Deleting Records with Any Value

We want to delete all the records with any valid value. We make use of the DELETE command. Let us look at the code snippet below:

It deletes all the records where students' marks are not null.

Learn More About NULL Value in SQL

It is important to learn about the NULL value in SQL to get a clearer understanding of the NOT NULL clause. The need for the latter rose because of the former’s existence. To learn more about the NULL value in SQL, visit here.