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