How to add a NOT NULL column in MySQL?

You can add a not null column at the time of table creation or you can use it for an existing table.

Case 1 − Add a not null column at the time of creating a table. The syntax is as follows

CREATE TABLE yourTableName
(
   yourColumnName1 dataType NOT NULL,
   yourColumnName2 dataType
   .  
   .
   .
   N
);

The query to create a table is as follows

mysql> create table NotNullAtCreationOfTable
   -> (
   -> Id int not null,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (0.60 sec)

In the above table, we have declared Id as int type that does not take NULL value. If you insert NULL value, you will get an error.

The error is as follows

mysql> insert into NotNullAtCreationOfTable values(NULL,'John');
ERROR 1048 (23000): Column 'Id' cannot be null

Insert a value other than NULL. That would be acceptable

mysql> insert into NotNullAtCreationOfTable values(1,'Carol');
Query OK, 1 row affected (0.13 sec)

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

mysql> select *from NotNullAtCreationOfTable;

The following is the output

+----+-------+
| Id | Name  |
+----+-------+
|  1 | Carol |
+----+-------+
1 row in set (0.00 sec)

Case 2 − Add a not null column in the existing table. The syntax is as follows

ALTER TABLE yourTableName ADD yourColumnName NOT NULL

The query to create a table is as follows

mysql> create table AddNotNull
   -> (
   -> Id int,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (1.43 sec)

Here is the query to add a not null column in an existing table using alter command.

The query to alter a column to not null column is as follows. Here we are going add Age column which has the constraint NOT NULL.

mysql> alter table AddNotNull add Age int not null;
Query OK, 0 rows affected (0.44 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now you can check the description of the table using desc command. The query is as follows

mysql> desc AddNotNull;

The following is the output

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Id    | int(11)      | YES  |     | NULL    |       |
| Name  | varchar(100) | YES  |     | NULL    |       |
| Age   | int(11)      | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.08 sec)

Let us try to insert NULL value to the column Age. If you will try to insert NULL value to the column Age, you will get an error.

The query to insert record is as follows

mysql> insert into AddNotNull values(1,'John',NULL);
ERROR 1048 (23000): Column 'Age' cannot be null

Now insert the other record. That won’t give an error

mysql> insert into AddNotNull values(NULL,NULL,23);
Query OK, 1 row affected (0.22 sec)

Now you can display all records from the table using select statement. The query is as follows

mysql> select *from AddNotNull;

The following is the output

+------+------+-----+
| Id   | Name | Age |
+------+------+-----+
| NULL | NULL | 23 |
+------+------+-----+
1 row in set (0.00 sec)