Last updated on July 18th, 2024 at 06:18 am
If you need to set a MySQL column to not accept null values, then you can add NOT NULL constraint in MySQL. You can add NOT NULL constraint when you create table table using CREATE TABLE statement, or add NOT NULL constraint in existing table using ALTER TABLE statement. Here’s how to add NOT NULL constraint in MySQL.
How To Add NOT NULL Constraint in MySQL
Here are the steps to add NOT NULL constraint for column in MySQL, add NOT NULL constraint to existing column and remove NOT NULL constraint from a column.
Here’s the syntax to define NOT NULL constraint in MySQL when you create new table.
column_name data_type NOT NULL;
In the above statement, you need to specify NOT NULL after mentioning the column_name and its data_type
Here’s an example to add NOT NULL constraint in MySQL.
mysql> create table product_sales(
id int,
amount int NOT NULL,
order_date date
);
mysql> describe product_sales;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| amount | int(11) | NO | | NULL | |
| order_date | date | YES | | NULL | |
+------------+---------+------+-----+---------+-------+
mysql> insert into product_sales(id, order_date)
values(1,'2020-08-01');
ERROR 1364 (HY000): Field 'amount' doesn't have a default value
In the above CREATE TABLE query, we have added a NOT NULL constraint for amount column. When you insert a NULL value in this column, MySQL will give an error.
Bonus Read : How to Add Default Constraint in MySQL
ALTER TABLE Add NOT NULL constraint in MySQL
You can also add NOT NULL constraint to existing column in MySQL using ALTER TABLE … CHANGE statement. Here’s the syntax to add not null constraint in existing table in MySQL.
ALTER TABLE table_name CHANGE old_column_name new_column_name column_definition;
In the above query, we mention the same column name for old_column_name as well as new_column_name. The new_column_name must be followed by its column_definition of data type and NOT NULL keyword.
Here’s an example of SQL query to add NOT NULL constraint to existing column in MySQL.
mysql> ALTER TABLE product_sales
CHANGE
order_date
order_date DATE NOT NULL;
mysql> describe product_sales;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| amount | int(11) | NO | | NULL | |
| order_date | date | NO | | NULL | |
+------------+---------+------+-----+---------+-------+
In the above query, we have added NOT NULL constraint to existing column order_date
Bonus Read : MySQL Select Top N Rows Per Group
How to Drop NOT NULL constraint
You can also drop NOT NULL constraint using ALTER TABLE … MODIFY statement. Here’s the syntax to remove NOT NULL constraint in MySQL.
ALTER TABLE table_name MODIFY column_name column_definition;
In the above query, you need to specify the column name and definition for which you want to remove NOT NULL constraint.
Here’s an example to remove NOT NULL constraint for order_date column.
mysql> ALTER TABLE product_sales
MODIFY order_date DATE;
mysql> describe product_sales;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| amount | int(11) | NO | | NULL | |
| order_date | date | YES | | NULL | |
+------------+---------+------+-----+---------+-------+
Bonus Read : How to Get Last Week Data in MySQL
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.