How to add NOT NULL constraint to an existing column in MySQL?

To add a non-null constraint to an existing column in MySQL, we will use the ALTER command. This is a validation type used to restrict user input of null values.

Let's look at an example. First, we will create a table. The CREATE command is used to create a table.

mysql> create table AddNotNUlldemo
　　　-　> (
　　　-　> name varchar(100)
　　　-　> );

Inserting records.

mysql> insert into AddNotNUlldemo values('John');
mysql> insert into AddNotNUlldemo values('Bob');

Display all records.

mysql> select　*from AddNotNUlldemo;

This is the output.

+------+
| name |
+------+
| John |
| Bob |
+------+
2　rows in set (0.00 sec)

The following is the syntax for adding constraints to existing columns.

ALTER table yourTableName modify column_name data type constraint;

Now let's implement the above syntax to achieve the following query. Here, we include the 'not null' constraint.

mysql> ALTER table AddNotNUlldemo modify name varchar(100) not null;
Records: 0 Duplicates: 0 Warnings: 0

Now, we cannot insert a null value into the table 'AddNotNUlldemo' because we have set the constraint above to 'not null'. If you try to add a null value, you will receive an error message. Here is an example.

mysql> INSERT into AddNotNUlldemo values(null);
ERROR　1048　(23000): Column 'name' cannot be null

Display all records.

mysql> SELECT　*from AddNotNUlldemo;

This is the output.

+------+
| name |
+------+
| John |
| Bob |
+------+
2　rows in set (0.00 sec)