Introducing PopSQL + dbt. The first SQL editor with built-in dbt supportRead more →

How to Remove a Not Null Constraint in MySQL

To remove a NOT NULL constraint for a column in MySQL, you use the ALTER TABLE .... MODIFY command and restate the column definition, removing the NOT NULL attribute.

--Example: Products have a default stock of 0
ALTER TABLE products MODIFY stocks INT;

Note that you MUST restate the full column definition, otherwise undeclared attributes will go back to default settings. For example, not restating the DEFAULT clause will unset the default value.

To ensure that you do not miss anything, you can use the SHOW CREATE TABLE command to see the full column definition:

mysql> SHOW CREATE TABLE products\G
*************************** 1. row ***************************
       Table: products
Create Table: CREATE TABLE `products` (
  `product_id` bigint(20) NOT NULL,
  `product_name` varchar(100) DEFAULT '',
  `stocks` int(11) NOT NULL DEFAULT '0',

(The rest of the output is truncated for brevity)

Use the current definition and remove the NOT NULL for the correct modification:


Ready for a modern SQL editor?