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

MySQLMySQLi Database

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)
raja
Published on 20-Nov-2018 16:52:28
Advertisements