How to Add a Not Null Constraint in MySQL

Not null constraints are a great way to add another layer of validation to your data. Sure, you could perform this validation in your application layer, but shit happens: somebody will forget to add the validation, somebody will remove it by accident, somebody will bypass validations in a console and insert nulls, etc. The only way to really be sure is to enforce it in your column definition. If you're validating nulls on the database layer as well, you're protected 💪

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

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

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) DEFAULT '0',

(The rest of the output is truncated for brevity)

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

ALTER TABLE products MODIFY stocks INT NOT NULL DEFAULT 0;