MySQL NOT NULL Constraint

The NOT NULL constraint in MySQL restricts the insertion of any null values on the column or a set of columns on which it has been defined. It means a column cannot contain any NULL or empty values if the NOT NULL constraint is defined on it. This constraint guarantees that each row in the table must have a value for that particular column.

This feature helps the database to become more robust due to the data accuracy and reliability.

MySQL NOT NULL Constraint Syntax

The NOT NULL constraint in the MySQL table can defined in two ways.

NOT NULL on CREATE TABLE

The following syntax helps to define the NOT NULL constraint during the table creation in MYSQL.

CREATE TABLE table_name (
	column1 datatype NOT NULL,
	column2 datatype NOT NULL,
	.....
);

Here, the NOT NULL keyword needs to be added after column_name and datatype option.

NOT NULL on ALTER TABLE

In MySQL, you can create the NOT NULL column ever after the creation of the table. In this case, you need to use the ALTER TABLE statement.

ALTER TABLE table_name 
modify column_name datatype NOT NULL;

MySQL NOT NULL Constraint Example

Now, let’s understand the concept of the NOT NULL constraint more clearly with the help of some examples.

CREATE TABLE teachers (
	teacherID INT AUTO_INCREMENT PRIMARY KEY,
	firstName varchar(30) NOT NULL,
	lastName varchar(30) NOT NULL,
	emailID varchar(100)
);

In the above example, you can see that we have created a table named teachers where firstName and lastName columns are defined with the NOT NULL constraint. That means you cannot insert any NULL or empty values in these columns.

Now, let’s insert some data into the teachers table.

INSERT INTO teachers(teacherID,firstName,lastName,emailID) VALUES
(1,'Raghu','Ram','raghu1970@gmail.com'),
(2,'Priya','Saha','priyasaha123@gmail.com'),
(3,'Sagar','Sharma',NULL);

Table: teachers

mysql> select * from teachers;
+-----------+-----------+----------+------------------------+
| teacherID | firstName | lastName | emailID                |
+-----------+-----------+----------+------------------------+
|         1 | Raghu     | Ram      | raghu1970@gmail.com    |
|         2 | Priya     | Saha     | priyasaha123@gmail.com |
|         3 | Sagar     | Sharma   | NULL                   |
+-----------+-----------+----------+------------------------+
3 rows in set (0.41 sec)

Now, let’s try to insert some null values into the firstName column.

INSERT INTO teachers(teacherID,firstName,lastName,emailID) VALUES
(4,NULL,'Suresh','suresh123@gmail.com');

Output:-

mysql> INSERT INTO teachers(teacherID,firstName,lastName,emailID) VALUES
-> (4,NULL,'Suresh','suresh123@gmail.com');
ERROR 1048 (23000): Column 'firstName' cannot be null

As you can see above the insert statement gives an error while inserting a row with the NULL VALUE in firstName column into the table teachers as the firstName column defined with the NOT NULL constraint.

You can also define the NOT NULL constraint to an existing column using the following example.

ALTER TABLE teachers
MODIFY COLUMN emailID varchar(100) NOT NULL;