How to add not null constraint to existing column in MySQL?

To add not null constraint to an existing column in MySQL, we will use the ALTER command. This is a type of validation to restrict the user from entering null values.

Let us see an example. Firstly, we will create a table. The CREATE command is used to create a table.

 mysql> create table AddNotNUlldemo - > ( - > name varchar(100) - > ); Query OK, 0 rows affected (0.44 sec) 

To insert records.

 mysql> insert into AddNotNUlldemo values('John'); Query OK, 1 row affected (0.19 sec) mysql> insert into AddNotNUlldemo values('Bob'); Query OK, 1 row affected (0.19 sec) 

To display all the records.

 mysql> select *from AddNotNUlldemo; 

Here is the output.

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

The following is the syntax to add a constraint to an existing column.

 ALTER table yourTableName modify column_name data type constraint; 

Let us now implement the above syntax to implement the below query. Here, we are including "not null" constraint.

 mysql> ALTER table AddNotNUlldemo modify name varchar(100) not null; Query OK, 0 rows affected (1.14 sec) Records: 0 Duplicates: 0 Warnings: 0 

Now, we cannot insert a null value into the table "AddNotNUlldemo" because we have set the constraint above as "not null". If we try to add a null value, we will get an error. The following is an example.

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

To display all the records.

 mysql> SELECT *from AddNotNUlldemo; 

Here is the output.

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