NOT NULL Constraint in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

NOT NULL Constraint in MySQL with Examples

In this article, I am going to discuss NOT NULL Constraint in MySQL with Examples. Please read our previous article where we discussed the basics of MySQL Constraints.

MySQL NOT NULL Constraint

By default, the database column stores NULL value means no value in the data row. By defining NOT NULL Constraint to the table column, the default behavior of the database column changes and it does not accept NULL values. In the database table, we can have some columns where storing NULL value doesn’t make any sense.

So, if you want any column not to accept NULL value then you need to apply the NOT NULL constraint to that column in MySQL. So, in other words, we can say that this MySQL NOT NULL constraint is used to avoid NULL values but it accepted duplicate values into a column.

In the MySQL database, a table can contain any number of NOT NULL Constraints. The NOT NULL Constraints can be defined to string as well as numeric data types such as INT, VARCHAR, etc.

Examples to Understand MySQL NOT NULL Constraint:

Let us understand MySQL NOT NULL Constraint with some examples. To understand this concept, first, let us create the StudentDB database and Student table by executing the below SQL Script.

Create database StudentDB;
Use StudentDB;

CREATE TABLE Student (
  StudentId INT NOT NULL,
  FullName VARCHAR(50) NOT NULL,
  RegdNo VARCHAR(50) NOT NULL,
  Branch VARCHAR(50) NOT NULL,
  Mobile VARCHAR(15)
);

As you can see, here, we created the Student table with five columns i.e. StudentId, FullName, RegdNo, Branch, and Mobile. There is no possible data row, where the students StudentId, FullName, RegdNo, Branch will have a NULL value or no value. So, we applied the NOT NULL Constraint to StudentId, FullName, RegdNo, Branch columns. On the other hand, it may possible that the mobile number is not present and hence we need have not applied the NOT NULL Constraint to the Mobile Number column.

For any existing database table, if you want to check the constraints, in MySQL, you can use the below SQL Query. As we created the Student table, let us see, what the following query gives us. Here, the Student is the table name.

DESCRIBE Student;

Once you execute the above query, it will give us the below output.

The above image shows the Student table structure, except Mobile filed, all other fields have NO value for NULL constraint. It means the StudentId, FullName, RegdNo, Branch won’t accept NULL values. Only the Mobile field will accept a NULL value.

Let’s modify the Student table definition using the following alter SQL statement and accept a null value for the FullName column.

ALTER TABLE Student MODIFY COLUMN FullName VARCHAR(45) NULL;

Now execute the following SQL statement to display the Student table structure.

DESCRIBE Student;

Once you execute the above query, it will give us the below output.

And now the FullName column has NULL constraints value “yes”. It means the FullName column can accept NULL values. Let’s INSERT a new data row with no name by executing the below SQL Statement.

INSERT INTO Student (StudentId, FullName, RegdNo, Branch, Mobile) VALUES (1, NULL, ‘1001’, ‘CSE’, ‘1234567890’);

Once you execute the above SQL Statement, please verify the Student table and you will see the following record. As you can see, we have one student without a name.

Does the above record in the Student table make any sense? The answer is NO. Therefore, it is better not to allow inserting NULL for columns that are required and mandatory like the above FullName column. Let’s modify the table structure again and enable NOT NULL Constraint for the FullName column by executing the below SQL Statement.

ALTER TABLE Student MODIFY COLUMN FullName VARCHAR(45) NOT NULL;

But, while you try to execute the above alter statement, you will get the below error. This is because the FullName Column already has null values and hence you cannot apply the NOT NULL constraint.

Error Code: 1138. Invalid use of NULL value

So, first, we need to delete the data row which contains NULL values in the FullName column and then alter the column by executing the below SQL Statements.

DELETE FROM Student WHERE FullName IS NULL;
ALTER TABLE Student MODIFY COLUMN FullName VARCHAR(45) NOT NULL;

Note: When we INSERT a null value into a column on which the NOT NULL constraint is imposed. The execution of the insert statement is terminated by displaying a user-friendly message telling the reason for termination and also specifies the database, the table, and the column where the problem got occurred.

Now let’s try to insert a student record with a NULL value for the FullName column as shown in the below SQL Query or try to insert NULL for any column on which the NOT NULL constraint is applied.

INSERT INTO Student (StudentId, FullName, RegdNo, Branch, Mobile) VALUES (1, NULL, ‘1001’, ‘CSE’, ‘1234567890’);

Now, when you try to execute the above statement, you will get the following error which clearly tells us that you cannot insert null for the FullName column.

Error Code: 1048. Column ‘FullName’ cannot be null

That means we cannot save the data row using the NULL value for FullName columns. Now, execute the below SQL Statement by supplying the FullName value.

INSERT INTO Student (StudentId, FullName, RegdNo, Branch, Mobile) VALUES (1, ‘Anurag’, ‘1001’, ‘CSE’, ‘1234567890’);

And this time the data get inserted into the Student table.

When to use NOT NULL Constraint?

If you don’t want to accept NULL values but you want to accept duplicates values to be stored in a column, then you need to apply NOT NULL Constraint to that column in MySQL.

In the next article, I am going to discuss the Default Constraint in MySQL with Examples. Here, in this article, I try to explain NOT NULL Constraint in MySQL with Examples and I hope you enjoy this article.