Removing NOT NULL restriction from column in MySQL?

To remove NOT NULL restriction from column in MySQL, use ALTER command. The syntax is as follows:

ALTER TABLE yourTableName MODIFY COLUMN yourColumnName dataType;

To understand the above syntax, let us create a table. The query to create a table is as follows:

mysql> create table NotNullDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(20) NOT NULL,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.58 sec)

In the above table, if you insert the NULL value to the column ‘Name’ then MySQL will give an error of NOT NULL restriction. The query to insert NULL value is as follows:

mysql> insert into NotNullDemo(Name) values(NULL);
ERROR 1048 (23000): Column 'Name' cannot be null

Here is the query to remove NOT NULL restriction from column ‘Name’:

mysql> alter table NotNullDemo MODIFY COLUMN Name varchar(20);
Query OK, 0 rows affected (1.78 sec)

Records: 0 Duplicates: 0 Warnings: 0

Now you can insert NULL values as per your choice. The query to insert record in the table with NULL value:

mysql> insert into removeNotNulllDemo(Name) values(NULL);
Query OK, 1 row affected (0.12 sec)
mysql> insert into removeNotNulllDemo(Name) values('Sam');
Query OK, 1 row affected (0.14 sec)
mysql> insert into removeNotNulllDemo(Name) values('Mike');
Query OK, 1 row affected (0.13 sec)
mysql> insert into removeNotNulllDemo(Name) values(NULL);
Query OK, 1 row affected (0.10 sec)
mysql> insert into removeNotNulllDemo(Name) values(NULL);
Query OK, 1 row affected (0.10 sec)
mysql> insert into removeNotNulllDemo(Name) values('John');
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement. The query is as follows:

mysql> select *from removeNotNulllDemo;

The following is the output:

+----+------+
| Id | Name |
+----+------+
|  1 | NULL |
|  2 | Sam  |
|  3 | Mike |
|  4 | NULL |
|  5 | NULL |
|  6 | John |
+----+------+
6 rows in set (0.00 sec)