How to Remove Not Null Constraint in MySQL

Last updated on July 18th, 2024 at 06:14 am

NOT NULL column constraint prevents users from assigning null values to the column. They are useful for disallowing null values from creeping into your database. But sometimes you may need to remove NOT NULL constraint in MySQL. For example, occasionally you may need to enter null data values in your table. MySQL allows you to drop NOT NULL constraint in MySQL using ALTER TABLE … MODIFY statement. In this article, we will look at how to remove NOT NULL constraint in MySQL.

How to Remove Not Null Constraint in MySQL

Here are the steps to remove NOT NULL constraint in MySQL. Here is the syntax of ALTER TABLE statement to remove Not Null constraint.

alter table table_name modify full_column_definition;
OR
alter table table_name modify column full_column_definition;

In the above SQL query you need to specify table name, and also the full definition of column whose NOT NULL constraint you want to remove, after MODIFY keyword. While specifying column definition, we do not specify NOT NULL constraint.

Let us look at a few examples.

Example #1

For example, let us say you have a table sales(id, order_date, amount). Here amount column has int data type with not null constraint.

mysql> create table sales (   
   id int primary key,   
   order_date date,
   amount int not null
);

mysql> describe sales;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | NO   | PRI | NULL    |       |
| order_date | date    | YES  |     | NULL    |       |
| amount     | int(11) | NO   |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

Here is the SQL query to remove NOT NULL constraint from amount column.

mysql> alter table sales modify amount int;

mysql> describe sales;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | NO   | PRI | NULL    |       |
| order_date | date    | YES  |     | NULL    |       |
| amount     | int(11) | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

We specify amount column’s full definition, but without NOT NULL constraint. As you can see in the output of describe command, amount column’s default value is NULL.

Example #2

As mentioned earlier, while removing NOT NULL constraint you need to restate the full column definition. Otherwise, undeclared attributes will return to their default settings.

For example, let us say you have the following sales table such that amount column has NOT NULL constraint as well as default value of 0.

create table sales (   
   id int primary key,   
   order_date date,
   amount int not null default 0
);

Here is the syntax to remove NOT NULL constraint from sales table.

alter table sales modify amount int default 0;

As you can see, we have continued to specify default value for amount column in our SQL query.

Example #3

By default, primary key columns cannot have null values. Even if you do not specify a NOT NULL constraint during table definition, it is automatically added. But you can still remove this constraint from primary key column as shown below, using alter table…modify statement.

mysql> alter table sales modify id int;

mysql> describe sales;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | NO   | PRI | 0       |       |
| order_date | date    | YES  |     | NULL    |       |
| amount     | int(11) | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

Please note, when you remove NOT NULL constraint from primary key column, its default value is set to 0, and not NULL, as shown above.

Example #4

Similarly, foreign keys also have a NOT NULL constraint in most cases. In the following example, order_num is a foreign key with a not null constraint. Nevertheless, you can remove this constraint also using ALTER TABLE statement.

mysql> CREATE TABLE orders (
         order_id int,
         order_num int not null,
         pid int,
         PRIMARY KEY (order_id),
         FOREIGN KEY (order_num) REFERENCES sales(id)
     );

mysql> describe orders;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| order_id  | int(11) | NO   | PRI | 0       |       |
| order_num | int(11) | NO   | MUL | NULL    |       |
| pid       | int(11) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+

mysql> alter table orders modify order_num int;

mysql> describe orders;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| order_id  | int(11) | NO   | PRI | 0       |       |
| order_num | int(11) | YES  | MUL | NULL    |       |
| pid       | int(11) | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+

Example #5

If you want to add back the NOT NULL constraint, you can use the same alter table…modify statement, with NOT NULL constraint in column definition.

mysql> alter table sales modify column amount int not null;

mysql> describe sales;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id         | int(11) | NO   | PRI | NULL    |       |
| order_date | date    | YES  |     | NULL    |       |
| amount     | int(11) | NO   |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

Conclusion

In this article, we have learnt how to remove NOT NULL constraint in MySQL. using ALTER TABLE…MODIFY statement. You can use it on all column data types. Please remember to mention MODIFY keyword between table name and column definition. Also remember to specify the full column definition after MODIFY keyword, without NOT NULL constraint in it. However, please remember, it is not a good practice to remove NOT NULL constraints if they have already been set during table definition. Therefore, it is recommended to add back NOT NULL constraint after you are done with null data entry/modifications.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Also read :
How to Remove Default Value of Column in MySQL
How to Round Timestamp in MySQL
How to Rename Table in MySQL