NOT NULL constraints are an important tool for maintaining data integrity in SQL databases. However, we may occasionally find that a NOT NULL constraint gets in the way of a legitimate operation that we want to perform, and it prevents us from entering data. In such cases, we may need to remove the NOT NULL constraint in order to proceed.

To remove a NOT NULL constraint in MySQL, we use the ALTER TABLE statement to redefine the relevant column without the NOT NULL constraint.

Example

This example demonstrates creating a table that contains NOT NULL constraints against its columns, then removing one of those constraints from the table.

Create a Table with a NOT NULL Constraint

Suppose we create the following table:

CREATE TABLE Products (
  ProductName varchar(255) NOT NULL,
  ProductDescription varchar(1000) NOT NULL
);

This table contains two columns, both of which have NOT NULL constraints. When it comes to NOT NULL constraints, these are applied as attributes against the individual column level.

Remove the NOT NULL Constraint

Let’s now remove the NOT NULL constraint of the ProductDescription column:

ALTER TABLE Products 
MODIFY ProductDescription varchar(1000) NULL;

Result:

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

That’s all we need to do. The message indicates that we successfully removed the NOT NULL constraint. Or perhaps more accurately, it indicates that we modified the column to allow NULL values.

We could also have omitted the NULL keyword, and the column would have been redefined without the NOT NULL constraint, like this:

ALTER TABLE Products 
MODIFY ProductDescription varchar(1000);

Either way, the column would be redefined as allowing NULL values (i.e. without the NOT NULL constraint).