How to Remove Not Null Constraint in MySQL

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 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. While specifying column definition, we do not specify NOT NULL constraint.

Also read : How to Remove Default Value of Column in MySQL

For example, let us say you have a table sales(id, order_date, amount)

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

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

alter table sales amount int;

We specify amount column’s full definition, but without NOT NULL constraint.

Also read : How to Round Timestamp in MySQL

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
);

Also read : How to Rename Table in MySQL

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

alter table sales amount int default 0;

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

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

• About Author

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!